Problem – While reading a CSV file using Microsoft Text Driver,
It interprets CSV file columns in it’s own way like if one the column
contains data specific to a date, then it only keep data which is in
proper format otherwise it put null against that.
Solution
– Create a schema.ini file which defines the structure of a CSV file in
turn gets interpreted automatically by driver itself in a desired
format.
Details –
Schema.ini files provide schema information about the records in a text file.
If
you create a DSN, the schema.ini file gets created automatically in the
folder where all your CSV files reside. But if you use connection
string, you have to create schema.ini file on your own.
When
the Text driver is used, the format of the text file is determined by
using a schema information file. The schema information file, which is
always named schema.ini and always kept in the same directory as the
text data source, provides the IISAM with information about the general
format of the file
Each Schema.ini entry specifies one of five characteristics of the table:
· The text file name
· The file format
· The field names, widths, and types
· The character set
· Special data type conversions
Example of a schema.ini file
ColNameHeader=FALSE
Col1=Name Char Width 255
Col2=Company Char Width 255
Below are the links for detail information on this topic
http://msdn.microsoft.com/en-us/library/ms709353.aspx
http://www.codeproject.com/KB/database/FinalCSVReader.aspx
ColNameHeader=FALSE
Col1=Name Char Width 255
Col2=Company Char Width 255
Below are the links for detail information on this topic