CODEWITHSUNDEEP
c#.netexcelcsv
Run CMD
Run CMD

Reputation: 3035

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

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;
    }

Upvotes: 3

Views: 11249

Answers (4)

Tobi G.
Tobi G.

Reputation: 1607

  • Solution for German Windows 10:

Change Windows system delimiter

  • 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?

Upvotes: 1

PlaceboZA
PlaceboZA

Reputation: 208

As mentioned by dendarii, the CSV separator that Excel uses is determined by your regional settings, specifically the 'list separator' character. (And Excel does this erroneously in my opinion, as it is called a comma seperated file)

HOWEVER, if that still does not solve your issue, there is another possible complication:

Check your 'digit grouping' character and ensure that is NOT a comma.

Excel appears to revert back to semicolon when exporting decimal numbers and has digit grouping also set to a comma. Setting the digit grouping to a full stop / period (.) solved this for me.

Upvotes: 0

Kent Boogaart
Kent Boogaart

Reputation: 178770

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"];
    }
}

Upvotes: 9

dendarii
dendarii

Reputation: 3088

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.

Upvotes: 3

Related Questions