Import Options String
Since the Import Options dialog is not displayed when the program is driven from an automation script, an options string can be specified in the script. The string consists
of a series of semicolon-separated keywords that specify the behavior
of the import. See the Worksheet Import Options topic for links to file-type specific import options.
The options string can only be specified for the file types that display import options int he program. For example, a CSV file will not accept the import options string in automation, since it has no import options in the user interface(UI).
Examples:
Text file:
"Delimiter=comma;TextQualifier=singlequote;SkipExtraDelimiters=0"
This would set the delimiter character to a comma and the text qualifier character to the single-quote mark ( ‘ ).
"FilterID=txt;DecimalSymbol=comma;Delimiter=semicolon"
This would set the file type to .TXT file with comma decimal symbols and semicolon delimiters:
Database:
“SQLSTR=;Table=table1”
This uses Table1 from the database.
Excel File:
"Sheet=Sheet1"
This uses Sheet1 from the XLS, XLSX, or XLSM file.
Option
|
Action
|
Default |
Description
|
ColumnBreaks
|
N,N,N... |
no
option string
|
ColumnBreaks
is used when UsedFixedWidth
is set to 1. The field separators are a series separated by commas
or spaces, for example: 6 12 20 25.
|
DecimalSymbol
|
period = use . comma = use , |
period
|
Sets the decimal symbol to period or comma
|
Delimiter
|
tab = tab comma = comma semicolon = semicolon space = space equals = equals other = enter the delimiter character
|
comma
|
The Delimeter
is the character that separates data cell values. You can use
more than one delimiter in the string.
For example, " Delimiter=comma,%,tab" |
EatWhiteSpace |
0 = Do not skip extra space and tab in the data 1= Skip extra space and tab in the data
|
0 |
EatWhiteSpace
removes extra space and tab characters preceding and following
data. A 0 means not to skip the extra space.
For example, " 1 2" would leave the column A blank
and import data into the columns B and C. A 1 means to skip the extra space. For example, " 1 2" would import data into the
columns A and B. |
ImportCodePage
|
String
|
"" |
The name of the ANSI code page to use when
importing Unicode data
|
Sheet |
sheet name
|
|
Opens the specified sheet in an Excel workbook |
SkipExtraDelimiters |
0 = Do not skip extra delimiters in the data 1= Skip extra delimiters in the data |
1 |
SkipExtraDelimiters
specifies whether multiple delimiters are treated as one delimiter.
SkipExtraDelimiters=0" means "don't skip the extra delimiters"
For example, 1,,2,,,3 would import into columns A,C, and F. "SkipExtraDelimiters=1" means "do skip extra
delimiters". For example, 1,,2,,,3 would import into columns A, B and C. |
SQLSTR
|
SQL string
|
No
|
Structured Query Language access string.
|
StartRow
|
N
|
1 |
StartRow is the row number at which to begin importing.
|
Table
|
table or query name
|
|
Table
opens the specified table in an Access database.
|
TextQualifier |
doublequote = doublequote(") singlequote = singlequote(') none = none other = enter the text qualifier
|
doublequote |
TextQualifier specifies the character that surrounds cells containing text.
You can use more than one text qualifier in the string.
For example, " TextQualifier=doublequote,%" |
UseFixedWidth |
0 = No 1 = Yes |
0
|
UseFixedWidth
uses a fixed width for columns during import.
|
Locale
|
String
|
System
Locale
|
Locale
is the locale ID in decimal values. The default locale is determined
by the locale setting in the Windows Control Panel.
|
DateOrder
|
0 = Auto 1 = MDY (Month, Day, Year) 2 = DMY 3 = YMD 4 = MYD 5 = DYM 6 = YDM
|
0
|
The DateOrder
specifies the order in which dates are written in the data file.
When DateOrder
is set to 0, the standard date order from the Locale
is used. |
See Also
Export Options String