none
Connection string or advance settings to read CSV file

    Question

  • Hi everyone,
    I'm moving my code from VS 2008; WindowsXP to VS 2010; Windows 7

    I read my simple CVS file with semicolon separator
    Site;Format
    12;22
    23;45

    with this snippet code:

    public void ReadCSVFile(string fileName)
    {
      string connection="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}\\;Extended Properties='Text;HDR=Yes;FMT=CSVDelimited'";

      connection=String.Format( connection, Path.GetDirectoryName(fileName));

      DataTable CSVTable = new DataTable();

      OleDbDataAdapter csvAdapter;
      csvAdapter = new OleDbDataAdapter("SELECT * FROM " + Path.GetFileName(fileName),connection);

      if (File.Exists(fileName) && new FileInfo(fileName).Length > 0)
      {
      try
      {
       csvAdapter.Fill(CSVTable);
      }
      catch (Exception ex)
      {
       throw new Exception(String.Format("Error reading Table {0}.\n{1}", Path.GetFileName(fileName), ex.Message));
      }
      }
      // ...
     
    }

    The DataTable is properly filled with two columns ("Site" and "Format") and two rows

    I run the same code with VS 2010:
    I get the CSVTable with One Column named "Site;Format" and two rows ("12;22" ,  "23;45")

    In my second trial I changed the connection into:
    string connection="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}\\;Extended Properties='Text;HDR=Yes;FMT=CSVDelimited(;)'"

    Nothing changed.
    Cindi Meister MVP Moderator said to change the windows settings (see post) or the connection.
    I would the properly connection.
    How to do?

    Thanks in advance,
    Mauro 


    Mauro
    Thursday, September 16, 2010 8:30 AM

Answers

  • Yes, I'm running Windows 7. The schema.ini file does not modify the Registry. The Registry entry for the default delimiter is specified in the Format key under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text (or HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Text if running 64-bit).

    http://msdn.microsoft.com/en-us/library/bb177651%28office.12%29.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by MauroGv Saturday, September 18, 2010 7:44 PM
    Thursday, September 16, 2010 8:36 PM

All replies

  • Did you have schema.ini file in your original project? It describes structure of CSV file. You would need to use it in your current project as well.


    Val Mazur (MVP) http://www.xporttools.net
    Thursday, September 16, 2010 11:43 AM
  • As Val indicated, you would need a schema.ini file (or the Registry entry) to specify a custom delimiter; the connection string does not support this despite the FMT parameter.

    http://msdn.microsoft.com/en-us/library/ms709353%28VS.85%29.aspx

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, September 16, 2010 1:02 PM
  • Hi Val and Paul,
    thanks for your reminders.
    I forgot it but in the past I used the schema.ini and I had always troubles.
    I put in the same folder of the CSV file source the following schema.ini
    [fileName.csv]
    ColNameHeader=True
    Format=Delimited(;)

    The custom char (semi colon) seems to be still ignored by the text driver.
    Again one column named Site;Format!!

    What do you think about it?

    Mauro


    Mauro
    Thursday, September 16, 2010 4:05 PM
  • I copied your file contents and schema.ini contents but can't reproduce the problem. It works fine for me. Are you certain the schema.ini file is in the same folder as the file?

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, September 16, 2010 6:11 PM
  • Hi Paul,
    the schema.ini is in the same folder as the file source.
    Do you run the code on Windows 7?
    Which is the register that the schema.ini modifies?


    Mauro
    Thursday, September 16, 2010 8:00 PM
  • Yes, I'm running Windows 7. The schema.ini file does not modify the Registry. The Registry entry for the default delimiter is specified in the Format key under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text (or HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Text if running 64-bit).

    http://msdn.microsoft.com/en-us/library/bb177651%28office.12%29.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by MauroGv Saturday, September 18, 2010 7:44 PM
    Thursday, September 16, 2010 8:36 PM
  • Hi Paul,
    I changed the register you showed me from CSVDelimited into Delimited(;) and the code works.
    So the Schema.ini  is ignored thinking does the same temporarily.
    How do you build the file schema.ini?
    I did with a Text editor saving with ANSI coding.
    At tomorrow night...
    Thanks,
    Mauro
    Mauro
    Thursday, September 16, 2010 9:49 PM
  • I just used Notepad and copied the settings you have. Actually I had an existing schema.ini with schema definitions for other text files as well and I just copied and pasted into that file.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, September 17, 2010 2:06 AM
  • Hi,
    I struggled for two hours but I didn't find why schema.ini is ignored. Seem so easy...
    I'll change the windows register to fill the table.

    Mauro


    Mauro
    Friday, September 17, 2010 7:20 PM