Data missing while Reading Excel sheet using OleDbConnection in C#

Question

I need to read the excel data and store in database. when I try to read the data, there are some data got missed, I couldn't able to find.

It's my excel sheet

My code :

private string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";

 [![//Get the name of the First Sheet.
            using (OleDbConnection con = new OleDbConnection(conStr))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    cmd.Connection = con;
                    con.Open();
                    DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    sheetName = dtExcelSchema.Rows\[0\]\["TABLE_NAME"\].ToString();
                    con.Close();
                }
            }

            //Read Data from the First Sheet.
            using (OleDbConnection con = new OleDbConnection(conStr))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    using (OleDbDataAdapter oda = new OleDbDataAdapter())
                    {

                        cmd.CommandText = "SELECT * From \[" + sheetName + "\]";
                        cmd.Connection = con;
                        con.Open();
                        oda.SelectCommand = cmd;
                        oda.Fill(dtExcelData);
                        con.Close();

                        //Populate DataGridView.
                        //dataGridView1.DataSource = dt;
                    }
                }
            }

            dtExcelData = dtExcelData.AsEnumerable().Skip(2).Take(1).CopyToDataTable();

            return dtExcelData;][2]][2]

Data after reading through in oledbConnection :

King, do you want to read all data (including headers) or do you want to get only data (without headers)? — Maciej Los
– Maciej Los, Commented May 28, 2016 at 8:34
Hi Mac,how are you, data is enough. Now it is working, after I set IMEX=1 in connection — King_Fisher
– King_Fisher, Commented May 28, 2016 at 8:37
Hi King! Using IMEX=1 is good practice, but you have to define the range you want to read. Please refer this — Maciej Los
– Maciej Los, Commented May 28, 2016 at 8:40

Community · Accepted Answer · 2017-05-23 11:58:39Z

1

You have some merged cell in your excel Sheet , it's better to access your cells directly

I think here also can help you:

Excel - Reading Merged Cells (rows) in .NET using C#

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered May 28, 2016 at 6:46

Arman.Salehi

965 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

lotomax · Accepted Answer · 2016-05-28 06:49:00Z

0

You can use this class to your project easily; https://exceldatareader.codeplex.com/

answered May 28, 2016 at 6:49

lotomax

1132 silver badges12 bronze badges

Collectives™ on Stack Overflow

Data missing while Reading Excel sheet using OleDbConnection in C#

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related