Missing data trying to read an excel file In c# using OleDb

Question

I have a problem when I try to read a Excel file in C#

filePath = txtExcelFile.Text;
                string[] fileSpit = filePath.Split('.');
                if (filePath.Length > 1 && fileSpit[1] == "xls")
                {
                    connString = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=No'";
                }
                else
                {
                    connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=No'";
                }
OleDbCommand cmd = new OleDbCommand(@"Select * from [" +comboBox1.SelectedValue.ToString() + "]", ole);
OleDbDataAdapter oledata = new OleDbDataAdapter();
oledata.SelectCommand = cmd;
DataSet ds = new DataSet();
oledata.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;

But it only shows only partial data. I have attached images.

Please include the code that sets up the Connection to the Excel spreadsheet. — mjwills
– mjwills, Commented Aug 10, 2017 at 11:28
Possible duplicate of Can I specify the data type for a column rather than letting linq-to-excel decide? — mjwills
– mjwills, Commented Aug 10, 2017 at 11:28
Yap, I added code Connection Excel. But it's still dont work :( — Kate Mid
– Kate Mid, Commented Aug 11, 2017 at 2:43

Rob Anthony · Accepted Answer · 2017-08-11 06:17:07Z

1

It reads the first few records and decides that the column is of type integer, which fails when it finds data which is not of integer type.

You need to set HDR=No as a property in your connection string and IMEX=1

edited Aug 11, 2017 at 6:17

answered Aug 10, 2017 at 18:50

Rob Anthony

1,8431 gold badge17 silver badges20 bronze badges

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

6 Comments

Kate Mid Over a year ago

Thanks Rob, but i edited HDR=No, but it's dont work :( I dont know what's the error :(

Rob Anthony Over a year ago

Do you have a header in your file? If not, put one in as HDR=No works by reading the header as days and forcing all the data to be read as text. I've also suggested the IMEX=1 change too.

Kate Mid Over a year ago

Yes, thanks Rob. I added IMEX=1 and it's work. But i have a new problem.DataGridview show data lines than excel file.

Kate Mid Over a year ago

Exp: Excel file has 20 rows but datagridview show 25 rows, 20 rows in excel file and 5 blank rows . So, i cant right RowCount DataGridView @@

Rob Anthony Over a year ago

try deleting the 'blank' rows in Excel, they may not actually be blank.

|

Darthchai · Accepted Answer · 2017-08-11 07:12:51Z

1

I ran into this same type of issue before and no matter the settings in the connection string I still had missing column data. In order to work around it, I ended up using the open source ExcelDataReader.DataSet nuget package(https://github.com/ExcelDataReader/ExcelDataReader) which can read Excel files without using the oledb driver or having excel installed.

Below is the code that should work for you as I created both an .xls and .xlsx using your sample data, and the dataset returned contained all values. The ExcelDataReader will also detect whether it's an .xls or .xlsx.

       var filePath = txtExcelFile.Text;

        using (var stream = File.Open(filePath,FileMode.Open,FileAccess.Read))
        {
            using (var reader = ExcelReaderFactory.CreateReader(stream))
            {
                var ds = reader.AsDataSet();
            }

            dataGridView1.DataSource = ds.Tables[0].DefaultView;
        }

answered Aug 11, 2017 at 7:12

Darthchai

7558 silver badges18 bronze badges

2 Comments

Kate Mid Over a year ago

Yes, I undertand and thanks Darthchai. I added HDR and IMEX into connection code and it's work.

gojimmypi Over a year ago

OMG thank you for this! The ExcelDataReader works great! I have been fussing with connection strings for a couple of DAYS now, trying to figure out the occasionally missing data (it was certainly not a type issue, as at least a hundred thousand rows had mixed string/numeric data). OH and yes, this is considerable faster.

P. Zantinge · Accepted Answer · 2017-08-11 06:32:39Z

0

It might also help if you specify the column data type in excel an set it to text. I think this works.

If not, you could also try inserting a dummy row on line 2 (or 1 if hdr=no) and skip over it when processing the file.

Or you could use an other sollution to read the file. I personally prefer ooxml because you won't have to depend on the oledb driver beeing installed.

edited Aug 11, 2017 at 6:32

answered Aug 11, 2017 at 6:24

P. Zantinge

1951 silver badge16 bronze badges

1 Comment

Kate Mid Over a year ago

Yes, I added HDR=NO and IMEX=1, so it's work.Thanks Dexter :)

Collectives™ on Stack Overflow

Missing data trying to read an excel file In c# using OleDb

3 Answers 3

6 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related