reading text file using OLEDB into dataset

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
Tue Aug 02 11:28:18 CDT 2005

On 2 Aug 2005 07:53:08 -0700, ds_pam@hotmail.com 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
Tue Aug 02 14:43:35 CDT 2005

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.

Paul Clement wrote:
> On 2 Aug 2005 07:53:08 -0700, ds_pam@hotmail.com wrote:
>
> =A4 Hi
> =A4 I'm using OLEDB to fill a dataset from CSV file.
> =A4 it works great - too great.
> =A4 the dataset recognizes some text fields as integers. for example the
> =A4 date 02/08/05 is transformed to 20805.
> =A4 I want the dataset to read all data as strings.
> =A4 I can't use schema files because the CSV files are generated at runti=
me
> =A4 with different names and column number,
> =A4 and I can't manipulate the files I read.
> =A4 any ideas?
>
> You cannot force the Text driver to assume the data type of certain colum=
ns without a schema.ini
> file. In the absence of such a file the driver makes a "best guess" depen=
ding 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=3D6
>=20
>=20
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)

Cor
Wed Aug 03 01:46:08 CDT 2005

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
Wed Aug 03 02:38:27 CDT 2005

>>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
Wed Aug 03 09:16:42 CDT 2005

On 3 Aug 2005 00:38:27 -0700, ds_pam@hotmail.com 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)