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

Question

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

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

HTH,
Kent

Answer 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.