locked
importing csv to datatable RRS feed

  • Question

  •      Hello i import a csv file to a datatable with code below.
    But i hava a problem, only one column returns from csv file to datatable.
    I can see  value  of only the  first column of csv file at datatable.
    How can i solve that and import all columns value to datatable?
    Thanks




              string path = fileSelect.FileName;

                    string full = Path.GetFullPath(path);
                    string file = Path.GetFileName(full);
                    string dir = Path.GetDirectoryName(full);

                    //create the "database" connection string
                    string connString = "Provider=Microsoft.Jet.OLEDB.4.0;"
                      + "Data Source=\"" + dir + "\\\";"
                      + "Extended Properties=\"text;HDR=No;FMT=Delimited\"";

                    //create the database query
                    string query = "SELECT * FROM " + file;

                    //create a DataTable to hold the query results
                    DataTable dTable = new DataTable();

                    //create an OleDbDataAdapter to execute the query
                    OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);

                    dAdapter.Fill(dTable);
    Friday, November 13, 2009 8:46 AM

Answers

  • What's the regional setting of your machine (control panel)? After a first look on your data, it seems like the data is formatted using the german conventions.

    It looks like your data file has also some structure problems: the fields header line ("Date";"Alloy name" etc.) is repeated throughout the data. You must filter them out (all but the first). Then use Hdr=yes in the connection string as Tamer Oz suggested above. The fields are delimited by ";" and the data formatted according to the regional standards for Germany, so if you're working on a system with different settings this might explain your problem.
    • Marked as answer by eryang Wednesday, November 18, 2009 9:57 AM
    Friday, November 13, 2009 12:49 PM
  • Hi,

    create a schema.ini file with the same directory with your csv file.

    And change the content of schema.ini file like below by opening it at notepad.

    [PASLANMAZ.CSV]
    ColNameHeader=True
    CharacterSet=ANSI
    Format=Delimited(;)

    Then your code should work fine.

    • Marked as answer by eryang Wednesday, November 18, 2009 9:57 AM
    Friday, November 13, 2009 2:03 PM
  • ... and don't forget to save the schema.ini file as ANSI (not UTF-8 or Unicode)  ;-)
    • Marked as answer by eryang Wednesday, November 18, 2009 9:57 AM
    Friday, November 13, 2009 2:15 PM

All replies

  • Hi,

    What's the delimeter, Is it ";" or another character?

    Also try with Hdr=yes

    see http://www.connectionstrings.com/textfile#p85
    Friday, November 13, 2009 8:59 AM
  • Hello,

    you should check your data. The OleDbDataAdapter does not throw an exception if for example the fields in the csv file are separate by an ";" but the system is configured to accept only "," as a separator character. In this case you would get a single field with a contents like "John;Doe;1975;New York". What data do you get in your first column?

    Marcel
    Friday, November 13, 2009 9:14 AM
  • my first csv column(Column A) has a date value , but on first row there is a string . "Date" is written

    A

    Date
    10.10.2009 13:35
    10.10.2009 13:35
    10.10.2009 13:36
    10.10.2009 13:37


    and on my second colum (Column B) has a string value .

    B

    Alloy name
    FE_250
    FE_250
    FE_250
    FE_250


    but when i import it to datatable i just see values of Column A at datatable.



    Friday, November 13, 2009 9:57 AM
  • Just to be sure. Does your file look like this?

    Date, Alloy name
    10.10.2009 13:35, FE_250
    10.10.2009 13:35, FE_250 
    10.10.2009 13:36, FE_250 
    10.10.2009 13:37, FE_250
    ...

    Also: Does the DataTable contain the right number of columns or is your problem related only to *saving* the data from the DataSet to the target database?
     

    Friday, November 13, 2009 10:03 AM
  • no my datatable just contain one column
    and it looks like this.

    Date
    10.10.2009 13:35
    10.10.2009 13:35
    10.10.2009 13:36
    10.10.2009 13:37
    Friday, November 13, 2009 10:08 AM
  • What about your file? Does it look like in my post above?
    Friday, November 13, 2009 10:10 AM
  • The file is not accessible. Place it under:

    http://cid-26578c8b54050699.skydrive.live.com/self.aspx/.Public
    Friday, November 13, 2009 12:11 PM
  • What's the regional setting of your machine (control panel)? After a first look on your data, it seems like the data is formatted using the german conventions.

    It looks like your data file has also some structure problems: the fields header line ("Date";"Alloy name" etc.) is repeated throughout the data. You must filter them out (all but the first). Then use Hdr=yes in the connection string as Tamer Oz suggested above. The fields are delimited by ";" and the data formatted according to the regional standards for Germany, so if you're working on a system with different settings this might explain your problem.
    • Marked as answer by eryang Wednesday, November 18, 2009 9:57 AM
    Friday, November 13, 2009 12:49 PM
  • Hi,

    create a schema.ini file with the same directory with your csv file.

    And change the content of schema.ini file like below by opening it at notepad.

    [PASLANMAZ.CSV]
    ColNameHeader=True
    CharacterSet=ANSI
    Format=Delimited(;)

    Then your code should work fine.

    • Marked as answer by eryang Wednesday, November 18, 2009 9:57 AM
    Friday, November 13, 2009 2:03 PM
  • ... and don't forget to save the schema.ini file as ANSI (not UTF-8 or Unicode)  ;-)
    • Marked as answer by eryang Wednesday, November 18, 2009 9:57 AM
    Friday, November 13, 2009 2:15 PM