CODEWITHSUNDEEP
c#asp.netcsvoledb
Learner
Learner

Reputation: 159

cannot read all the columns from csv using oledb

I have a csv file with the following header: "Pickup Date","Pickup Time","Pickup Address","From Zone", and so on.. I can only read the first 2 columns and nothing beyond using oledb. I used a schema.ini file with all column names specified. Pls suggest.

Here is my sample csv.

"PickupDate","PickupTime","PickupAddress","FromZone"
"11/05/15","4:00:00 AM","9 Houston Rd, CityName, NC 28262,","262"

Here is my code:

Schema.ini
-----------
[ReportResults.csv]
ColNameHeader = True
Format = CSVDelimited
col1=Pickup Date DateTime
col2=Pickup Time Text width 100
col3=Pickup Address Text width 500
col4=FromZone short

oledb code
-----------      

     public static DataTable SelectCSV(string path, string query)
     {
      // since the file contains addresses with , the delimiter ", is used. Each cell is written within "" in  the file.
                  var strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path +
                                "; Extended Properties='text;HDR=Yes;FMT=Delimited(\",)'";

                  OleDbConnection selectConnection = (OleDbConnection)null;
                  OleDbDataAdapter oleDbDataAdapter = (OleDbDataAdapter)null;


                      selectConnection = new OleDbConnection(strConn);
                      selectConnection.Open();
                      using(OleDbCommand cmd=new OleDbCommand(query,selectConnection))
                      using (oleDbDataAdapter = new OleDbDataAdapter(cmd))
                      {
                          DataTable dt = new DataTable();
                          dt.Locale=CultureInfo.CurrentCulture;
                          oleDbDataAdapter.Fill(dt);
                          return dt;
                      }
    }

Upvotes: 4

Views: 1914

Answers (1)

tezzo
tezzo

Reputation: 11105

Every column is contained in double quotes so every comma inside a double quote is not considered as delimeter.

So you can import your file:

  • without using schema.ini
  • specifying EXTENDED PROPERTIES='text;HDR=Yes;FMT=Delimited' in your connection string

If you need to use a schema to solve other problems please note that your schema.ini is not formally correct; use something like this:

[ReportResults.csv]
ColNameHeader = True
Format = CSVDelimited
col1=PickupDate DateTime
col2=PickupTime Text width 100
col3=PickupAddress Text width 500
col4=FromZone short

If you have problem extracting DateTime column specify DateTimeFormat options; i.e. if your pickup date is something like 2015/11/13 specify DateTimeFormat=yyyy/MM/dd=yyyy/MM/dd.

If you have problem extracting Short column verify that FromZone is an integer between -32768 and 32767; if not, use a different type. You can also set DecimalSymbol option if you have problem with decimal separators.

You can find more info on MSDN.

Upvotes: 1

Related Questions