Reading Excel spreadsheet via C# comes back with some empty cells

Question

I have a way of reading an excel spreadsheet row by row and converting it to CSV via C#.

Here are the basics :

static void convertExcelToCSVThenJS(string sourceFile, string worksheetName, string targetFile, string varName, StreamWriter jsWrtr )
        { 
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + sourceFile + ";Extended Properties=\"Excel 12.0; HDR=NO\"";
            //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sourceFile + ";Extended Properties=\" Excel.0;HDR=Yes;IMEX=1\""; 
            OleDbConnection conn = null;
            StreamWriter wrtr = null;
            OleDbCommand cmd = null;
            OleDbDataAdapter da = null; 
            try
            {
                conn = new OleDbConnection(strConn);
                conn.Open();

                cmd = new OleDbCommand("SELECT * FROM [" + worksheetName + "$]", conn);
                cmd.CommandType = CommandType.Text;
                wrtr = new StreamWriter(targetFile);

                da = new OleDbDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);

                //gets data from cells
                int idCount=0;
                for (int x = 0; x < dt.Rows.Count; x++)
                {                    
                    string rowString = "";
                    for (int y = 0; y < dt.Columns.Count; y++)
                    { 
                        if (dt.Rows[0][y].ToString().ToUpper().Equals("ID")) { idCount = y; };
                        var thisCell = dt.Rows[x][y];
                        var cellContain = dt.Rows[x][y].ToString().Replace("\"", "");
                        rowString += "\"" + cellContain + "\",";

                        rowString = rowString.Replace("\"", "");
                    }
                    if (dt.Rows[x][idCount].ToString() != "")
                    {
                        if (x != 1)
                        {
                            wrtr.WriteLine(rowString);
                        }

                    } 
                }
                wrtr.Close();

//and so on

This works great on most worksheets. But on one in particular it doesn't read a specific cell, it just returns it as empty.

I have overwritten this cell, i.e copied and pasted a blank cell over this one, then rewrote the string and it works fine, but I can't use this as the answer as eventually I will pass this tool to a colleague who will throw spreadsheets to it where this problem may reoccur.

Basically I set a debug point just after this line :

var thisCell = dt.Rows[x][y];

And step through each step of the for loop which goes through dt.Columns. I get to the cell I have a problem with, hover over this variable and its empty, where as in Excel it's a basic string, no formulas etc.

If I copy this cell elsewhere and try read it, the same error occurs but for the new cell.

I thought maybe data validation may be the problem, but I'm no good at Excel at the moment so I wouldn't know what to check for.

Has anyone come across this before ? Or am I going wrong somewhere ?

This is as old as the hills. Essentially, the OLEDB driver will try and 'guess' a data type, and if it guesses something that some later data doesn't fit, you'll get `DbNull.Value`. I'll try and find a duplicate... — Charles Mager, May 10 '16 at 13:04
Great stuff, the last answer on the link you provided, lead me to this : http://stackoverflow.com/questions/3232281/oledb-mixed-excel-datatypes-missing-data/5721521#5721521 which solved my problem. Just needed to add ***TypeGuessRows=0;ImportMixedTypes=Text*** to the properties :) Thank you @CharlesMager — thatOneGuy, May 10 '16 at 13:14

Reading Excel spreadsheet via C# comes back with some empty cells

0 Answers0