none
csv not being read properly

    Question

  • Dear Friends,

     I have a small csv.

    When it contains all text it is being read by oledb.

    But if it contains numbers it is not being read properly.

    So if my csv is like this:

    OLD_CUST, NEW_CUST
    G,         H
    I,    J
    M,    N
    65464653,  644646636
    98765465,  988924212
    G,    6546546

    H.J,N are not being read.

    1)How to tackle the above problem?

    2) I have tried with ACE but with same result.

    3) How to restrict the number of rows to be read eg 5000 when reading using OledbAdapter?

    Thanks for your efforts.


    Today's knowledge is too little for tomorrow's job
    Friday, December 10, 2010 7:42 AM

Answers

  • Hello,

    Sorry for late reply, I was busy with exams.

    You need to create Schema.ini file in the same directory as exe file is located. This Schema.ini file will contain information about your data.

    Here's sample ini file which I used for testing, works fine. Thing is, it was needed to tell Jet provider that second column is to be treated as Text.

    Also [data.txt] at top is particular file name for which Schema is defined. OldC and NewC are columnNames I chose.

    MaxScanRows is the buffer size to read number of lines at a time.

    You'll find more information about Schema.ini in documentation : Link

     

    Schema.ini file :

    [data.txt]
    Format=CSVDelimited
    ColNameHeader=True
    MaxScanRows=50
    Col1=OldC Text
    Col2=NewC Text


    Thanks

    A place for MSDN users to socialize  

    Living on Earth may be expensive, but did you know that it includes a free trip around the sun? Isn't that worth it?
    • Marked as answer by Jing0Moderator Monday, December 27, 2010 5:29 AM
    Saturday, December 11, 2010 1:29 PM
  • Schema.ini is for Jet provider not sure if applies to Ace.

    Switch to Jet provider connection string and it should work.


    Thanks

    A place for MSDN users to socialize 

    Living on Earth may be expensive, but did you know that it includes a free trip around the sun? Isn't that worth it?
    • Marked as answer by Aspen VJ Monday, December 20, 2010 7:25 AM
    Friday, December 17, 2010 10:04 AM

All replies

  • post your code.

    Thanks

    A place for MSDN users to socialize 

    Living on Earth may be expensive, but did you know that it includes a free trip around the sun? Isn't that worth it?
    Friday, December 10, 2010 9:28 AM
  • Hi,

    Here it is.

    FileInfo

     

    file = new FileInfo(flname);

     

    string csvconnstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + file.DirectoryName + "\";Extended Properties=" + "\"text;HDR=Yes;FMT=Delimited(,)\";";

     

    string csvconnstr1 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"" + file.DirectoryName + "\";Extended Properties=" + "\"Excel11.0;HDR=Yes;FMT=Delimited\";";

     

    OleDbConnection oledbconn = new OleDbConnection(csvconnstr1);

     

    int res = 0;

     

    DataTable tbl = new DataTable();

     

    OleDbDataReader read;

     

     

    string onlyname = Path.GetFileName(flname);

     

    try

    {

     

    using (oledbconn)

    {

     

    using (OleDbCommand cmd = new OleDbCommand(string.Format

    (

    "SELECT * FROM [{0}]", file.Name), oledbconn))

    {

     

    using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))

    {

    tbl =

    new DataTable("CSVTable");

    adp.Fill(tbl);

     

    //foreach (DataRow row in tbl.Rows)

     

    //{

     

    // Process the current row...

     

    //}

    }

     

    //read = cmd.ExecuteReader();

    }

     

    }

    res = 1;

    }

     

    catch (Exception ex)

    {

    logmsgf[1] = ex.StackTrace;

     

    //ff.WriteToLog(iso, "CSVDBUpload/log/CSVDBUpload_log.txt", logmsgf);

    res = ex.GetHashCode();

    }


    Today's knowledge is too little for tomorrow's job
    Friday, December 10, 2010 9:37 AM
  • Hello,

    Sorry for late reply, I was busy with exams.

    You need to create Schema.ini file in the same directory as exe file is located. This Schema.ini file will contain information about your data.

    Here's sample ini file which I used for testing, works fine. Thing is, it was needed to tell Jet provider that second column is to be treated as Text.

    Also [data.txt] at top is particular file name for which Schema is defined. OldC and NewC are columnNames I chose.

    MaxScanRows is the buffer size to read number of lines at a time.

    You'll find more information about Schema.ini in documentation : Link

     

    Schema.ini file :

    [data.txt]
    Format=CSVDelimited
    ColNameHeader=True
    MaxScanRows=50
    Col1=OldC Text
    Col2=NewC Text


    Thanks

    A place for MSDN users to socialize  

    Living on Earth may be expensive, but did you know that it includes a free trip around the sun? Isn't that worth it?
    • Marked as answer by Jing0Moderator Monday, December 27, 2010 5:29 AM
    Saturday, December 11, 2010 1:29 PM
  • Hello,

    Sorry for late reply, I was busy with exams.

    You need to create Schema.ini file in the same directory as exe file is located. This Schema.ini file will contain information about your data.

    Here's sample ini file which I used for testing, works fine. Thing is, it was needed to tell Jet provider that second column is to be treated as Text.

    Also [data.txt] at top is particular file name for which Schema is defined. OldC and NewC are columnNames I chose.

    MaxScanRows is the buffer size to read number of lines at a time.

    You'll find more information about Schema.ini in documentation : Link

     

    Schema.ini file :

    [data.txt]
    Format=CSVDelimited
    ColNameHeader=True
    MaxScanRows=50
    Col1=OldC Text
    Col2=NewC Text


    Thanks

    A place for MSDN users to socialize  

    Living on Earth may be expensive, but did you know that it includes a free trip around the sun? Isn't that worth it?


    I have already used schema.ini but with no effect.

    You r telling to keep in the same directory as the exe. - R u sure abt this?

    I had kept it in the same directory as my winforms,config files.

    What is the purpose of MaxScanRows?

    Is it optional?

    Thanks for your reply.


    Today's knowledge is too little for tomorrow's job
    Thursday, December 16, 2010 7:10 PM
  • yup! I had tried with schema.ini before posting reply and it worked perfect for me.

    Schema.ini was in same directory as my exe was. I tried in both /bin/debug and /bin/release modes.

    and I mis-judged the MaxScanRows functionality in first post. Its a setting that tells how many rows should be scan to determine column type. Sorry for that.

     

     


    Thanks

    A place for MSDN users to socialize 

    Living on Earth may be expensive, but did you know that it includes a free trip around the sun? Isn't that worth it?
    Thursday, December 16, 2010 9:00 PM
  • Hi,

    My csv is like this:

    [Datafile1.csv]

    ColNameHeader=True

    Format=Delimited(,)

    DateTimeFormat=dd-MM-yyyy

    Col1=OLD_CUST Text Width 8

    Col2=NEW_CUST Text Width 8

    It didn't work even after placing it in bin/debug directory.

     

    Then I changed it to:

    [datafile1.txt]

    Format=CSVDelimited

    ColNameHeader=True

    DateTimeFormat=dd-MM-yyyy

    MaxScanRows=50

    Col1=OLD_CUST Text Width 8

    Col2=NEW_CUST Text Width 8

    It still doesn't work.

    I think the problem is with the provider i.e oledb.Is there a better provider which can overcome such issues.


    Today's knowledge is too little for tomorrow's job
    Friday, December 17, 2010 6:41 AM
  • Schema.ini is for Jet provider not sure if applies to Ace.

    Switch to Jet provider connection string and it should work.


    Thanks

    A place for MSDN users to socialize 

    Living on Earth may be expensive, but did you know that it includes a free trip around the sun? Isn't that worth it?
    • Marked as answer by Aspen VJ Monday, December 20, 2010 7:25 AM
    Friday, December 17, 2010 10:04 AM