Parsing CSV file in Windows Server 2008 64 C#

Question

Hi all..

I'm having some trouble parsing a *.CSV file in windows server 2008 64bit version. The problem it that Jet OLEDB 4.0 doesn't read the header row, presented in the CSV.

That means, that when I try to access one of the columns like this:

DataTable tbl = GetCsvData();
string sd = tbl.Rows[0]["id"].ToString();

The program throws an exception, saying that the column does not belong in the datable.

I'm using the following code:

public DataTable GetCsvData() {
        FileInfo file = new FileInfo(this.fileName);
        string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
             "Data Source=" + file.DirectoryName + "\\;" +
             "Extended Properties=\"text;HDR=Yes;FMT=Delimited(;)\";";
        OleDbConnection objConn = new OleDbConnection(connectionString);
        objConn.Open();
        OleDbCommand objCmdSelect = new OleDbCommand(string.Format("SELECT * FROM [{0}]", file.Name), objConn);
        OleDbDataAdapter adp = new OleDbDataAdapter(objCmdSelect);
        DataTable tbl = new DataTable("CSVData");
        adp.Fill(tbl);
        objConn.Close();
        objConn.Dispose();
        return tbl;
    }

As you can see, the Extended Properties are correct: "HDR=Yes", this forces the Jet engine to read the header row on the CSV file.

The problem is really strange because I can read the same CSV file on my development machine ( Windows XP SP3 ), with absolutely no problem.

I think this is a problem derived to the 64 bit version of Windows server 2008.

I checked the versions of the msjet40.dll file on both the server and local machine:

Windows XP SP3 => 4.0.9551
Windows Server 2008 64 bit => 4.0.9755

The problem isn't on the CSV file, it's in the driver provided by Microsoft ( at least is what I think ), since I can read the CSV file perfectly on my machine.

Does one has any idea a way to solve this problem? I googled a lot, but I couldn't find anything.

Thanks..

Answer 1

You might want to have a look at "Fast CSV Reader" or "FileHelpers library" instead of using JET OLEDB.

Answer 2

Perhaps not a great answer, but if it turns out to be the driver then you might be able to work around it by using http://www.filehelpers.com/.

Answer 3

I find the Jet drivers to be so inconsistent that I usually read CSVs as text files. CSVs are fairly easy to parse, and I never have to worry about driver problems.

Answer 4

Why not use the TextFieldParser that's built into the .NET framework?

Yes folks, it's part of Microsoft.VisualBasic, but it's still a totally supported part of .Net.