PPWIZARD is a free preprocessor for HTML, REXX, Visual Basic or any text files.
[Bottom][Contents][Search][Prev]: #import[Next]: #import - Fixed Field Records
\ -> Commands -> #import -> #import - Delimited Records

#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.

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:

  1. The use of #defines and line continuation to format things to be easier to read and understand.

  2. The use of #autotag to translate some of the text (for example "priority") to minimize the width of the columns.

  3. More complex overriding of defaults to get smaller font size etc.

  4. More field dropping but also swapping of fields.
<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 = "15.192"
say "[]------------------------------------------------------------------[]"
say "| XLS2TAB.VBS v" & PgmVersion & ": Exports a single sheet of a Excel spreadsheet |"
say "|                      Written by Dennis Bareis (PPWIZARD Manual)    |"
say "[]------------------------------------------------------------------[]"
say ""

'--- Initialization ---------------------------------------------------------
dim oShell : set oShell = WScript.CreateObject("WScript.Shell")
dim oFS    : set oFs    = CreateObject("Scripting.FileSystemObject")
const xlTextWindows     = 20
const xlExcel9795       = 43
const xlA1              = 1         '$B$3, XlReferenceStyle Enumeration (Excel): https://msdn.microsoft.com/EN-US/library/office/ff821207.aspx
const xlR1C1            = -4150     'R3C2, XlReferenceStyle Enumeration (Excel): https://msdn.microsoft.com/EN-US/library/office/ff821207.aspx
const CellLength2Report = 20000
FixLocaleIfEffectedByVbscriptRegionalSettingsBug()



'--- Command line processing ------------------------------------------------
dim DebugMode    : DebugMode    = false
dim ExcelFile    : ExcelFile    = ""
dim TabFile      : TabFile      = ""
dim WantedSheet  : WantedSheet  = "*"
dim Sort         : Sort         = true
dim Truncate     : Truncate     = 0                    'No limit (Excel has issues 21,000+ exporting and 32,767 hard limit)
dim TruncatedText: TruncatedText= "...[truncated]"     'Default Text to be added after a cell is truncated
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("Debug")
            '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
                say "Debug mode turned on..."
                DebugMode = true
            '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
            case lcase("Truncate")
            '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
                '--- Valid examples are "1000" "1000:", "1000:{CELL TRUNCATED}" ---
                Bits = split(SwitchParm, ":", 2)
                SwitchParm = ""

                '--- Length manditory ---------------------------------------
                Truncate = cint(Bits(0))             '0=No truncation (the default)

                '--- Replacement text optional ------------------------------
                if  ubound(Bits) = 1 then
                    TruncatedText = Bits(1)          'replacement text passed after ":" (even if its empty)
                end if
            '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
            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       = DebugMode
say "Opening """ & ExcelFile & """."
err.clear()
'oExcel.WorkBooks.Add ExcelFile                     '2011-10-31: Changed from add() to open() as one particular spread sheet wanted to autosave while opening :-(
oExcel.Workbooks.Open 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
    set oRange = Nothing
end if

'--- Do we want to limit data in cells? -------------------------------------
if  Truncate > 0 then
    say "Truncating cell data to " & Truncate & " characters"
    dim oCell
    for each oCell in oSheet.UsedRange.Cells            'Does the whole row (even trailing empty cells}: for each oCell in oSheet.Cells
      'do some stuff: http://stackoverflow.com/questions/73785/how-to-iterate-through-all-the-cells-in-excel-vba-or-vsto-2005
      dim CellLength : CellLength = 0 : CellLength = len(oCell.Value2)
      if  CellLength > Truncate then
          '--- Need to shorten this cell ------------------------------------
          say "   * Truncating " & oCell.AddressLocal(,,xlA1) & " (" & CellLength & " characters long)"
          if    TruncatedText = "" then
                oCell.Value2 = left(oCell.Value2, Truncate)
          else
                oCell.Value2 = left(oCell.Value2, Truncate) & TruncatedText
          end if
      end if
    next
    set oCell = Nothing
end if


'--- Now "Export" -----------------------------------------------------------
say "Writing """ & TabFile & """."
err.clear()
oSheet.SaveAs TabFile, XlFileFormat
if  err.number <> 0 then
    dim ErrCode : ErrCode = "0x" & hex(err.number)
    dim Reason  : Reason = "Could not export the data to """ & TabFile & """." & vbCRLF & "Reason " & ErrCode & " - " & err.description
    if  ucase(ErrCode) = "0X3EC" then
        '--- Probably have some very long cells -----------------------------
        Reason = Reason & vbCRLF & vbCRLF & "Look at the exported data to indicate problem area in workbook, Excel doesn't" & vbCRLF & "like cells with more than around 21,000 characters in them :-(" & vbCRLF & vbCRLF & "Consider using the ""/Truncate"" option"

        '--- Display long cells ---------------------------------------------
        say ""
        say "Looking for cells longer than " & CellLength2Report & " characters..."
        for each oCell in oSheet.UsedRange.Cells            'Does the whole row (even trailing empty cells}: for each oCell in oSheet.Cells
          CellLength = 0 : CellLength = len(oCell.Value2)
          if  CellLength > CellLength2Report then
              say "   * CELL " & oCell.AddressLocal(,,xlA1) & ": " & CellLength & " characters"
          end if
        next
        set oCell = Nothing
    end if
    Die Reason
end if

'--- Thats All Folks --------------------------------------------------------
say "Successful."
AboutToExitDialog()
oExcel.Quit
set oSheet = Nothing
set oExcel = Nothing
wscript.quit 0

'============================================================================
sub AboutToExitDialog()
'============================================================================
    if  DebugMode then
        MsgBox "About to Exit and close Excel, want to check anything?",, "DEBUG MODE"
    end if
end sub



'============================================================================
sub say(What)
'============================================================================
    wscript.echo What
end sub


'============================================================================
sub Die(Why)
'============================================================================
    on error resume next
    say ""
    say "ERROR"
    say "~~~~~"
    say Why

    '--- Exit ---------------------------------------------------------------
    AboutToExitDialog()
    oExcel.Quit
    set oSheet = Nothing
    set oExcel = Nothing
    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 the 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 "                                     xlWKS = 4, xlExcel9795 = 43"
    say "    [/Truncate:MaxLength[:[Text]]] - Make sure no cell is ""too long"","
    say "                                     optionally remove or replace appended text."
    say ""
    say "Invalid arguments"
    say "~~~~~~~~~~~~~~~~~"
    say Why & chr(7) & chr(7)
    wscript.quit 666
end sub



'============================================================================
sub FixLocaleIfEffectedByVbscriptRegionalSettingsBug()
'
' This is the mimimum required code (hard codes "AU" TZ and applied even where bug doesn't exist):
'      SetLocale(3081)             'AU=3081, will obviously cause issues in non-AUSTRALIAN countries
'============================================================================
    'on error resume next

    '---- Lets be conservative and do nothing we don't need to.. ------------
    dim CONST_LOCALE_AUSTRALIA : CONST_LOCALE_AUSTRALIA = 3081
    dim CONST_LOCALE_US        : CONST_LOCALE_US        = 1033
    dim CONST_RegSystemLocale  : CONST_RegSystemLocale  = "HKEY_USERS\S-1-5-18\Control Panel\International\Locale"
    dim CurrentLocale : CurrentLocale = "" : CurrentLocale = GetLocale()
    if  CurrentLocale = CONST_LOCALE_US then
        '--- Could be effected by the bug, check actual configured regional setting ---
        dim oShell : set oShell = CreateObject("WScript.Shell")
            dim Locale : Locale = "" : Locale = oShell.RegRead(CONST_RegSystemLocale)
            if Locale <> "" then
               '--- We do know the system locale ----------------------------
               Locale = h2d(Locale)
               if Locale <> "" and Locale <> CurrentLocale then
                  '--- Locale differs, seem to have the bug, fix ------------
                  SetLocale(Locale)
               end if
            end if
        set oShell = Nothing
    end if
end sub
function H2D(ByVal HexValue)
    on error resume next
    H2D = Eval("&H" & HexValue )
    if err.number <> 0 then H2D = ""                    'Something wrong...
end function


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

[Top][Contents][Search][Prev]: #import[Next]: #import - Fixed Field Records


PPWIZARD Manual
My whole website and this manual itself was developed using PPWIZARD (free preprocessor written by Dennis Bareis)
Sunday November 05 2017 at 10:00am