OLEDB reading CSV file returns first column only

I’m trying to read a pipe separated text file. First lines are

"BewerberID"|"Druck"|"Druckdatum"|"HistorieID"|"Bearbeiter"|"BewZuBewGruppeID"|"Bemerkung"
"12586"|"EinladungOFD.dot                                  "|"03.02.2003 00:00:00"|"162"|"Petersen  "|"20295"|"ungültig"
"12807"|"EinladungOFD.dot                                  "|"27.02.2003 00:00:00"|"258"|"Petersen  "|"20617"|""
"12807"|"EinladungOFD.dot                                  "|"28.02.2003 00:00:00"|"270"|"Petersen  "|"20617"|""

​x
 
"BewerberID"|"Druck"|"Druckdatum"|"HistorieID"|"Bearbeiter"|"BewZuBewGruppeID"|"Bemerkung""12586"|"EinladungOFD.dot                                  "|"03.02.2003 00:00:00"|"162"|"Petersen  "|"20295"|"ungültig""12807"|"EinladungOFD.dot                                  "|"27.02.2003 00:00:00"|"258"|"Petersen  "|"20617"|"""12807"|"EinladungOFD.dot                                  "|"28.02.2003 00:00:00"|"270"|"Petersen  "|"20617"|""​

Below is the LINQpad script i’m using. It runs perfectly, but does return values from the first colum only.

string mySelectQuery = "SELECT * FROM Historie.CSV";
OleDbConnection connection = new OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\;" + 
   "Extended Properties="text;HDR=YES;IMEX=1;FMT=Delimited(|)"");
connection.Open();
OleDbCommand cmd = connection.CreateCommand();
cmd.CommandText = mySelectQuery;
OleDbDataReader rdr = cmd.ExecuteReader();
rdr.Dump();
rdr.Close();
connection.Close();

 
string mySelectQuery = "SELECT * FROM Historie.CSV";OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\;" +    "Extended Properties="text;HDR=YES;IMEX=1;FMT=Delimited(|)"");connection.Open();OleDbCommand cmd = connection.CreateCommand();cmd.CommandText = mySelectQuery;OleDbDataReader rdr = cmd.ExecuteReader();rdr.Dump();rdr.Close();connection.Close();​

This returns the first column only.

BewerberID 
12586 
12807 
12807

 
BewerberID 12586 12807 12807​

I tried switching to column names SELECT BewerberID, Druck FROM Historie.CSV but get an error stating “At least one parameter has no value”. (BTW: SELECT BewerberID FROM Historie.CSV does work and returns the same as *)

What do i have to do to get all columns back?

Answer

Create a file named schema.ini in the same folder as Historie.CSV (in this case C:). The file should have the following contents:

[Historie.csv]
Format=Delimited(|)
ColNameHeader=True

 
[Historie.csv]Format=Delimited(|)ColNameHeader=True​

Then try rerunning the code.

Some links:

Advertisement

Answer