Reading from excel file using oledb - empty strings

Question

I'm using oledb to read from excel file.

    DataTable sheet1 = new DataTable();
    string excelCS = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"";

    using (OleDbConnection connection = new OleDbConnection(excelCS))
    {
        connection.Open();
        string selectSql = @"SELECT * FROM [Sheet1$]";   
        using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
        {
            adapter.Fill(sheet1);
        }
        connection.Close();
    }

But there is a problem with some cells of the file.

For some cells I get an empty value instead of text. I tried to put some other text into these cells but it didn't work - I'm still getting empty strings. But after deleting the column and inserting again my application get the right value of cell. Important is that the problem is not with all cells in the column.

Is this a problem with cell format or something? This excel file will be generated by another system so I won't be able to modify it manually.

Has anybody any sugestions what's wrong and what can I do?

Thank you, I do this for the first time and I didn't know about it. Now it works perfectly. Thank you so much! — , Dec 10 '13 at 14:13
Glad it helped. Read [HERE](http://www.connectionstrings.com/excel-2007/) to know more about IMEX — Siddharth Rout, Dec 10 '13 at 14:15

score 1 · Answer 1 · answered Dec 16 '14 at 02:31

Use IMEX = 1 at the end of your connection string. That will fix your problem.

To always use IMEX=1 is a safer way to retrieve data for mixed data columns. .."

Remember that, sometimes there are some errors involved using IMEX while you're using Update rather than Selecting.

score 1 · Answer 2 · answered Jan 13 '16 at 07:03

using this method convert Execel to Dataset without Empty String in c#

 public static DataSet ConvertExcelToDataTable(string FileName)  
    {
        DataSet ds = new DataSet();
        string strConn = "";

        strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + FileName + ";Extended Properties=\"Excel 8.0; HDR=YES; IMEX=1;\"";
        OleDbDataAdapter da = new OleDbDataAdapter
        ("SELECT * FROM [Sheet1$]", strConn);


        da.Fill(ds);

        return ds;          
    }

it will return dataset.

score 0 · Answer 3 · edited May 23 '17 at 12:25

0

I had this issue. What I found was that on the cells that returned blank values the data looked like strings, but the rest of the data looked like numbers, so excel stored the strings in a different place as the numbers. I changed the column format to text and all the data was picked up.

This thread might help with changing the format: Format an Excel column (or cell) as Text in C#?

edited May 23 '17 at 12:25

Community

1
1

answered Apr 21 '17 at 12:53

Lanklaas

2,870
1
15
18

Reading from excel file using oledb - empty strings

3 Answers3