#import

Many programs and databases (Microsoft Excel or Access, Lotus 123 etc) allow you to export data in many different formats. The #import command allows you to import the most common formats and generate output to your specification.

The default output format for quite a few of the import types is a HTML table. Not only don't you need to generate a table at all, you also have a lot of fine tuning parameters if you wish to remain with a table but just wish to tweek its look (give certain columns a special background color etc). I have seen people generate a whole series of #define and #include commands to generate a separate html page for each record (from a product database).

This command might seem complicated at first (and in actual fact it is!), I recommend you actually try the examples (cut and paste as required). The "#data" command is another approach for importing data (note that this command can also import into #data structures)...

If you are having problems working out what is going on I highly recommend the use of debugging (/debug or #debug) as this has been designed not just for debugging but for training as well. Note I recommend that if debugging you cut your database down to a few test records so that you won't be swamped with output.

Import Processing Details

All import types get handled in two distinct passes as follows:

1. The imported file is read, processed (including calls to your rexx filter code if defined) and the output is generated to a temporary file.

   The following locations are searched in order, using FindFileInPath():

   1. The Current Directory
   2. The Main Input Files Directory
   3. /INCLUDEPATH
      Any locations specified with the /IncludePath switch or IncludePath() routine.
   4. PPWIZARD_INCLUDE environment variable.
   5. INCLUDE environment variable.
   6. PPWIZARD Install Directory

   If you have a filter then in some advanced applications you may need to use the WriteLineToTmpImportFile() routine. An example of this is if you wished to generate PPWIZARD commands such as #output.

2. The temporary file is read in and processed in exactly the same manner as any file that you yourself might include with a #include command. This is where any macros you may have referenced are expanded.

   By default, if debug is not on then after pass two completes the temporary file is deleted. Turn debug on and examine the temporary file if things are not going to plan! This will tell you at which of the 2 stages your import is failing to work correctly.

Syntax

[WhiteSpace]#import  ["]File2Import["]  ["]T2H|WRAP["]  ["]DefineName["]    OR
[WhiteSpace]#import  ["]File2Import["]  ["]ImportType[-]["]  ["]DefineName["]  ["]FieldInfo1["]  ...

The parameters are as follows:

1. File2Import
   This is the name of the file to be imported. Blank lines are ignored (is this causing anyone problems?).

2. ImportType
   This describes the format of the data to be imported. If the type has "-" appended then the first lines of the file are dropped (default is one line). The currently supported types are:
   • SQL
     PPWIZARD can perform any SQL query you like and process the resulting rows.
   • CMA[-]
     All fields are comma delimited (fields can't contain commas unless quoted). Fields may be empty. Blank trailing fields may be missing. Under windows you can also import this type of file via SQL!
   • TAB[-]
     As above except a tab (ascii 9) is the delimiter. Under windows you can also import this type of file via SQL!
   • ???[-]
     As above except you specify the exact delimiter you want. For example "^^^" indicates that you wish a delimiter of "^". Under windows you can also import this type of file via SQL!
   • FIX[-]
     Unlike all previous types there is no delimiter. Items appear in fixed columns in the file. While you can play with "FieldInfo" there is not much point as you can specify all the fields you want in the order you want them. Under windows you can also import this type of file via SQL!
   • ML
     Each record spans multiple lines and ends with a blank line. Each field takes up a line and specifies a field name and a value separated by a user defined delimiter ('=' by default).
   • T2H
     This is a text to HTML type import.
   • WRAP
     This is a specialised import facility. You get passed a line at a time and you decide how to handle it.

3. DefineName
   This parameter gives you a lot of control over how your output is generated. The value you supply here is used as a prefix for all your import options. As an example, if you specified "FRED" as a value then some import types would look for a #define of "FRED_DROP_BLANK_LINES" to see if you wished to override the default handling of blank lines.

   The exact #define options which are available will differ depending on the import type. If you specified a blank value for the prefix a default is used. The name of the default also varies with import type but for the common types is "IMPORT".

   In some cases such as when HTML tables are generated there are multiple levels of defaults and changing a lower level one may have no effect if you also override a higher one. This is because the lower level values became the default values for the higher level. As an example there is no point specifying that record columns should have a background color of green and then overriding the record generation default! Again the use of debugging support will generally make this clear as it will show PPWIZARD looking for all options and display the value PPWIZARD has decided to use.

EXAMPLE - "Inline" database

This code demonstrates that the data being imported can be imbedded inline with your page and does not have to come from a separate file:

;--- Create temporary file with "inline" CSV ---
#DependsOn TEMP '<?TmpDir>\Tmp.Csv'  ;;No dependancy on the temporary file!
#output '<?TmpDir>\Tmp.Csv' ASIS     ;;Open temp file
   ;--- Include each row -----------------------
   db0@bnz.com,Dennis Bareis,http://www.labyrinth.net.au/~dbareis/index.htm
   fred@any.com,Fred Nerk,http://www.fred.com
   another@any.com,Another Guy,http://www.another.com
#output                              ;;Close temp file

;--- Import the information --------------------
#(
   #import "<?TmpDir>\Tmp.Csv" CMA ""
           "Email<BR>Address"
           "Name"
           "Home<BR>Page"
#)

;--- Delete the temp file ----------------------
#evaluate ^^ ^call FileDelete '<?TmpDir>\Tmp.Csv'^

Example - Import to #data

Most import types can import into memory, this would allow manipulation of the data without requiring any rexx code:

;--- Import the information (store in memory) ---
#NextId
#define IMPORT_#DATA TmpCsv    ;;Import to memory
#(
   #import "SomeCommaSeparatedFile.CSV" CMA ""
           "EmailAddress"      ;;CSV Field #1
           "Name"              ;;CSV Field #2
           "HomePage"          ;;CSV Field #3
#)

;--- Start a table and output heading line ------
<table border=1>
   <tr>
       <th>Name</th>
       <th>Email<br>Address</th>
       <th>Home<br>Page</th>
   </tr>

;--- Output the imported records ----------------
#{ FOR @@Record = 1 to <?Data:TmpCsv.?>
   ;--- Is this an EVEN or ODD row? -------------
   #if @@Record // 2 = 0      ;;Can use CSS (stylesheet) to alternate background color etc
       #define+ TrClass EVEN
   #elseif
       #define+ TrClass ODD
   #endif

   ;--- Start row (set class to "EVEN/ODD") -----
   <tr class='<$TrClass>'>
      ;--- Output Name --------------------------
      <td><?Data:TmpCsv.@@Record.2></td>

      ;--- Output email address -----------------
      <td><a href="mailto:<?Data:TmpCsv.@@Record.1>"><?Data:TmpCsv.@@Record.1></a></td>

      ;--- Output homepage ----------------------
      <td><a href="<?Data:TmpCsv.@@Record.3>" target="_blank"><?Data:TmpCsv.@@Record.3></a></td>
   </tr>
#}

;--- End the table ------------------------------
</table>

The above generates one html table row per record, the following will place 3 records into a single html row:

;--- Create temporary file with "inline" CSV ---
#DependsOn TEMP '<?TmpDir>\Tmp.Csv'  ;;No dependancy on the temporary file!
#output '<?TmpDir>\Tmp.Csv' ASIS     ;;Open temp file
   db1@zyx.com,Dennis Fredric,http://www.dns1/index.htm
   fred1@any.com,Fred Nerk,http://www.fred1.com
   another1@any.com,Another Guy,http://www.another1.com
   db2@zyx.com,Dennis Fredric,http://www.dns2/index.htm
   fred2@any.com,Fred Nerk,http://www.fred2.com
   another2@any.com,Another Guy,http://www.another2.com
   db3@zyx.com,Dennis Fredric,http://www.dns3/index.htm
   fred3@any.com,Fred Nerk,http://www.fred3.com
   another3@any.com,Another Guy,http://www.another3.com
   db4@zyx.com,Dennis Fredric,http://www.dns4/index.htm
#output

;--- Import the information (store in memory) -------------------------------
#NextId
#define IMPORT_#DATA TmpCsv    ;;Import to memory
#(
   #import "<?TmpDir>\Tmp.Csv" CMA ""
           "EmailAddress"      ;;CSV Field #1
           "Name"              ;;CSV Field #2
           "HomePage"          ;;CSV Field #3
#)

;--- Start a table ----------------------------------------------------------
<table border=1>

;--- Output the imported records --------------------------------------------
#RexxVar @@RecsPerRow     = 3    ;;How many DB records fit in one table row?
#RexxVar @@RowCnt         = 0    ;;Number of rows generated
#RexxVar @@RecsCurrRow    = 0    ;;Have we started a row (0=NO, else #DB rows in it)?
#{ FOR @@Record = 1 to <?Data:TmpCsv.?>
   ;--- Need to start a new row? --------------------------------------------
   #if [@@RecsCurrRow = 0]
       ;--- Increment row count ---------------------------------------------
       #RexxVar @@RowCnt + 1

       ;--- Is this an EVEN or ODD row? -------------------------------------
       #if @@RowCnt // 2 = 0      ;;Can use CSS (stylesheet) to alternate background color etc
           #define+ TrClass EVEN
       #elseif
           #define+ TrClass ODD
       #endif

       ;--- Start row (set class to "EVEN/ODD") -----------------------------
       <tr class='<$TrClass>'>
   #endif

   ;--- Output this field ---------------------------------------------------
   #RexxVar @@RecsCurrRow + 1
   #( ''
      <td>
        ;--- Simple formatting (could use table etc) ------------------------
        <?Data:TmpCsv.@@Record.1>
        <br>
        <?Data:TmpCsv.@@Record.2>
        <br>
        <?Data:TmpCsv.@@Record.3>
      </td>
   #)

   ;--- At the end of a row? ------------------------------------------------
   #if  [@@RecsCurrRow = @@RecsPerRow]
        #RexxVar @@RecsCurrRow = 0
        </tr>
   #endif
#}

;--- The last row needs completing? -----------------------------------------
#if  [@@RecsCurrRow <> 0]
    ;--- Yes, just end row (could create columns to fill out first) ---------
    </tr>
#endif

;--- End the table ------------------------------
</table>

Example - Generate one HTML page per Record

;--- Import the information (store in memory) ---
#NextId
#define IMPORT_#DATA TmpCsv    ;;Import to memory
#(
   #import "SomeCommaSeparatedFile.CSV" CMA ""
           "EmailAddress"      ;;CSV Field #1
           "Name"              ;;CSV Field #2
           "HomePage"          ;;CSV Field #3
#)

;--- Create a HTML page per CSV record ---
#{ FOR @@Record = 1 to <?Data:TmpCsv.?>
   ;--- Collect Information to be passed to the #included file ---
   #define+ CSV_NAME    <?Data:TmpCsv.@@Record.2>
   #define+ CSV_EMAIL   <?Data:TmpCsv.@@Record.1>
   #define+ CSV_WEBPAGE <?Data:TmpCsv.@@Record.3>

   ;--- Now create the HTML page (inline - would normally use #include) ---
   #output "out\CSV_<$CSV_NAME>.htm" ASIS
       <html>
       <head>
           <title>BASIC page for "<$CSV_NAME>"</title>
       </head>
       <body>
           ;--- Heading ------------------
           <center><h1>BASIC page for "<$CSV_NAME>"</h1></center>

           ;--- Page body ----------------
           <p><$CSV_NAME> has an email address of "<$CSV_EMAIL>" and a
           web address of "<$CSV_WEBPAGE>".

           ;--- Footer -------------------
           <hr size=1 color=red>
           <center><?CompileTime><br><$CSV_NAME></center>
       </body>
       </html>
   #output
#}

email me  any feedback, additional information or corrections.
See this page online (look for updates)

PPWIZARD Manual

Saturday May 28 2022 at 2:55pm