Exporting Data

 


Besides running query results to the SQLThing Data Window, you can also export results to various PC file formats.  SQLThing supports the following PC formats:

  •  HTML

  •  Lotus 123 and WKS files

  •  Excel Spreadsheet Files

  •  Access Databases

  •  Paradox Databases

  •  dBase databases

  •  Text files

To export a statement, you use the Export Wizard available from the Statement menu. Type in a statement like you would normally do if you were interested in viewing results or doing performance verification.  If fact, I find it useful to attempt to execute the statement first before attempting to export to ensure that the statement is working. Once you have a working statement, select the Statement menu and then select Export Current and then Wizard.  The Export Wizard screen should appear.

Use the Export Type dropdown list to select the type of PC file you wish to write to. Certain PC file types cause the dialog boxes to behave differently. For example, if exporting to Paradox or Access, you must specify the database path and filename in the Output Database Path or ISAM Filename field and just place a table name in the Export Table Name field. If you are exporting to a single file, (Excel, Lotus or dBase type exports), the Output Database Path or ISAM Filename field will contain the directory where you want the file to live, and the table name will become the file name of the stored data.  Pressing the button brings up a browser window that you can use to select a file or database.

Once you have completed this screen, select the Setup Table tab. A screen like the one shown below will be displayed.

You can change the name of a column in the exported file by editing the Target Col Name field in the Table Structure Grid.  You can also edit the type description and length parameters. Once you are satisfied, press the Export button to continue.

At this point, SQLThing will attempt to connect to the PC database and create your table.  If there are any errors, this process will terminate and a message will be displayed.  You should correct the problem and then attempt to export again. Common problems are using reserved words for field names or using an incorrect data type for the target database.  SQLThing does a pretty good job of type translation between AS/400 and PC databases, so you should not have to edit the data types too extensively.

If the table is successfully created, SQLThing will proceed to read all records that satisfy your query from the AS/400 and write them to the target file/database. Any errors will be stored in a collection that can be dumped to a PC text file after the export is complete.