About cookies on this site Our websites require some cookies to function properly (required). In addition, other cookies may be used with your consent to analyze site usage, improve the user experience and for advertising. For more information, please review your options. By visiting our website, you agree to our processing of information as described in IBM’sprivacy statement. To provide a smooth navigation, your cookie preferences will be shared across the IBM web domains listed here.
Question & Answer
Question
How can I transfer data from Excel (or Calc) into a table on IBM i with Access Client Solutions (ACS)?
Answer
There are two different scenarios for uploading data from a spreadsheet. The first is when the data in the spreadsheet is used to create a new table and the second is when the data is uploaded into an existing table.
I need to upload the data in it to the KITCHCUST table on the IBM i that exists with the following DDL:
There are a couple of issues here to consider. First, the column names I have in the first row of my spreadsheet do not match the column names in the file so I simply do not select them. Second, I do not have a fdfx file that defines my spreadsheet data so I cannot upload into an externally described table. Therefore, I have to start by doing a download from the KITCHCUST table to an active spreadsheet so that the download utility creates the proper fdfx file for me. I set up the download like the following:
I must click the Details button to enable the option to save the fdfx file and give the fdfx a name, I do not care about the rest of these features.
But most importantly, I do not really want to download the data from the i, and I sure do not want it to overwrite the data in my spreadsheet. Back on the main transfer panel, I click the 'Data Options' button and add a Where clause to define a selection criteria (1=0) which prevents any data from being selected.
Then, I click the OK button in the Data Options window and press the 'Start Transfer' button. It results in a message that says, "No data was matched to the specified options." but we know that was intentional.
Next, click the button on the toolbar that is a green arrow pointing up which starts a new data transfer to the IBM i. We specify that the input device is 'Active Excel Spreadsheet', specify the IBM i system that we want to upload to, and the library and file (MIKSWENS/KITCHCUST). Then, we click the details button and likely see that the 'Use client file description' option is selected but the file name is not correct. Recall I named my fdfx with name KitchCustomers.fdfx. So I click the browse button and I find the fdfx file I created in the default location. I can click the Advanced button and confirm that the starting and ending positions are correct and I see that while they are disabled, they match the range that was selected in Figure 5
If the table has any date, time, or timestamp columns, click the 'Properties' button and select the option, 'Convert spreadsheet date and time cell to IBM i date or time'. Finally, click the OK button in the upload file details window and the 'Start Transfer' button in the main window. If the types and sizes of the data in your spreadsheet match the column definitions for the table on the IBM i, it uploads your data.
Scenario 1: Creating a table and populating it from a spreadsheet by using ACS data transfer.
Here is a small sample spreadsheet with data that I need to export to a library on IBM i with the populated cells already selected (highlighted). Note, the first row is descriptive column names that I would like to have as the SQL long column names.
With this spreadsheet open and the data selected, I now open the ACS data transfer program, select the 'To IBM i' tab and select 'Active Excel Spreadsheet' from the 'Input Device' list. This example does not include any date, time or timestamp data but if it did, the next step is to click the 'Properties' button and select the option 'Convert spreadsheet date and time cell to IBM i date or time'. If you don't select his before running the file creation wizard, the wizard will not correctly interpret these types of data (because Excel stores all of them as large integer values internally).
Next, I select the option 'Create IBM i Database File...' from the Actions menu to launch the table creation wizard. Click Next on the first screen. The second screen shows the name of the fdfx file that it creates on completion of the wizard steps.
- A quick aside about fdfx files...
Now is a good time to explain what the fdfx file is and why we need it. PC files are normally just streams of data that has no external description to ascribe meaning to the data that is in them. While an argument could be made that Excel files and other document types are an exception, as far as data transfer is concerned, we do not really care. So the fdfx file is a second file that describes the data in your data file (in this case the spreadsheet). You could think of it as a file of metadata since it is data that describes data. The data transfer upload tools require these files so that they can parse the source data file into columns and rows and know what their type and size are.
On the next panel, you can specify a starting and ending cell to define the range of data to be scanned. Since I already selected the range, this feature is disabled for me. If you need to, you can override the highlighted area by using this panel then click Next.
You are now on the panel in the wizard where you can have ACS data transfer scan the spreadsheet. The scan determines how many columns, their size, and data type for your table on IBM i. Since my first row is descriptive names, I check the box 'First row of data contains field names'. Note, my names are valid SQL long names. They do not contain any spaces and are not using any of the reserved words documented in the SQL Reference. Next, I click the 'Start Scan' button and when it is complete, I click the Next button to see what the scan produced.
Note from Figure 2 that it used my column names. Most of them are more than 10 characters long so the system name is the first 5 characters of the name I gave it followed by 5 digits. In this case, I want to modify some of these values. I would like my ItemNumber to be 20 characters in length and the Description column to be up to 2000 characters and be a VARCHAR rather than a CHAR type. I also want to add descriptive text for the columns. So I click the ItemNumber row in the table in Figure 2 and click the Details button and make the changes shown in Figure 3. I changed the length to 20, made sure that it is not Null capable and added a description of 'IBM i kitch Item Number', which is the label on this column. In Figure 4, you can see that I made the Description column a VARCHAR(2000) field. I also selected the option to make it null capable so this field does not need to have a value for every row in the table. I went on to modify the ItemCost and ItemsOnHand columns to give them labels and provided a default value of 0.00 and 0. If you have a column that is not null capable and your input file has no value for it, the default value is used if one is specified by the description of the table. After I make the appropriate changes, I click the Next button.
In the next panel in the wizard, you select the IBM i system on which the table is created. After you select the IBM i system you want, press the Next button to get the panel where you define the library and file for the new file to be created. After you complete the library and file names, press the Next button. In this panel, you can add descriptive text for your file. I called mine 'IBM i kitch'. Press the Next button to get to the confirmation panel. On this last panel, you confirm the system, library, file, and the name of the fdfx file to be created. It all looks good so I press the Next button to have the wizard create these files for me. The last panel is a confirmation panel and it simply confirms the success of the wizard. Press the Finish button to close the wizard.
You are now returned back to the data transfer 'To IBM i' tab that is completed with the system, library, table, and fdfx file that were created. All I need to do now is to press the 'Start Transfer' button. It uploads my data to the newly created IBM i file.
Case 2: Upload Data From An Active Excel Spreadsheet To An Existing Table Using ACS Data Transfer
In this case, I have the following spreadsheet
I need to upload the data in it to the KITCHCUST table on the IBM i that exists with the following DDL:
CREATE TABLE MIKSWENS.KITCHCUST (
CUSTOMERID INTEGER NOT NULL ,
CUSTOMERNAME FOR COLUMN CUSTNAME VARCHAR(200) CCSID 37 NOT NULL ,
CUSTOMERPHONE FOR COLUMN CUSTPHONE CHAR(20) CCSID 37 NOT NULL ,
CONSTRAINT MIKSWENS.Q_MIKSWENS_KITCHCUST_CUSTOMERID_00001 PRIMARY KEY( CUSTOMERID ) )
RCDFMT KITCHCUST ;
LABEL ON TABLE MIKSWENS.KITCHCUST
IS 'Kitch Customers' ;
LABEL ON COLUMN MIKSWENS.KITCHCUST
( CUSTOMERID TEXT IS 'Kitch Customer ID',
CUSTOMERNAME TEXT IS 'Kitch Customer Name',
CUSTOMERPHONE TEXT IS 'Kitch Customer Phone Number' ) ;
GRANT ALTER , DELETE , INDEX , INSERT , REFERENCES , SELECT , UPDATE
ON MIKSWENS.KITCHCUST TO MIKSWENS WITH GRANT OPTION ;
There are a couple of issues here to consider. First, the column names I have in the first row of my spreadsheet do not match the column names in the file so I simply do not select them. Second, I do not have a fdfx file that defines my spreadsheet data so I cannot upload into an externally described table. Therefore, I have to start by doing a download from the KITCHCUST table to an active spreadsheet so that the download utility creates the proper fdfx file for me. I set up the download like the following:
I must click the Details button to enable the option to save the fdfx file and give the fdfx a name, I do not care about the rest of these features.
But most importantly, I do not really want to download the data from the i, and I sure do not want it to overwrite the data in my spreadsheet. Back on the main transfer panel, I click the 'Data Options' button and add a Where clause to define a selection criteria (1=0) which prevents any data from being selected.
Then, I click the OK button in the Data Options window and press the 'Start Transfer' button. It results in a message that says, "No data was matched to the specified options." but we know that was intentional.
Next, click the button on the toolbar that is a green arrow pointing up which starts a new data transfer to the IBM i. We specify that the input device is 'Active Excel Spreadsheet', specify the IBM i system that we want to upload to, and the library and file (MIKSWENS/KITCHCUST). Then, we click the details button and likely see that the 'Use client file description' option is selected but the file name is not correct. Recall I named my fdfx with name KitchCustomers.fdfx. So I click the browse button and I find the fdfx file I created in the default location. I can click the Advanced button and confirm that the starting and ending positions are correct and I see that while they are disabled, they match the range that was selected in Figure 5
If the table has any date, time, or timestamp columns, click the 'Properties' button and select the option, 'Convert spreadsheet date and time cell to IBM i date or time'. Finally, click the OK button in the upload file details window and the 'Start Transfer' button in the main window. If the types and sizes of the data in your spreadsheet match the column definitions for the table on the IBM i, it uploads your data.
Related Information
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z000000cwMLAAY","label":"Data Access-\u003EData Transfer"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
More support for:
IBM i
Component:
Data Access->Data Transfer
Software version:
All Versions
Document number:
667343
Modified date:
10 January 2025
UID
nas8N1021503
Manage My Notification Subscriptions