Accessing Excel Spreadsheet with C# occasionally returns blank value for some cells

Question

Hi,

I need to access an excel spreadsheet and insert the data from the spreadsheet into a SQL Database. However the Primary Keys are mixed, most are numeric and some are alpha-numeric.

The problem I have is that when the numeric and alpha-numeric Keys are in the same spreadsheet the alpha-numeric cells return blank values, whereas all the other cells return their data without problems.

I am using the OleDb method to access the Excel file. After retrieving the data with a Command string I put the data into a DataAdapter and then I fill a DataSet. I iterate through all the rows (dr) in the first DataTable in the DataSet.

I reference the columns by using, dr["..."].ToString()

If I debug the project in Visual Studio 2008 and I view the "extended properties", by holding my mouse over the "dr" I can view the values of the DataRow, but the Primary Key that should be alpha-numeric is {}. The other values are enclosed in quotes, but the blank value has braces.

Is this a C# problem or an Excel problem?

Has anyone ever encountered this problem before, or maybe found a workaround/fix?

Thanks in advance.

Answer 1

The {} means this is some sort of empty object and not a string. When you hover over the object you should be able to see its type. Likewise, when you use quickwatch to view dr["..."] you should see the object type. What type is the object you receive?

Answer 2

The ItemArray is an Object Array. So I assume that the "column" in the DataRow, that I am trying to reference, is of type object.

Answer 3

Hi Aidan Host,

I am having the same problem here. When accessing Excel from C#, sometimes some cells give empty value (in the DataRow you can see that they are {}). Interestingly, the same program runs fine in my XP machine but not the Vista machine. I was wondering if anyone has found an answer for this? Aidan, did you have a work around?

Answer 4

For VISTA compatibility you can use EXCEL 12.0 driver in connection string. This should resolve your issue. It did mine.

Answer 5

The Excel data source picks a column type for the entire column. If one of the cells doesn't match that type exactly, it leaves blanks like that. We had issues where our typist entered a " 8" (a space before the number, so Excel converted it to a string for that cell) in a numeric column. It would make sense to me that it would try the .Net Parse methods as they are more robust, but I guess that's not how the Excel driver works.

Our fix, since we were using database import services, was to log all the rows that 'failed' this way. Then, we went back to the XLS document and re-typed those cells, to ensure the underlying type was correct. (We found just deleting the space didn't fix it--we had to Clear the whole cell first, than re-type the '8'.) Feels hacky and isn't elagent, but that was the best method we found. If the Excel driver can't read it in correctly by itself, there's nothing you can do to get that data out of there once you're in .Net.

Just another case where Office hides the important details from users in the name of simplicity, and therefore making it more difficult when you have to be exact for power uses.

Answer 6

Solution:

Connection String: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FilePath;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

1) "HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

2) "IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

SQL syntax "SELECT * FROM [sheet1$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

Important:

Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows".

That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance.

If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file."

Answer 7

Abhi your answer worked for me. Thanks

Answer 8

Solution:

1. You put HDR=No so that the first row is not considered the column header. Connection String: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FilePath;Extended Properties="Excel 8.0;HDR=No;IMEX=1";
2. You ignore the first row and you acces the data by any means you want (DataTable, DataReader ect). You acces the columns by numeric indexes, instead of column names.

It worked for me. This way you don't have to modify registers!

Answer 9

I had a similar problem, when i tried to read data from excel sheet into the dataset, and when i put IMEX=1; into connection string, everything started to work properly, just as i wanted :) Thanks ABHI!!!

Answer 10

IMEX=1 setting really helped resolving my issue mentioned at the beginning of the thread. Thanks a lot Abhi for the resolution and for bringing up this issue Aidan...

Cheers Sameer

Answer 11

I answered a similar question here. Here I've copied and pasted the same answer for your convenience:

I had this same problem, but was able to work around it without resorting to the Excel COM interface or 3rd party software. It involves a little processing overhead, but appears to be working for me.

1. First read in the data to get the column names
2. Then create a new DataSet with each of these columns, setting each of their DataTypes to string.
3. Read the data in again into this new dataset. Voila - the scientific notation is now gone and everything is read in as a string.

Here's some code that illustrates this, and as an added bonus, it's even StyleCopped!

public void ImportSpreadsheet(string path)
{
    string extendedProperties = "Excel 12.0;HDR=YES;IMEX=1";
    string connectionString = string.Format(
        CultureInfo.CurrentCulture,
        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"{1}\"",
        path,
        extendedProperties);

    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        using (OleDbCommand command = connection.CreateCommand())
        {
            command.CommandText = "SELECT * FROM [Worksheet1$]";
            connection.Open();

            using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
            using (DataSet columnDataSet = new DataSet())
            using (DataSet dataSet = new DataSet())
            {
                columnDataSet.Locale = CultureInfo.CurrentCulture;
                adapter.Fill(columnDataSet);

                if (columnDataSet.Tables.Count == 1)
                {
                    var worksheet = columnDataSet.Tables[0];

                    // Now that we have a valid worksheet read in, with column names, we can create a
                    // new DataSet with a table that has preset columns that are all of type string.
                    // This fixes a problem where the OLEDB provider is trying to guess the data types
                    // of the cells and strange data appears, such as scientific notation on some cells.
                    dataSet.Tables.Add("WorksheetData");
                    DataTable tempTable = dataSet.Tables[0];

                    foreach (DataColumn column in worksheet.Columns)
                    {
                        tempTable.Columns.Add(column.ColumnName, typeof(string));
                    }

                    adapter.Fill(dataSet, "WorksheetData");

                    if (dataSet.Tables.Count == 1)
                    {
                        worksheet = dataSet.Tables[0];

                        foreach (var row in worksheet.Rows)
                        {
                            // TODO: Consume some data.
                        }
                    }
                }
            }
        }
    }
}

Answer 12

IMEX=1

Will not help you if you have cells with more than 255 characters Eventually you will realise that you cant load the data without modifying the Excel file

BTW the idea of automation is to be able to load any file without manual intervention

There one solution which works... More Information

http://www.dbsoftlab.com/etl-tools/advanced-etl-pocessor-news/say-no-to-typeguessrows0-imex1-and-excel-odbc-bugs.html