Column limitation on CSV using Microsoft Jet OLEDB

Question

I'm importing data from a CSV that contains approx 350 columns. This CSV import is fixed and I have absolutely no control over it.

If I attempt to open the CSV in Excel 2003, it only partially loads due to the column limit of 255 (IV).

When I load the CSV into a DataSet using OleDb and Microsoft.Jet.OLEDB.4.0 it also only shows 255 columns. Worse still though, when attempting to access the data for some of these 255 columns it shows incorrect data and is splitting up the values incosistently.

If I open the CSV in Excel, let it truncate the data and re-save it, my Import works fine.

My question is, has anyone else encountered this limitation with the Jet.OLEDB provider. If yes, is there a workaround to the problem? If no, is there an alternative solution available that can load such a large CSV?

Note: This is not a one off task, I need to provide an end user with a browse / upload button that performs this import as and when they need it.

Answer 1

We've used http://www.codeproject.com/KB/database/CsvReader.aspx with much success.

Answer 2

You can go through this code , Use ExcelDataReader and add reference in your project. and use below code ...

FileStream stream = File.Open(strFileName, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
excelReader.Close();
return result.Tables[0];

Answer 3

Place a schema.ini file in the same folder as the CSV file. See here for full syntax.

Answer 4

I suggest doing it in an object oriented manner. try using File Helpers library. http://www.filehelpers.com/. It allows you to have an object representation for each line in your file. You can then persist this object to a database.