Working With CSV Files
Connection strings
For details of connection strings to use with different versions of Excel, see ConnectionString Attribute.
Detecting data types
Any data surrounded by the text delimiter (double quotation marks [“
], is
interpreted as text. For example, "42"
is a text string, and not a
number.
The OLE-DB driver for Text automatically assigns the data type of each column based on a sampling of 25 rows. This may generate issues in some cases. For example, if the 25 first rows of particular column include only numeric values, the column is now typed as numeric. If string values exist in the 26th row or beyond, either a fatal error will be generated or values will be discarded.
One possible way to solve the problem is to change the number of rows considered by the driver. Of course, this is only reliable if all rows in the CSV file are examined; but on very large files, this may have performance impacts.
regedit
to locate the following registry key:- For a 32-bit operating
system:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text
- For a 64-bit operating
system:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Text
In this key, the value for MaxScanRows
defines the number of rows that the
driver reads to determine the data type of a column. A value of zero causes the driver to
read the full worksheet to evaluate column types.
Using Schema.ini
Columns names, data types, character set, and data conversions are specified for the Text
OLE-DB driver by using a schema.ini
file. This file contains the definition
of the columns for any text file in the current directory and overwrites all other settings.
Microsoft Windows offers an easy way to generate a default schema.ini
file
based on the existing text files in a directory. For details, see Editing a Schema.ini File.
A CSV example
asset.csv
file, located in the temp
directory, contains the following
values:Assetname, AssetSerialNumber, AssetPrice
"First Computer", "SerialNumber1", 1000
"Second Computer", "SerialNumber2", 2000
"Third Computer", "SerialNumber3", 3000
<Import Name="ASSET"
Type="CSV"
ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\temp;
Extended Properties='text;HDR=Yes;FMT=CSVDelimited'"
Query="select * from [asset.csv]">
<Log Name="NewLog"
Output="file"
Loglevel="debug"
filename="IMPORT_NAME.log.txt"
</Log>
<Object Name="asset"
Type="asset"
Output="assetoutid"
Update="True"
Create="True">
<Property
Type="shortdescription"
Name="Description"
Value="AssetName"
ValueType="FieldValue"
UseForMatching="false">
</Property>
<Property
Type="serialnumber"
Name="Serial Number"
Value="AssetSerialNumber"
ValueType="FieldValue"
UseForMatching="true">
</Property>
<Property
Type="purchaseprice"
Name="Purchase Price"
Value="AssetPrice"
ValueType="FixedValue"
UseForMatching="false">
</Property>
</Object>
</Import>
FlexNet Manager Suite (On-Premises)
2023 R2