CSV is actually .... Semicolon Separated Values ... (Excel export on AZERTY)

c# .net excel csv

10,195

Solution 1

One way would be to just use a decent CSV library; one that lets you specify the delimiter:

using (var csvReader = new CsvReader("yourinputfile.csv"))
{
    csvReader.ValueSeparator = ';';
    csvReader.ReadHeaderRecord();

    while (csvReader.HasMoreRecords)
    {
        var record = csvReader.ReadDataRecord():
        var col1 = record["Col1"];
        var col2 = record["Col2"];
    }
}

Solution 2

Check what delimiter is specified on your computer. Control Panel > Regional and Language Options > Regional Options tab - click Customize button. There's an option there called "List separator". I suspect this is set to semi-colon.

Solution 3

Solution for German Windows 10:

Mention to change the decimal separator to . and maybe thousands separators to (thin space) as well.

Can't believe this is true...Comma-separated values are separated by semicolon?

10,195

Author by

Run CMD

Updated on June 28, 2022

Comments

Run CMD almost 2 years

I'm a bit confused here.

When I use Excel 2003 to export a sheet to CSV, it actually uses semicolons ...

Col1;Col2;Col3
shfdh;dfhdsfhd;fdhsdfh
dgsgsd;hdfhd;hdsfhdfsh

Now when I read the csv using Microsoft drivers, it expects comma's and sees the list as one big column ???

I suspect Excel is exporting with semicolons because I have a AZERTY keyboard. However, doesn't the CSV reader then also have to take in account the different delimiter ?

How can I know the appropriate delimiter, and/or read the csv properly ??

    public static DataSet ReadCsv(string fileName)
    {
        DataSet ds = new DataSet();
        string pathName = System.IO.Path.GetDirectoryName(fileName);
        string file = System.IO.Path.GetFileName(fileName);
        OleDbConnection excelConnection = new OleDbConnection
        (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties=Text;");
        try
        {
            OleDbCommand excelCommand = new OleDbCommand(@"SELECT * FROM " + file, excelConnection);
            OleDbDataAdapter excelAdapter = new OleDbDataAdapter(excelCommand);
            excelConnection.Open();
            excelAdapter.Fill(ds);
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally 
        {
            if(excelConnection.State != ConnectionState.Closed )
                excelConnection.Close();
        }
        return ds;
    }

Run CMD almost 14 years

This seems like a great library ... Can we use this in a commercial application ? We couldn't make that out from the license ... Also, do you need an attribution in our "About" window, and what would that have to be ? Thanks for your answer and for sharing your library.
aroon65 almost 14 years

Absolutely - the license is about as permissive as they come. No need for credits - your upvote here is sufficient :) But if you really like it, a review on the codeplex site would be much appreciated.
Run CMD almost 14 years

Unfortunately we are targeting .NET 2.0 and cannot add a reference to the KBCsv lib. We tried to open the source code project, but we only have VS2003 & VS2008. Is there a version for .NET 2.0 available? Or am I missing something maybe? Thanks a lot though :-)
aroon65 almost 14 years

I removed official .NET 2.0 support just because I wanted to reduce my time commitments. But since you want it, I've added a 2.0 build of the latest release for you. See kbcsv.codeplex.com/releases/view/36254
Run CMD almost 14 years

Wow man, you're the best! Thanks. Just for correctness: csvReader.ValueDelimiter = ';'; should be csvReader.ValueSeparator = ';'; for our purpose here. (That gave confusing results at first :-)
aroon65 almost 14 years

You're welcome. Thanks for the correction - I've updated my post. Yes, delimiters delimit values that need to be (i.e. values that contain the separator character), and separators, er, separate values.
Tobi G. about 8 years

Some tools save Tab-separated values with .tsv instead.