IBM I-series Data Download
I run a query in query/400 and tried to download that file using IBM Iseries. in the downloaded file, the column headers are in field ids and not the descriptions i have given in Query/400 report column formatting. Is there any way to download a file with column descriptions given in query? Please suggest.
Software/Hardware used:
as/400 IBM I series
Software/Hardware used:
as/400 IBM I series
ASKED:
February 10, 2012 4:25 AM
UPDATED:
March 17, 2012 1:38 AM
Answer Wiki:
If your database has already been created and do not want to go back and change the Query definition, then use Client Access to create the column headings. After you enter the iseries file name to transfer, select the Data Option tab. The tab will bring up the Change Data Option screen . It is here where you can enter all the fields on the Select statement and use the as clause to change the field heading. Ex: Select ssn as SOCIALSECURITY, birth as Birthdate. If you do not know how to build the SQL select statement, Select the Details tab to bring up the Receive Request Details screen. This scree will let you select each field one at a time. The select clause can be added behind each field selected.
To see all answers submitted to the Answer Wiki: View Answer History.
I assume you are trying to pull the file into an excel spread sheet. For quick one time programs, I like to use a free third party utility called CVTPFXLS. You can download it from http://reocities.com/siliconvalley/pines/5581/tips.htm.
If I need more control over how the data is formated or I want to have the excel sheet automatically created, then I use the POI API’s. You can read about those at http://www.easy400.net/hssfcgi/documentation/index.html.
…and not the descriptions i have given in Query/400 report column formatting.
The column descriptions are used by Query/400 to print a readable report.
But you didn’t download the report — you downloaded a file.
The column headings are stored as attributes of the columns in the file object as the column TEXT. If it is necessary, it can be retrieved by the PC from the system database catalog. Use ODBC to retrieve the COLUMN_TEXT column of the SYSCOLUMNS view in library QSYS2.
But I don’t know of any way to retrieve the text of a column as part of an iSeries Access data transfer of the file. The field names can be transferred with the data, but that’s the only attribute that can go along.
Tom