ImportingConsecutiveFiles < Main

Tags: view all tags
---+ 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.
   1 Upload the file to the APPX server. You can prompt the user for the location of the file on their PC, then use <a href="0LASubrClientUploadFile" target="_blank">.CLIENT UPLOAD FILE</a> to transfer the file to a location of your choice on the server. If you are running 5.2 or later, you can use <a href="520DragDrop" target="_blank">Drag & Drop</a> to make it easier for the user to upload the file.
   1 Once the file is on the server, open it using <a href="0LASubrStreamOpen" target="_blank">.STREAM OPEN</a>, and read each line using <a href="0LASubrStreamRead" target="_blank">.STREAM READ</a>.
   1 For each line, use <a href="0LASubrTextParseLine" target="_blank">.TEXT PARSE LINE</a> to break it down into individual fields. Move the fields to their final destination, or perform whatever processing is required.
   1 Remember to use <a href="0LASubrStreamClose" target="_blank">.STREAM CLOSE</a> to close the file after you have read all the lines, otherwise APPX will leave the file open until the session ends.
The above steps could be in a stand alone subroutine, or you could incorporate them into an Output process.
---++ 
---++ APPX Versions Prior to 5.1

<span data-mce-mark="1"><a name="OLE_LINK4"></span><span data-mce-mark="1"></a></span><span data-mce-mark="1"><a name="OLE_LINK9"></span>Here are three approaches:<span data-mce-mark="1"></a></span>

<span data-mce-mark="1"><a name="OLE_LINK9"></span> <span data-mce-mark="1"></a></span>
   1 <span data-mce-mark="1"><a name="OLE_LINK9"></span>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 <span data-mce-mark="1"></a></span>"to import a flat file" below.
   1 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.
   1 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.

<span data-mce-mark="1" style="font-size: x-small;">To import Tab delimited variable length files into APPX, check <a href="http://www.cansyswest.com/importing.php" target="_blank">this document</a> from "Sparky and Spike" at CANSYS West</span><span data-mce-mark="1" style="font-size: x-small;">.</span>

<span data-mce-mark="1"><a name="ToImportAflatFile"></span> <span data-mce-mark="1"></a></span>

<span data-mce-mark="1"><a name="ToImportAflatFile"></span> <span data-mce-mark="1"></a></span>
---

<span data-mce-mark="1"><a name="ToImportAflatFile"></span> To import a flat file<span data-mce-mark="1"></a></span> into APPX <em><span data-mce-mark="1" style="font-size: x-small;">(for example, from an Excel spreadsheet)</span></em> try the following:
   1 Export the file from Excel into a fixed length format.
   1 Define a Consecutive file in APPX, with all Alpha fields, matching the column widths of each Excel column, as exported.
   1 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.
   1 Design Transfer the design for the Consecutive file defined above into a 2nd APPX file.
   1 Change the field types you want to be other than Alpha, into their other field type. <em><span data-mce-mark="1" style="font-size: x-small;">(Numeric, Date, or whatever.)</span></em> Add indices.
   1 Run 'Create Files' on both the Consecutive and Indexed files.
   1 FTP the file to your Unix box in ASCII format.
   1 Move the FTP'd file into your Data directory as the name of the Consecutive f ile. <em><span data-mce-mark="1" style="font-size: x-small;">(Filename is all upper case, with a lower case .dat suffix.)</span></em>
   1 Write an OUTPUT that reads the Consecutive file and reports on its contents. <p>Within the 'Select Image' event point, write ILF that moves field contents from the Consecutive file into the Indexed file. Write the new record.</p> <p>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.</p> <p><span data-mce-mark="1" style="font-size: x-small;"> _Set_ </span> =--- ALTERNATE IMAGE NUMBER= <em><span data-mce-mark="1" style="font-size: x-small;"> in </span></em> =Select Image= <em><span data-mce-mark="1" style="font-size: x-small;"> to selectively display/print records of interest. </span></em></p> <p><span data-mce-mark="1" style="font-size: x-small;"> _Date fields will require special manipulation. Use_ </span> =SET TEMP= <em><span data-mce-mark="1" style="font-size: x-small;"> commands, to get them into APPX date format. <br /></span></em></p>
   1 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._
---

<br /><span data-mce-mark="1">%COMMENT%</span>
Topic revision: r3 - 2016-02-18 - JeanNeron