Oledb Provider issue while read csv file

Question

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.

Answer 1

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:

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:

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.

Answer 2

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