| #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:
![[Bottom]](bottom.gif){kind=small}
#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 = "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
![[Top]](top.gif){kind=small} | ![[Contents]](contents.gif){kind=small} | ![[Search]](search.gif){kind=small} | ![[Prev]: #import](prev.gif){kind=small} | ![[Next]: #import - Fixed Field Records](next.gif){kind=small} |
