In other words, a selection criteria set on the query ensures that only certain rows are selected and returned. Frequently an entire table is transferred to a spreadsheet and then several rows are removed because they weren't wanted in the first place. Let's move on to something a little more dynamic. Select the option to load the data in the spreadsheet
#CONVERT DBC FILE TO EXCEL PROGRAMS PASSWORD#
You might be prompted for an ID and password after you press OK and then you are presented with the query results as in Fig. This value causes that data to be converted from EBCDIC by using the job CCSID so that's in the example in Fig.
If you have data in character fields defined with CCSID 65535, add the Translate keyword value pair.
#CONVERT DBC FILE TO EXCEL PROGRAMS DRIVER#
Using this interface, you do not need to provide values for anything more that the Driver and System keywords because we are not using any catalog features to browse for files or columns. The ODBC connection string keywords and their default values are defined in the IBM Documentation. But this time, select '(None)' for the data source and enter a connection string that defines the ODBC driver to use and the system to connect to at a minimum. We follow the same steps of selecting the option to get data from ODBC. Then, we can share the spreadsheet with anyone with the ODBC driver installed on their system. Fortunately, we can define the connection without a data source. So you can see how a DSN is also a hindrance to the portability of the solution. You can freely share a spreadsheet with an embedded data connection defined in it (like our first example). But the recipient must also have that DSN defined with the same name (and connecting to the same system). While DSNs are a great way to get consistent behavior from one or more applications, they do require the step of creating the DSN. Ultimately, the application might override most any of the values you specify in a DSN so they aren't strict controls on the settings. A DSN or data source Name can be thought of as a saved collection of default values for an ODBC connection to use. In the previous example, a DSN was selected to define the connection. With this dialog you can modify the query, change the query name, and use the 'Advanced Editor' function (near the upper left) to edit the SQL statement. Selecting the EDIT option opens the dialog box shown in Fig. If you close the 'Queries & Connections' pane, you can reopen it using the 'Queries & Connections' item on the Data ribbon. Note, there is also a refresh icon to the right of the query name and clicking it refreshes the data in spreadsheet. The options in this window provide functions to edit the query and do several other things that are beyond the scope of this technote. If you hover your mouse over the spreadsheet icon just to the left of the query name (it was given a default name of Query1), you get a pop-up window as shown in Fig. Note the 'Queries & Connections' item to the right of the data.
Press 'Load' and it loads the data into the spreadsheet and saves the query in it as shown in Fig. 1b.Ĭlick OK and it makes an ODBC connection, run the query and display the results in a table as shown in Fig. Select the data source to connect to Db2 on IBM i and expand the 'Advanced options' twistie and type in your select statement in the 'SQL statement (optional)' section as shown in Fig. Start by opening a blank worksheet and selecting the Data ribbon, then 'Get Data', 'From Other Sources' and select 'From ODBC' as shown in Fig. In the following examples, Excel 2016 was used but the features exist in older versions of Excel as well. This technote demonstrates two different ways to retrieve data by using an ODBC data source (DSN) which was previously created and finally by using a DSN-less connection. Excel features a Data ribbon in which you can select different ways to import data into Excel. While the data transfer tools provided by IBM can certainly retrieve data for use in Excel, they are not the easiest nor the most functional way to do that.
0 kommentar(er)
