reading text file using OLEDB into dataset

[ds_pam]

Hi
I'm using OLEDB to fill a dataset from CSV file.
it works great - too great.
the dataset recognizes some text fields as integers. for example the
date 02/08/05 is transformed to 20805.
I want the dataset to read all data as strings.
I can't use schema files because the CSV files are generated at runtime
with different names and column number,
and I can't manipulate the files I read.
any ideas?
Thanks
Richard

 

[Paul Clement]

On 2 Aug 2005 07:53:08 -0700, (e-mail address removed) wrote:

¤ Hi
¤ I'm using OLEDB to fill a dataset from CSV file.
¤ it works great - too great.
¤ the dataset recognizes some text fields as integers. for example the
¤ date 02/08/05 is transformed to 20805.
¤ I want the dataset to read all data as strings.
¤ I can't use schema files because the CSV files are generated at runtime
¤ with different names and column number,
¤ and I can't manipulate the files I read.
¤ any ideas?

You cannot force the Text driver to assume the data type of certain columns without a schema.ini
file. In the absence of such a file the driver makes a "best guess" depending upon the data present.
Unfortunately it doesn't always generate the results you expect or desire.

If the files follow a standard structure you could just create the schema.ini file entries "on the
fly".

http://www.mentalis.org/soft/class.qpx?id=6


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[shriop]

You could try my csv parser that I sell, http://www.csvreader.com . It
has a method that will return a DataTable of all the values from the
file.

 

[Cor Ligthert [MVP]]

Shrio,

You could try my csv parser that I sell, http://www.csvreader.com . It
has a method that will return a DataTable of all the values from the
file.

I assume that Paul can build that very good himself. However we are talking
about the inbuild OleDb method.



Cor

 

[ds_pam]

If the files follow a standard structure you could just create the schema.ini file entries "on the fly".
couple of questions regarding this option:
1)does the shema.ini should be in the same directory as the CSV file?
can I use a stream as schema?
2)do I have to write the CSV filename [myfile.csv] at the beginning of
the schema file? I have 3 CSV files with different schema ( different
header and column number ) and these CSV files are generated daily,
only change in the name, not in the schema. can I create one schema to
use for all CSV's ( with different names)?

Thanks
Richard

 

[Paul Clement]

On 3 Aug 2005 00:38:27 -0700, (e-mail address removed) wrote:

¤ >>If the files follow a standard structure you could just create the schema.ini file entries "on the fly".
¤ couple of questions regarding this option:
¤ 1)does the shema.ini should be in the same directory as the CSV file?
¤ can I use a stream as schema?

Yes, the schema.ini file should be in the same folder as your CSV file(s).

I'm not sure what you mean by "use a stream as schema".

¤ 2)do I have to write the CSV filename [myfile.csv] at the beginning of
¤ the schema file? I have 3 CSV files with different schema ( different
¤ header and column number ) and these CSV files are generated daily,
¤ only change in the name, not in the schema. can I create one schema to
¤ use for all CSV's ( with different names)?

The schema entry can be anywhere in the file. As a matter of fact you can have a schema entry for
each CSV file you have in the folder. Below is an example of a schema.ini file that I use for
testing:

[SemiColonDelimited.txt]
ColNameHeader=False
CharacterSet=ANSI
Format=Delimited(

[People.txt]
ColNameHeader=False
Format=FixedLength
CharacterSet=ANSI
Col1=ID text width 11
Col2=LastName text width 30
Col3=FirstName text width 20

[Order.txt]
ColNameHeader=False
Format=Delimited( )
CharacterSet=ANSI

[Staff.txt]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=25
CharacterSet=OEM
DateTimeFormat="yyyymmdd"
Col1="Staff_Id" Long
Col2="Birth" DateTime

Each file must have it's own schema entry, assuming each has a different file name. As I mentioned
you can use the INI file reader/writer to update schema.ini entries.


Paul
~~~~
Microsoft MVP (Visual Basic)