Text File Import

[Joe Fallon]

I am importing a delimited ASCII text file into a dataset using OLEDB.
I set up the connection and dataadapter and then fill the dataset.
It works - sort of.

It turns out that some of my data is omitted. Specifically, one of the
columns is set to Int32, but the data is really string.
The first few rows of data have numeric valus in this field but further down
there are some letters mixed in with the numbers. So only fields with pure
numbers are imported.

I tried building the table in code before importing and specifying this
field as String.
The import occurred but the data was still missing.

Seems like the import process is deciding things for me "behind the scenes".
Any ideas how to get all the data loaded?

Would it help if I put string data in that column in the first row of data?
Would the import then "guess" that the column datatype is string and not
Int32?
How would I handle this since for an arbitrary file which may not have
string data in that column in row 1?

 

[Paul Clement]

¤ I am importing a delimited ASCII text file into a dataset using OLEDB.
¤ I set up the connection and dataadapter and then fill the dataset.
¤ It works - sort of.
¤
¤ It turns out that some of my data is omitted. Specifically, one of the
¤ columns is set to Int32, but the data is really string.
¤ The first few rows of data have numeric valus in this field but further down
¤ there are some letters mixed in with the numbers. So only fields with pure
¤ numbers are imported.
¤
¤ I tried building the table in code before importing and specifying this
¤ field as String.
¤ The import occurred but the data was still missing.
¤
¤ Seems like the import process is deciding things for me "behind the scenes".
¤ Any ideas how to get all the data loaded?
¤
¤ Would it help if I put string data in that column in the first row of data?
¤ Would the import then "guess" that the column datatype is string and not
¤ Int32?
¤ How would I handle this since for an arbitrary file which may not have
¤ string data in that column in row 1?

If you're having conversion issues then you'll probably need to use a schema.ini file:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Joe Fallon]

Paul,
Thanks. That is a good reference and should do the trick.
Finding information on schema.ini used to be much harder. This page pretty
much has it all.

 

[Aegee]

Paul et al,

We ran into a similar issue and discovered the schema.ini file that kind of
solved the issue but one disadvantage with the schema.ini file is that it's
expecting a static filename as a first parameter; in our case the file name
keeps changing - the timestamp is appended to the filename to enforce the
uniqueness. Do you (or anyone) have a workaround ?

thanks in advance

 

[Joe Fallon]

FYI
Defining the columns and datatypes in the schema.ini file worked perfectly.

I build it on the fly so entering a unique file name in the first row is now
problem.