Import CSV Issue...

[SpotNet]

Hello NewsGroup,

I have to import a CSV file into an Access table, not the whole text file
though. The number of columns in the text file is variable can be 20 or
1300,... I only need say anywhere between the last 10 to 15 (maybe non
adjacent) columns. I'm able to construct an SQL statement for the CSV file
for the relevant fields to extract the information from them. I have noticed
that if the column count in the text file is greater than 255, I can't get
the information out of the file even though my SQL statements only express
the relevant 10 to 15 fields I need to extract. Before I commence the work
around solution, can anyone confirm for me that using OleDb text file
connection (in C#), that OleDb will only recognise the first 255 columns of
a csv text file, just like an Access database, even though I only call the
fields I need, or I'm missing something?

Attempted example;

Text CSV:
Field1,...., Field745, Field746, Field747, Field748
.....with integer data beneath the fields.

The working connection string:
connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\TextFilePath\;Extended
Properties=""text;HDR=Yes;FMT=CSVDelimited"""

The SQL statement;
sqltext = "SELECT [Field623], [Field629], [Field630], [Field631],
[Field640]"
+ " FROM [TextFile.csv]"

Thus,

OleDbConnection conn = new OleDbConnection(connstr); <---Good so far.
OleDbCommand comm = conn.CreateCommand(sqltext); <---Good so far.
OleDbReader reader = comm.ExecuteReader(); <---Doesn't return anything.

But,
sqltext = "SELECT [Field120], [Field126], [Field127], [Field128],
[Field141]"
+ " FROM [TextFile.csv]"
Does return something.

Many thanks and kind regards,
SpotNet.

 

[Paul Clement]

¤
¤ Hello NewsGroup,
¤
¤ I have to import a CSV file into an Access table, not the whole text file
¤ though. The number of columns in the text file is variable can be 20 or
¤ 1300,... I only need say anywhere between the last 10 to 15 (maybe non
¤ adjacent) columns. I'm able to construct an SQL statement for the CSV file
¤ for the relevant fields to extract the information from them. I have noticed
¤ that if the column count in the text file is greater than 255, I can't get
¤ the information out of the file even though my SQL statements only express
¤ the relevant 10 to 15 fields I need to extract. Before I commence the work
¤ around solution, can anyone confirm for me that using OleDb text file
¤ connection (in C#), that OleDb will only recognise the first 255 columns of
¤ a csv text file, just like an Access database, even though I only call the
¤ fields I need, or I'm missing something?
¤

Yes, this is a limitation of the Jet database engine. You can't have more that 255 columns in a
Table, which would also apply to a Text file.


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

 

[SpotNet]

Thank you very much Paul, all is clarified and the work around to be
implemented, thanks again.

Regards,
SpotNet.