Importing Consecutive Files

APPX Versions 5.1 & higher

In 5.1 a number of APIs were introduced that make the task of importing data easier.

For example, if we have data in an Excel spreadsheet, you could import it using the following technique:

1. Save the file in a tab delimited format. Tabs are the easiest way to identify the different fields.
2. Upload the file to the APPX server. You can prompt the user for the location of the file on their PC, then use .CLIENT UPLOAD FILE to transfer the file to a location of your choice on the server. If you are running 5.2 or later, you can use Drag & Drop to make it easier for the user to upload the file.
3. Once the file is on the server, open it using .STREAM OPEN, and read each line using .STREAM READ.
4. For each line, use .TEXT PARSE LINE to break it down into individual fields. Move the fields to their final destination, or perform whatever processing is required.
5. Remember to use .STREAM CLOSE to close the file after you have read all the lines, otherwise APPX will leave the file open until the session ends.

APPX Versions Prior to 5.1

Here are three approaches:

1. If the file is "columnar" (all the data lines up into columns, and all the records have the same fixed length), define an APPX Data Dictionary file and fields corresponding to the columns for the fields, and read it directly. Have a 2-byte alpha field at the end, the 'absorb' the hex(0d0a) that Windows apps usually put on the end of each of the line, to denote end of line. See "to import a flat file" below.
2. Create an APPX consecutive file with a single 1-byte alpha field. Use it to read the text file into APPX, one byte at a time. Build a record buffer, testing for hex(0d0a) to determine the end of each record, and then writing it out to the file.
3. Using the ",RT_" commands to read the file. 'RT' commands are unsupported and subject to modification, but are often used for these sorts of tasks. We use them in the UPDATE process generated by our "Comma Delimited Update" process within Utilities/Toolbox. You might look at an UPDATE generated by this utility, to get a feel for how the commands work.

To import Tab delimited variable length files into APPX, check this document from "Sparky and Spike" at CANSYS West.

To import a flat file into APPX (for example, from an Excel spreadsheet) try the following:

1. Export the file from Excel into a fixed length format.
2. Define a Consecutive file in APPX, with all Alpha fields, matching the column widths of each Excel column, as exported.
3. Add a 2 byte alpha at the end of the APPX DD, to receive the hex(0d0a) that Excel and other Windows applications append to records to denote record boundaries.
4. Design Transfer the design for the Consecutive file defined above into a 2nd APPX file.
5. Change the field types you want to be other than Alpha, into their other field type. (Numeric, Date, or whatever.) Add indices.
6. Run 'Create Files' on both the Consecutive and Indexed files.
7. FTP the file to your Unix box in ASCII format.
8. Move the FTP'd file into your Data directory as the name of the Consecutive f ile. (Filename is all upper case, with a lower case .dat suffix.)
9. Write an OUTPUT that reads the Consecutive file and reports on its contents.

   Within the 'Select Image' event point, write ILF that moves field contents from the Consecutive file into the Indexed file. Write the new record.

   If you try to write two records with the same Primary or Unique Key, the WRITE will fail. Check the False condition on a WRITE to catch that and handle it accordingly.

   Set --- ALTERNATE IMAGE NUMBER in Select Image to selectively display/print records of interest.

   Date fields will require special manipulation. Use SET TEMP commands, to get them into APPX date format.
   

10. Run the above OUTPUT on this FTP'd file, converting it from Consecutive into Indexed format. Examine the resulting files. Modify your OUTPUT as needed.

Comments:

Read what other users have said about this page or add your own comments.