#import - Delimited Records |
This type of #Import has clearly defined fields. You would frequently wish to display these in a table (say in a HTML page) however nothing says that you need to do so. If you wish to create macros and then #include a template file then that is also possible.
Within a record the fields are delimited by a single character such as a tab or comma.
The actual code PPWIZARD uses to split up the delimited records is available in the PPWIZARD add-on called "import_d", you may wish to make use of this for more complicated importing (perhaps where lots of liltering and sorting might be required).
Note that under Windows you have the option of reading these sorts of files via ODBC and therefore you can use an SQL import! This gives you greater control without extra coding, for example sorting the data or only choosing records that match certain criteria (as per normal SQL query syntax).
Field Information Parameters |
On these types of imports "FieldInfo" follows the "DefineName" parameter on the #import command line. There is only one situation where this need not be supplied and that is if you wish to import all fields in the original order and the first record contains suitable field descriptions. If the field information is not supplied then PPWIZARD will extract it.
You must specify field information for each field up to the last one you are interested in. The field information is of the format:
[{NewColumn}]TitleText
The optional "NewColumn" specifies the column you wish the field to be moved to. This need only be supplied if you wish to change the order, by default the first field is column 1 etc.
The "title text" specifies the value for the field in the header record. A blank "title" is used to indicate that we don't require a field and it should be dropped.
DEFINITIONS/OPTIONS |
If you can't understand how these options work then I suggest you try using /debug or #debug to watch what variables the import uses etc.
To save the data to memory you need to supply the name of the #data structure, you refer to this when generating the output with the <?Data> symbol. If used a lot of the other definitions are not used, if unsure have a look at the debug output.
For even more control (probably rare requirement):
You can set this on a field by field level if required:
If your database contains preformatted HTML code (or references PPWIZARD macros) then this default handling would prevent this from working so you would probably want to turn this off, on the other hand you may wish to extend this to other characters (possibly international characters such as umlauts).
This definition lists zero, one or more names as used on previous "#AsIs SETUP" commands (separated by whitespace). Each is added in turn.
To prevent all character conversions you should assign an empty value to this parameter.
If this define is not used then you can use the following:
If this define is not used then you can use the following:
If an expression is supplied for this option then the filter code will be called immediately following the line having been read from the imported file. This could help you to perform slight reformatting of information etc.
The following rexx variables are relevant:
Note that any macros or other symbols are replaced once at the start of the import and not for each field.
Normally all records are displayed. A filter can examine all column variables and modify them or tell PPWIZARD to ignore the record. The filter is not called for the heading record.
The following rexx variables and functions are relevant:
If the contents starts with 'EOF:' then the current record and ALL following are dropped.
If all your records are processed the same way then you should not need to modify this variable. It is useful where you might want the output (row of table) to look different depending on the records data. In some cases this can be better done by updating the rexx "Column.?" array.
If you need multiple lines you can of course use "<?NewLine>" where required.
Note that any macros or other symbols are replaced once at the start of the import and not for each field.
You should also check out an example of importing a file into multiple HTML pages based on the contents of one of the fields.
Examples - Comma Delimited |
A tab delimited file is probably be best format to use however this example will use comma delimited as its a bit hard to display a tab! Assume the following file is being imported (Importme.CMA):
Dennis,Bareis,Programmer Wendy,Buxton,Librarian Fred,Nerk,Idiot
Please treat each of the following examples in isolation and assume that no #defines other than those specifically shown for that example have been set. Please note that I could have used ",,," instead of "CMA" when specifying the format.
The following code will display the 3 fields in the order they occur (in a completely default table format):
#import IMPORTME.CMA "CMA" '' "First Name" "Last Name" "Job"
We now wish to simply swap the order of the "Job" column so it becomes first:
#import IMPORTME.CMA CMA '' "{2}First Name" "{3}Last Name" "{1}Job"
Lets drop the last name altogether so that we only see the first name and job columns:
#import IMPORTME.CMA CMA '' "First Name" "" "Job"
Lets display the above table using slightly different table formatting (column borders thinner, table border fatter, headings centered on yellow background and record data left justified):
#define IMPORT_TABLE_ATTRIBS BORDER=20 CELLSPACING=1 #define IMPORT_HEADING_COLUMNS ALIGN=CENTER BGCOLOR=YELLOW #define IMPORT_RECORD_COLUMNS ALIGN=LEFT #import IMPORTME.CMA CMA '' "First Name" "" "Job"
As above but column 2 is centered:
#define IMPORT_TABLE_ATTRIBS BORDER=20 CELLSPACING=1 #define IMPORT_HEADING_COLUMNS ALIGN=CENTER BGCOLOR=YELLOW #define IMPORT_RECORD_COLUMNS ALIGN=LEFT #define IMPORT_RECORD_COLUMN_2 ALIGN=CENTER #import IMPORTME.CMA CMA '' "First Name" "" "Job"
Examples - More Complex |
This example is based on a real one situation at work. We export date from Microsoft's access/Excel and want this data to appear in a table.
The following main points are demonstrated:
<HTML> ;--- Problem database data exported from Excel (trying from access) --------- <CENTER><H1>Release 98.0.1</H1></CENTER> ;--- Setup table definitions ------------------------------------------------ #define IMPORT_TABLE_ATTRIBS BORDER=5 CELLSPACING=1 #define IMPORT_BLANK_FIELD - ;** CommentBlock /* (Tuesday 23/06/1998, 13:00:55, by Dennis_Bareis) */ ;**+-------------------------------------------------------------------------- ;**|#define IMPORT_HEADING_COLUMNS ALIGN=CENTER BGCOLOR=YELLOW ;**|#define IMPORT_RECORD_COLUMNS ALIGN=CENTER ;**|#define IMPORT_RECORD_COLUMN_2 ALIGN=LEFT ;**|#define IMPORT_RECORD_COLUMN_4 ALIGN=LEFT ;**+-------------------------------------------------------------------------- ;** /* (Tuesday 23/06/1998, 13:00:55, by Dennis_Bareis) */ ;--- Define some data translations (shorten Priority + Problem Type) -------- #AutoTag ">High<" ">H<" #AutoTag ">Low<" ">L<" #AutoTag ">Medium<" ">M<" #AutoTag ">Change<" ">C<" #AutoTag ">Error<" ">E<" ;--- Try these -------------------------------------------------------------- #define IMPORT_HEADER <TR> -\ <TH ALIGN=CENTER BGCOLOR=YELLOW><FONT SIZE=-1>{$Column1} -\ <TH ALIGN=CENTER BGCOLOR=YELLOW><FONT SIZE=-1>{$Column2} -\ <TH ALIGN=CENTER BGCOLOR=YELLOW><FONT SIZE=-1>{$Column3} -\ <TH ALIGN=CENTER BGCOLOR=YELLOW><FONT SIZE=-1>{$Column4} -\ <TH ALIGN=CENTER BGCOLOR=YELLOW><FONT SIZE=-1>{$Column5} -\ <TH ALIGN=CENTER BGCOLOR=YELLOW><FONT SIZE=-1>{$Column6} -\ <TH ALIGN=CENTER BGCOLOR=YELLOW><FONT SIZE=-1>{$Column7} -\ <TH ALIGN=CENTER BGCOLOR=YELLOW><FONT SIZE=-1>{$Column8} -\ </TR> #define IMPORT_RECORD <TR> -\ <TD ALIGN=CENTER><FONT SIZE=-1>{$Column1} -\ <TD ALIGN=LEFT><FONT SIZE=-1>{$Column2} -\ <TD ALIGN=CENTER><FONT SIZE=-1>{$Column3} -\ <TD ALIGN=LEFT><FONT SIZE=-1>{$Column4} -\ <TD ALIGN=CENTER><FONT SIZE=-1>{$Column5} -\ <TD ALIGN=CENTER><FONT SIZE=-1>{$Column6} -\ <TD ALIGN=CENTER><FONT SIZE=-1>{$Column7} -\ <TD ALIGN=CENTER><FONT SIZE=-1>{$Column8} -\ </TR> ;--- Specify the fields ----------------------------------------------------- #define FIELD_NAMES "{1}Problem<BR>#" \ "{2}Title" \ "{5}P<BR>r<BR>i" \ "{8}Pre<BR>/<BR>Spar" \ "{6}T<BR>y<BR>p<BR>e" \ "{3}Application" \ "" \ "" \ "{7}Fixed By" \ "" \ "" \ "" \ "" \ "" \ "{4}User Impact" ;--- Make the changes (autotag some text) ----------------------------------- #AutoTag ON #import "export.tab" TAB- "" <$FIELD_NAMES> #AutoTag OFF </HTML>
Examples - IPF Import |
The following code:
#define IMPORT_NEWLINE_CHAR <?NewLine>.br<?NewLine> #define IMPORT_BEFORE :table cols='15 15 10'. #define IMPORT_HEADER :row. -\ :c.:hp9.{$Column1}:ehp9. -\ :c.:hp9.{$Column2}:ehp9. -\ :c.:hp9.{$Column3}:ehp9. #define IMPORT_RECORD :row. -\ :c.{$Column1} -\ :c.{$Column2} -\ :c.{$Column3} #define IMPORT_AFTER :etable. #define IMPORT_RECORD_COLUMNS ALIGN=LEFT #import "EXAMPLE.CMA" CMA '' "First Name" "Surname" "Job"
Example - Use of Filter |
#DefineRexx IMPORT_RECORD_FILTER if Column.1 = "Wendy" then Remove='This is "Wendy" record' ;;Don't want this record else Column.1 = translate(Column.1) ;;Make column #1 upper case #DefineRexx #import ImportMe.CMA CMA "" "First<BR>Name" "Surname" "Job"
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>
XLS2TAB.vbs |
The "XLS2TAB.vbs" script (which PPWIZARD installs) can automate the export of data from a XLS spread sheet into a tab delimited text file. It can be run via CSCRIPT on windows with a batch file similar to:
@echo off del Out.tab >nul 2>&1 cscript //NoLogo XLS2TAB.vbs "/READ:ABC1234.xls" "/CREATE:Out.tab" "/Sheet:Sheet1" type Out.tab
The script looks like:
'--- Don't allow use via WSCRIPT! ------------------------------------------- Option Explicit if ucase(mid(wscript.FullName, len(wscript.Path) + 2, 1)) = "W" Then say "You can't use WSCRIPT on this VB script, use CSCRIPT instead!" wscript.quit 999 end if '--- Set and display Version information ------------------------------------ dim PgmVersion : PgmVersion = "05.111" say "[]------------------------------------------------------------------[]" say "| XLS2TAB.VBS v" & PgmVersion & ": Exports a single sheet of a Excel spreadsheet |" say "[]------------------------------------------------------------------[]" say "" '--- Initialiization -------------------------------------------------------- dim oShell : set oShell = WScript.CreateObject("WScript.Shell") dim oFS : set oFs = CreateObject("Scripting.FileSystemObject") const xlTextWindows = 20 '--- Command line processing ------------------------------------------------ dim ExcelFile : ExcelFile = "" dim TabFile : TabFile = "" dim WantedSheet : WantedSheet = "*" dim Sort : Sort = true dim SortRange : SortRange = "A1" dim XlFileFormat : XlFileFormat = xlTextWindows dim i for i = 0 to Wscript.Arguments.Count-1 '--- Get argument ------------------------------------------------------- dim OneArg : OneArg = Wscript.Arguments(i) dim OneArgL1 : OneArgL1 = left(OneArg, 1) '--- Switch? ------------------------------------------------------------ if left(OneArgL1, 1) <> "-" and left(OneArgL1, 1) <> "/" then '--- Handle non-switch parameter ------------------------------------ InvalidArguments("The parameter """ & OneArg & """ is not a switch!") else '--- This is a switch ----------------------------------------------- dim Bits, Switch, SwitchParm OneArg = mid(OneArg, 2) 'Drop switch character Bits = split(OneArg, ":", 2) Switch = Bits(0) if ubound(Bits) <> 1 then SwitchParm = "" else SwitchParm = Bits(1) end if '--- Process the switch --------------------------------------------- select case lcase(Switch) '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% case lcase("READ") '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% ExcelFile = oFS.GetAbsolutePathName(SwitchParm) SwitchParm = "" '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% case lcase("CREATE") '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% TabFile = oFS.GetAbsolutePathName(SwitchParm) SwitchParm = "" '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% case lcase("SHEET") '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% WantedSheet = SwitchParm SwitchParm = "" '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% case lcase("XlFileFormat") '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% XlFileFormat = cint(SwitchParm) SwitchParm = "" '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% case lcase("NoSort") '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% Sort = false '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% case lcase("SortRange") '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% SortRange = SwitchParm SwitchParm = "" '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% case else '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% InvalidArguments("The switch ""/" & Switch & """ is unknown!") end select if SwitchParm <> "" then InvalidArguments("Got an unexpected parameter of """ & SwitchParm & """ on the switch ""/" & Switch & """!") end if end if next if ExcelFile = "" then InvalidArguments("The /READ parameter was not used!") if TabFile = "" then InvalidArguments("The /CREATE parameter was not used!") '--- Make sure the Excel file exists ---------------------------------------- if not oFS.FileExists(ExcelFile) then Die "The Excel file """ & ExcelFile & """ doesn't exist!" end if '--- Now delete the TAB file ------------------------------------------------ on error resume next if oFS.FileExists(TabFile) then oFs.DeleteFile(TabFile) if err.number <> 0 then Die "Could not delete the file """ & TabFile & """ (generated output!)" & vbCRLF & "Reason 0x" & hex(err.number) & " - " & err.description end if end if '--- Load the XLS ----------------------------------------------------------- dim ExcelAppName : ExcelAppName = "Excel.Application" say "Loading Microsoft Excel (""" & ExcelAppName & """)." dim oExcel : Set oExcel = WScript.CreateObject(ExcelAppName) if err.number <> 0 then Die "Could not load """ & ExcelAppName & """ (is Excel installed?)" & vbCRLF & "Reason 0x" & hex(err.number) & " - " & err.description end if oExcel.DisplayAlerts = False oExcel.UserControl = False oExcel.Visible = False say "Opening """ & ExcelFile & """." err.clear() oExcel.WorkBooks.Add ExcelFile if err.number <> 0 then Die "Could not load the file """ & ExcelFile & """ (should be an Excel Spread Sheet!)" & vbCRLF & "Reason 0x" & hex(err.number) & " - " & err.description end if '--- Activate the appropriate Sheet ----------------------------------------- err.clear() dim oSheet if WantedSheet = "*" or WantedSheet = "" then say "Selecting the active sheet of the active workbook." set oSheet = oExcel.ActiveWorkBook.ActiveSheet else say "Loading the sheet """ & WantedSheet & """." set oSheet = oExcel.Sheets(WantedSheet) end if if err.number <> 0 then Die "Could not load activate the work sheet """ & WantedSheet & """ in the Excel file """ & ExcelFile & """." & vbCRLF & "Reason 0x" & hex(err.number) & " - " & err.description end if '--- Sort the sheet by first column (ignoring the first "header" row) ------- if Sort then '--- User wants a sort -------------------------------------------------- err.clear() say "Sorting the rows, Range = " & SortRange dim oRange : set oRange = oSheet.Range(SortRange) if err.number <> 0 then Die "Setting Range Failed." & vbCRLF & "Reason 0x" & hex(err.number) & " - " & err.description end if err.clear() oRange.sort oRange,,,,,,,1 if err.number <> 0 then Die "Sorting failed." & vbCRLF & "Reason 0x" & hex(err.number) & " - " & err.description end if end if '--- Now "Export" ----------------------------------------------------------- say "Writing """ & TabFile & """." err.clear() oSheet.SaveAs TabFile, XlFileFormat if err.number <> 0 then Die "Could not export the data to """ & TabFile & """." & vbCRLF & "Reason 0x" & hex(err.number) & " - " & err.description end if '--- Thats All Folks -------------------------------------------------------- oExcel.Quit say "Successful." set oRange = Nothing set oSheet = Nothing set oExcel = Nothing wscript.quit 0 '============================================================================ sub say(What) '============================================================================ wscript.echo What end sub '============================================================================ sub Die(Why) '============================================================================ say "" say "ERROR" say "~~~~~" say Why wscript.quit 777 end sub '============================================================================ sub InvalidArguments(Why) '============================================================================ say "CORRECT SYNTAX" say "~~~~~~~~~~~~~~" say "cscript.exe //NoLogo [path\]XLS2TAB.VBS" say " /READ:ExistingXlsFile - Name of file that Excel can load" say " /CREATE:NewTabFile - Name of Tab delimited file" say " [/Sheet:SheetName] - ""*"" = Default" say " [/NoSort] - Don't sort" say " [/SortRange] - Default is ""A1"" (can't sort buttons etc)" say " [/XlFileFormat:SaveType] - Integer. Default is xlTextWindows (" & xlTextWindows & ")" say "" say "" say "Invalid arguments" say "~~~~~~~~~~~~~~~~~" say Why & chr(7) & chr(7) wscript.quit 666 end sub