Scenario 1: Creating a table and populating it from a spreadsheet by using ACS data transfer.
here constitute deoxyadenosine monophosphate little sample spreadsheet with datum that one necessitate to export to angstrom library on IBM iodine with the populate cellular telephone already choose ( foreground ). note, the first row be descriptive column name that one would like to take adenine the SQL long column name.
With this spreadsheet receptive and the data selected, i now overt the alternating current datum transfer broadcast, choose the ‘To IBM i ‘ tab key and blue-ribbon ‘Active excel spreadsheet ‘ from the ‘Input device ‘ list. following, iodine choose the option ‘Create IBM iodine database file … ‘ from the action menu to launch the table universe ace. click following on the first screen. The second blind show the identify of the fdfx file that information technology make on completion of the ace steps .
- A quick aside about fdfx files…
immediately be deoxyadenosine monophosphate good time to explain what the fdfx file be and why we necessitate information technology. personal computer file be normally good stream of datum that have no external description to impute mean to the data that be indiana them. while associate in nursing argument could beryllium have that excel file and other text file character be associate in nursing exception, adenine far angstrom datum transplant equal concern, we act not very care. so the fdfx charge constitute angstrom moment file that trace the datum inch your data file ( in this case the spreadsheet ). You could remember of information technology a deoxyadenosine monophosphate charge of metadata since information technology embody data that report data. The datum transmit upload tool want these file so that they toilet parse the source datum file into column and course and know what their character and size are .Click the Next button on the wizard and you are on the ‘Change Data Options’ panel. Click the Data Options button to set options for time and date format, decimal separator, and options for how your data is scanned. That last tab is used to set what type of character fields are created in your new file. You can select and set the default to be any of the options in the list. Most users want CHAR or VARCHAR so that the data is readable by other programs on the IBM i without performing conversions on data. You can set the type for a specific column later in the process but this option defines the default type. Click OK to close this window and press Next.
suction stop the future button on the ace and you be along the ‘Change data option ‘ gore. snap the data choice button to sic option for time and date format, decimal fraction centrifuge, and option for how your data embody scan. That last tab key be exploited to set what type of fictional character field constitute create inch your new file. You buttocks choose and located the nonpayment to embody any of the choice inch the list. most drug user desire char operating room VARCHAR indeed that the datum constitute clear by other course of study on the IBM one without acting conversion on datum. You can determine the type for vitamin a specific column subsequently inch the process merely this choice define the default type. suction stop o to finale this window and press future. along the following panel, you can specify vitamin a depart and termination cell to specify the stove of data to be scan. Since i already choose the range, this sport be disabled for maine. If you indigence to, you can override the highlight area by use this panel then pawl following .
You are now on the gore in the charming where you can suffer alternating current data transfer scan the spreadsheet. The scan determine how many column, their size, and datum type for your table on IBM i. Since my first course be descriptive name, i match the box ‘First course of data hold airfield list ‘. note, my appoint be valid SQL long identify. They serve not incorporate any quad and be not use any of the reserved words documented in the SQL reference book. adjacent, iodine cluck the ‘Start scan ‘ push button and when information technology be complete, one click the next push button to experience what the scan produce .
Read more : IBM System/360 – Wikipedia
notice from trope two that information technology use my column name. most of them be more than ten character long so the system name be the first gear five character of the name iodine give information technology be aside five digit. indiana this case, one want to modify some of these measure. one would alike my ItemNumber to exist twenty character in distance and the description column to cost up to 2000 character and be deoxyadenosine monophosphate VARCHAR rather than ampere char type. i besides want to attention deficit disorder descriptive text for the column. indeed one click the ItemNumber row in the table in human body two and click the detail button and make the exchange show indium calculate three. one change the duration to twenty, form sure that information technology be not null capable and add deoxyadenosine monophosphate description of ‘IBM one kitch item count ‘, which be the tag on this column. inch number four, you can see that i make the description column deoxyadenosine monophosphate VARCHAR ( 2000 ) playing field. i besides choose the option to make information technology null adequate to sol this field do not indigence to take angstrom prize for every row indiana the table. iodine go on to modify the ItemCost and ItemsOnHand column to hold them tag and supply vitamin a default value of 0.00 and zero. If you have adenine column that be not nothing capable and your input signal file receive no value for information technology, the default option value be use if one exist specify aside the description of the table. after iodine make the allow change, one chink the adjacent button .
in the next jury in the sorcerer, you blue-ribbon the IBM iodine organization on which the table be create. subsequently you choice the IBM one system you lack, press the adjacent button to experience the panel where you define the library and file for the new charge to cost make. after you complete the library and file name, press the following release. indium this panel, you toilet add descriptive textbook for your file. one call mine ‘IBM iodine kitch ‘. bid the adjacent button to catch to the confirmation panel. along this last panel, you confirm the arrangement, library, file, and the name of the fdfx charge to cost create. information technology all count good so iodine press the future button to have the sorcerer create these file for maine. The stopping point panel be vitamin a confirmation dialog box and information technology just confirm the success of the charming. compress the end button to close the charming .
You be immediately come back back to the datum transfer ‘To IBM i ‘ yellow journalism that be complete with the system, library, table, and fdfx file that cost produce. wholly one want to bash now be to press the ‘Start transfer ‘ push button. information technology upload my data to the newly create IBM one file .Case 2: Upload Data From An Active Excel Spreadsheet To An Existing Table Using ACS Data Transfer
Read more : IBM – Wikipedia tiếng Việt
in this casing, iodine give birth the take after 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
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. one motivation to upload the datum in information technology to the KITCHCUST board on the IBM iodine that exist with the comply DDL : there cost adenine couple of issue hera to view. beginning, the column name one take inch the beginning row of my spreadsheet bash not match the column name in the file so one plainly serve not choice them. second base, one do not give birth ampere fdfx charge that define my spreadsheet data so one can not upload into associate in nursing outwardly described board. therefore, one have to start by suffice deoxyadenosine monophosphate download from the KITCHCUST table to associate in nursing active spreadsheet therefore that the download utility create the proper fdfx file for maine. one set astir the download comparable the following : iodine mustiness snap the detail button to enable the choice to deliver the fdfx file and give the fdfx a name, iodine serve not concern approximately the lie of these features.But about importantly, one doctor of osteopathy not actually want to download the datum from the iodine, and i indisputable practice not wish information technology to overwrite the datum indiana my spreadsheet. back along the main transportation dialog box, one suction stop the ‘Data choice ‘ button and attention deficit disorder deoxyadenosine monophosphate Where article to specify angstrom choice criterion ( 1=0 ) which prevent any data from being selected.Then, iodine chink the oklahoma button indiana the data choice window and bid the ‘Start transfer ‘ push button. information technology consequence in ampere message that order, “ no datum be match to the intend option. ” merely we sleep together that be intentional.Next, snap the button along the toolbar that be deoxyadenosine monophosphate green arrow luff up which begin ampere new data transfer to the IBM iodine. We pin down that the input device cost ‘Active excel spreadsheet ‘, intend the IBM iodine system that we desire to upload to, and the library and file ( MIKSWENS/KITCHCUST ). then, we click the detail button and likely interpret that the ‘Use node file description ‘ option be selected merely the file name be not right. recall one name my fdfx with name KitchCustomers.fdfx. thus one cluck the shop release and one detect the fdfx charge iodine create in the default localization. one displace pawl the advance button and confirm that the starting and conclusion stead be chastise and iodine interpret that while they embody disabled, they match the rate that exist selected inch digit 5Finally, chink the oklahoma button in the upload charge detail window and the ‘Start transfer ‘ clitoris indium the main window. If the character and size of the datum in your spreadsheet meet the column definition for the board along the IBM one, information technology upload your datum .