none
Microsoft.Ace.OLEDB.12.0 not working with csv mixed datatype column on both 32 bit and 64 bit applications RRS feed

  • Question

  • Hi,

    We are trying to read csv file using Microsoft.Ace.OLEDB.12.0 provider and csv file contains one columns has a string and numeric data type (column C4 in CSV below). I have two versions of my application one targets 32 bit and another one targets 64 bit platform. For both these applications, only numeric data can be read using the Microsoft.Ace.OLEDB.12.0 data provider. Our application is unable to read the string data. Please see the csv below

    Here is the example code. Please help.

    using (OleDbConnection con = new OleDbConnection("Provider=Microsoft.Ace.OLEDB.12.0;Data Source=\"" + file.DirectoryName + "\";Extended Properties='text;HDR=Yes;FMT=Delimited(,)';"))
                {
                    using (OleDbCommand cmd = new OleDbCommand(string.Format("SELECT * FROM [{0}]", file.Name), con))
                    {
                        con.Open();

                        // Using a DataReader to process the data
                        using (OleDbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                // Process the current reader entry...
                                //Console.WriteLine(reader.);
                            }
                        }

                        // Using a DataTable to process the data
                        using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
                        {
                            DataTable tbl = new DataTable("MyTable");
                            adp.Fill(tbl);

                            foreach (DataRow row in tbl.Rows)
                            {
                                // Process the current row...
                            }
                        }
                    }
                }

    Example csv is here -

    C1,C2,C3,C4
    AV,20160307,AST,213
    ABCD,20160307,sfsdf,clkass / CD
    ABCD,20160307,dfsd,D-204K
    ABCD,20160307,sfsf,12
    ABCD,20160307,dsfgf,3.12
    ABCD,20160307,dfgsdf,0.01 feet
    ABCD,20160307,dgfsdg,clkass / CD
    ABCD,20160307,dfgdfg,20.1
    ABCD,20160307,sgfgfdsg,11.111
    ABCD,20160307,sdfgsdfgds,1.111
    ABCD,20160307,sdfgdgfsdg,0.0005 rst
    ABCD,20160307,sdfgsdgs,1.0
    ABCD,20160307,sdgfsdgfds,0.01 rst
    ABCD,20160307,sdgfdfgsd,2.0
    ABCD,20160307,dgsdg,0.01 rst
    ABCD,20160307,gsdfgsdgsd,11.1
    ABCD,20160307,sgdfgs,22.2
    ABCD,20160307,sdgfsdfgs,1.0
    ABCD,20160307,dfgsdg,0.01 rst
    ABCD,20160307,dsgfsdg,1.012643
    ABCD,20160307,sgdf,0.01 rst
    ABCD,20160307,sgsdgfd,2.0
    ABCD,20160307,gsdgf,0.01 rst



    • Edited by sushilpatil009 Tuesday, April 26, 2016 1:45 PM corrected the title
    Tuesday, April 26, 2016 1:37 PM

Answers

All replies

  • Hi sushilpatil009,

    According to code snippet, I create a simple console demo as below, it seems ok, could you please provide a bit more information (such as detailed error message).

    static void Main(string[] args)
            {
                string dataPath = @"D:\Data\CSV\";
                string connectionString = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=\"" + dataPath + "\";Extended Properties='text;HDR=Yes;FMT=Delimited(,)';";
                using (OleDbConnection con = new OleDbConnection(connectionString))
                {
                    using (OleDbCommand cmd = new OleDbCommand(string.Format("SELECT * FROM {0}", "Test2.csv"), con))
                    {
                        con.Open();
                        // Using a DataReader to process the data
                        using (OleDbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                // Process the current reader entry...
                                Console.WriteLine("TTT-- {0} -- {1} -- {2} -- {3}", reader[0],reader[1],reader[2],reader[3]);
                            }
                        }
                        // Using a DataTable to process the data
                        using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
                        {
                            DataTable tbl = new DataTable("MyTable");
                            adp.Fill(tbl);
    
                            foreach (DataRow row in tbl.Rows)
                            {
                                // Process the current row...
                            }
                        }
                        Console.ReadKey();
                    }
                }
            }

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, April 27, 2016 6:51 AM
    Moderator
  • The column C4 seems to be mixed alphanumeric characters. Mixed mode columns can exhibit unexpected behavior because the driver has to guess at the data type based upon the content. In this instance I would recommend using a schema.ini file to define your column data types.

    https://msdn.microsoft.com/en-us/library/ms709353%28v=vs.85%29.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, April 27, 2016 1:54 PM