question

ShaileshDMistry-7467 avatar image
0 Votes"
ShaileshDMistry-7467 asked ShaileshDMistry-7467 answered

Oledb Provider issue while read csv file

We can read csv file using oledb provider since long time but today i found one major issue, in csv a column value start with DM-number that give only number value instead of DM-number. i had attached here my code oledb csv reading code and csv file data in image ...
please suggest why this happend when value start with DM.120684-csv-reading-issue.png


dotnet-csharp
csv-reading-issue.png (106.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DanielZhang-MSFT avatar image
1 Vote"
DanielZhang-MSFT answered DanielZhang-MSFT edited

Hi ShaileshDMistry-7467,
For some specially formatted text, you need to use the Schema.ini file to read all columns as text.
Here is a code example:
My test.csv:
120648-855.png

 private void Form1_Load(object sender, EventArgs e)
 {
    
     dataGridView1.DataSource = ImportCSVData();
 }
         
 private DataTable ImportCSVData()
 {
     DataTable dt = new DataTable();
     OleDbConnection conn = null;
     try
     {
         string strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "C:\\Users\\Desktop\\" + ";Extended Properties='text;HDR=Yes;FMT=Delimited(,)';";
         string sql_select;
         conn = new OleDbConnection(strConnString.Trim());
         sql_select = "select * from [" + "test.csv" + "]";
         conn.Open();
         OleDbCommand cmd = new OleDbCommand(sql_select, conn);
    
    
         OleDbDataAdapter obj_oledb_da = new OleDbDataAdapter(cmd);
         DataTable dtSchema = new DataTable();
         obj_oledb_da.FillSchema(dtSchema, SchemaType.Source);
    
    
         if (dtSchema != null)
             writeSchema(dtSchema);
                       
    
         obj_oledb_da.Fill(dt);
    
     }
     finally
     {
         if (conn.State == System.Data.ConnectionState.Open)
             conn.Close();
     }
     return dt;
 }
 private void writeSchema(DataTable dt)
 {
     try
     {
         FileStream fsOutput = new FileStream("C:\\Users\\Desktop" + "\\schema.ini", FileMode.Create, FileAccess.Write);
         StreamWriter srOutput = new StreamWriter(fsOutput);
         string s1, s2, s3, s4, s5;
         s1 = "[" +"test.csv" + "]";
         s2 = "ColNameHeader=True";
         s3 = "Format=CSVDelimited";
         s4 = "MaxScanRows=0";
         s5 = "CharacterSet=ANSI";
         srOutput.WriteLine(s1 + '\n' + s2 + '\n' + s3 + '\n' + s4 + '\n' + s5);
         StringBuilder strB = new StringBuilder();
         if (dt != null)
         {
             for (Int32 ColIndex = 1; ColIndex <= dt.Columns.Count; ColIndex++)
             {
                 strB.Append("Col" + ColIndex.ToString());
                 strB.Append("=F" + ColIndex.ToString());
                 strB.Append(" Text\n");
                 srOutput.WriteLine(strB.ToString());
                 strB = new StringBuilder();
             }
         }
    
    
         srOutput.Close();
         fsOutput.Close();
     }
     catch (Exception ex)
     {
       log.Info("Exception", ex);
     }
 }

The result:
120649-85.png
Best Regards,
Daniel Zhang


If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




855.png (2.1 KiB)
85.png (3.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ShaileshDMistry-7467 avatar image
0 Votes"
ShaileshDMistry-7467 answered

Thank you DanielZhang-MSFT

I had found another solution for generate datatable from csv


 // Bellow Code for call function with pass file path & data table name  
   ds.Tables.Add(ReadCsvFile(strDirPath + "\\" + strFileName, "tblCsv"));
    
 // bellow method read csv file and return data table 
 // input csv File name and data table table name 
     public static DataTable ReadCsvFile(string csvFileName, string tblName)
             {
                 DataTable dt = new DataTable(tblName);
                 DataRow dr;
                 //string csvFileName = "C:\\Users\\Administrator\\Desktop\\Ecsv\\11.csv";
                 string[] rows = File.ReadAllLines(csvFileName);
                 foreach (string str in rows)
                 {
                     if (str.Trim().Length == 0) continue;
                     string[] arr = str.Split(',');
                     if (dt.Rows.Count == 0)
                     {
                         for (int i = 1; i <= arr.Length; i++)
                             dt.Columns.Add("F" + i.ToString());
                     }
                     dr = dt.NewRow();
                     for (int i = 1; i <= dt.Columns.Count; i++)
                         //dt.Columns.Add("F" + i.ToString());
                         dr["F" + i.ToString()] = arr[i - 1];
                     dt.Rows.Add(dr);
                 }
                 return dt;
             }



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.