UploadSpreadsheetData < Main

Tags: view all tags
---+ Uploading Spreadsheet Data to APPX

You have data in a spreadsheet that you want to transfer into APPX data files. There are certainly several ways to go about doing that. Here's one ...

*Note: This example uses older subroutines. If you are running APPX 5.1 or higher, we recommend:*

| *Old Routine* | *New API* |
| GET CONFIGURATION INFO | <a href="0LASubrEnvGetAppxpath" target="_blank">.ENV GET APPXPATH</a> |
| GET FILE FROM CLIENT | <a href="0LASubrClientUploadFile" target="_blank">.CLIENT UPLOAD FILE</a> |
| STREAM OPEN | <a href="0LASubrStreamOpen" target="_blank">.STREAM OPEN</a> |
| STREAM READ | <a href="0LASubrStreamRead" target="_blank">.STREAM READ</a> |
| STREAM CLOSE | <a href="0LASubrStreamClose" target="_blank">.STREAM CLOSE</a> |
| Parsing routines | <a href="0LASubrTextParseLine" target="_blank">.TEXT PARSE LINE</a> |
 

*The DMO application has a more recent example of importing data, see the Input IMPORT PROSPECTS.*
---++ Export the Data

Choose the “Save As” or “Export” options from within Excel (or Open Office equivalent), and create a delimited file on your desktop. I’d recommend tab-delimited as opposed to comma-delimited, as it is much cleaner since cell data typically does not include any tab characters.
---++ Upload the File to the Server

Use an input process with a file-chooser field to select the file on the desktop that you want to upload. You have to tell APPX where to put the file on the server, and it has to be someplace that is not going to be locked because of permissions. I use the APPXPATH for that purpose. So …

In the <span style="text-decoration: underline;">Start of Process</span> event point, include this:

<verbatim>      SUBR     --- GET CONFIGURATION INFO           SUBPROCESS   END? N  FAIL 0</verbatim>

The screen includes a file-chooser field, which we will call WORK DESKTOP FILE NAME. We are going to use configuration info to build a temporary storage path for the file on the server, with a date time stamp to ensure that we don’t overwrite an existing file, in a field called WORK SERVER FILE NAME. When the user enters a path into the field on the screen and hits the proper option key, the following code is triggered in <span style="text-decoration: underline;">Option Intercept</span>:

<verbatim>      SET      --- CONFIG TYPE                =      APPXPATH
      READ     --- CONFIG                 HOLD 0 FT 0 BY CONFIG TYPE
F     CANCEL   Unexpected Error Reading CONFIG
      SET      XXX WORK SERVER FILE NAME      =  --- CONFIG DATA
      APPEND   XXX WORK SERVER FILE NAME      0      /tmp/TransferFile-
      SET DATE XXX WORK DATE TIME STAMP
      SET      --- TEMP 32                    =  XXX WORK DATE TIME STAMP
      APPEND   XXX WORK SERVER FILE NAME      0  --- TEMP 32
      *
      PASS     XXX WORK DESKTOP FILE NAME     FIELD            SHARE? Y
      PASS     XXX SERVER FILE NAME           FIELD            SHARE? N
      PASS     --- AI                         FIELD            SHARE? Y
      *
      SUBR     --- GET FILE FROM CLIENT             DETACHED     END? N  FAIL 0
      *
      IF       --- AI                         EQ     1
F     ERROR    Upload unsuccessful - feel sad.</verbatim>

Note: The APPEND statement that includes /tmp/TransferFile- is formatted for a Linux server. Windows servers will require a backslash separator and probably a different target folder.

Barring any unexpected situations, we now have the tab-delimited file on the server, located at WORK SERVER FILE NAME.
---++ Parse the Uploaded File for the Desired Data

The next step is to use the STREAM commands to read each row in the uploaded file. We know that the first row contains column headings, so we will skip past that row. If you prefer, though, and you want to get elegant, you can import them into a separate file, and use them to decide which data goes into which field. But for our example, we’ll assume that the columns are preset to always have the right data in the right place.

By the way, you want to be careful about your column headings – make sure they don’t have any line feeds in them, or it can throw the whole thing off. You can see that if you look at the tab-delimited file on your desktop via WordPad or NotePad. With Word Wrap off, if the second line doesn’t start with data, check and edit those column headings.

So, first we have to define a couple of fields and open the file for reading:

<verbatim>      SET      XXX WORK FIRST TIME THRU       =      Y
      CNV BIN  XXX WORK TAB CHARACTER         =      9
      SET      --- STREAM OPEN TYPE           =      r
      SET      --- STREAM FILE PATHNAME       =  XXX WORK FILE PATH NAME
      GOSUB    --- STREAM OPEN
      IF       --- STREAM RETURN CODE         NE     0
T     ERROR    Something went wrong, don't continue
      *
      LABEL    READ STREAM FILE
      SET      --- STREAM READ LENGTH         =
      GOSUB    --- STREAM READ
      *
      *        Return Code will be 0 if some data was returned.
      *        Return Code will be 1 if EOF reached or other error occurs.
      *        --- STREAM BUFFER contains the data that was read
      *        --- STREAM READ LENGTH = number of bytes actually returned
      *
      IF       --- STREAM RETURN CODE         EQ     0
T     IF       XXX WORK FIRST TIME THRU       EQ     Y
TT    SET      XXX WORK FIRST TIME THRU       =      N
TF    GOSUB    XXX FILE PARSE
T     GOTO     READ STREAM FILE
      *
      GOSUB    --- STREAM CLOSE</verbatim>

Each time we found a line of data, we called a subroutine called XXX FILE PARSE. Here’s what that subroutine might contain:

<verbatim>      SET      XXX WORK TAB POSITION      001 =      0
      RESTORE  XXX DATAFILE                   DEFAULT RECORD
      *
      GOSUB    XXX FILE PARSE SUB GET NEXT FIELD
      SET      XXX DATAFILE LAST NAME         =  --- TEMP 32K
      GOSUB    XXX FILE PARSE SUB GET NEXT FIELD
      SET      XXX DATAFILE FIRST NAME        =  --- TEMP 32K
      GOSUB    XXX FILE PARSE SUB GET NEXT FIELD
      SET      XXX DATAFILE EMAIL ADDRESS     =  --- TEMP 32K
      GOSUB    XXX FILE PARSE SUB GET NEXT FIELD
      *        Keep setting fields like that as needed
      WRITE    XXX DATAFILE               FAIL 0</verbatim>

The last piece of the puzzle is the FILE PARSE SUB GET NEXT FIELD subroutine. And that looks like this:
<verbatim>      IF       --- STREAM BUFFER              IN XXX WORK TAB CHARACTER
T     SET      XXX WORK TAB POSITION      002 =  --- TEXT AT POSITION
F     SET      XXX WORK TAB POSITION      002 =  --- STREAM READ LENGTH
F     COMPUTE  XXX WORK TAB POSITION      002 +      1
      *
      *        --- LI is field length
      SET      --- LI                         =  XXX WORK TAB POSITION      002
      COMPUTE  --- LI                         -  XXX WORK TAB POSITION      001
      COMPUTE  --- LI                         -      1
      *
      *        --- PI is location of first importable character
      SET      --- PI                         =  XXX WORK TAB POSITION      001
      COMPUTE  --- PI                         +      1
      SET      --- TEMP 32K                   =
      SET TEMP 32K   AT 001 FOR LI  FROM PI  OF  --- STREAM BUFFER
      *
      *        Replace second tab with blank, find next tab
      *
      SET      --- TEMP 1                     =
      SET      --- PI                         =  XXX WORK TAB POSITION      002
      SET TEMP 1     AT 001 FOR 001 INTO PI  OF  --- STREAM BUFFER
      SET      XXX WORK TAB POSITION      001 =  XXX WORK TAB POSITION      002
      *</verbatim>

And that’s it. You may need to do some tweaking in the XXX FILE PARSE subroutine to handle numeric fields, date fields, and so on.

Please note that APPX 5.1 has an excellent new subroutine called .TEXT PARSE LINE, which can be called instead of the FILE PARSE SUB GET NEXT FIELD subroutine above. I highly recommend using that if you’re on APPX 5.1 or later.

-- Main.AlKalter - 2012-06-19
Topic revision: r4 - 2017-10-27 - JeanNeron