unable to read a particular cell from excel using reader

Question

I am importing excel into sql server db the excel sheet has three columns :

id(number only)|data|passport

before importing it i want to check for certain things such as:

the passport should begin a letter and rest of the characters must be numbers

id must be numeric only

I am able to check for passport but i am not able to check id even though i am using same code i used for checking passport.

             using (DbDataReader dr = command.ExecuteReader())
        {
            // SQL Server Connection String
            string sqlConnectionString = "Data Source=DITSEC3;Initial Catalog=test;Integrated Security=True";

            con.Open();
            DataTable dt7 = new DataTable();
            dt7.Load(dr);
            DataRow[] ExcelRows = new DataRow[dt7.Rows.Count];
            DataColumn[] ExcelColumn = new DataColumn[dt7.Columns.Count];

            //=================================================
            for (int i1 = 0; i1 < dt7.Rows.Count; i1++)
            {

                if (dt7.Rows[i1]["passport"] == null)
                {
                    dt7.Rows[i1]["passport"] = 0;

                }
                if (dt7.Rows[i1]["id"] == null)
                {
                    dt7.Rows[i1]["id"] = 0;
                }

                string a = Convert.ToString(dt7.Rows[i1]["passport"]);
                string b = dt7.Rows[i1]["id"].ToString();

                if (!string.IsNullOrEmpty(b))
                {
                    int idlen = b.Length;

                    for (int j = 0; j < idlen; j++)
                    {
                        if (Char.IsDigit(b[j]))
                        {
                            //action
                        }
                        if(!Char.IsDigit(b[j]))
                        {
                            flag = flag + 1;
                            int errline = i1 + 2;
                            Label12.Text = "Error at line: " + errline.ToString();
                            //Label12.Visible = true;
                        }
                    }
                    if (!String.IsNullOrEmpty(a))
                    {
                        int len = a.Length;

                        for (int j = 1; j < len; j++)
                        {
                            if (Char.IsLetter(a[0]) && Char.IsDigit(a[j]) && !Char.IsSymbol(a[j]))
                            {
                                //action
                            }
                            else
                            {
                                flag = flag + 1;
                                int errline = i1 + 2;
                                Label12.Text = "Error at line: " + errline.ToString();
                                //Label12.Visible = true;
                            }


                    }
                }




                 }

For some strange reason when i use breakpoint i can see the values of id as long as id is numeric in excel the moment flow comes to cell which has id as 25h547 the value if b turn "" any reason for this? i can give you entire code if you require.

Check [this](http://stackoverflow.com/questions/3354453/visual-studio-debugger-displaying-integer-values-in-hex) one, may be the same problem — andy, Nov 06 '12 at 05:23
its ["id"] everywhere in code as you can see above, i used break point and found that dt7 has all the values of the excel sheet except the one which contains both digits and alphabet — Arbaaz, Nov 06 '12 at 08:43
i was thinking maybe "id" will be treated as a reserved word so was asking to try with `"[id]"` instead of `"id"` — Flakes, Nov 06 '12 at 08:47
pl check the IMEX option in this standard connection string. [excel](http://www.connectionstrings.com/excel) also [this](http://microsoft-ssis.blogspot.in/2011/06/mixed-data-types-in-excel-column.html) — Flakes, Nov 06 '12 at 09:08
@Flakes Yes it was IMEX i found the answer after lots of googling, the problem was that the datatable or adapter or whatever was assuming the entire column to be of datatype same as the first record in that column so if the first record in column is numeric it will treat the rest as numeric, but since you consistently tried to solve my problem and finally got the right answer (even though i got it first), why dont you answer this question (not comment) and i will choose it as the right answer lol , btw whats this imex all about? can you explain it a bit? — Arbaaz, Nov 06 '12 at 09:13
I've run into this same problem once. If the first cell in a column is a number, it will try to read all values in that column as numbers. Then once it comes across values that aren't a number, they end up empty. As I controlled the input, I just moved some values around and it had a non-number as first value, resulting in reading all values as strings. Maybe there is some way of forcing selection of the values to be read as strings? — René Wolferink, Nov 06 '12 at 09:41

score 2 · Accepted Answer · answered Nov 07 '12 at 04:06

What seems to be happening is that when the data is imported into the holding datatable and the first record in column is alphanumeric it will assume all the records in the column to be alphanumeric if the first one is numeric it will assume that all records in the column are numeric and therefore will be blank for alphanumeric records which occur somewhere in column. I solved the problem myself by modifying connectionstring : "Excel 8.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text"

"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text.

Flakes · Answer 2 · 2012-11-06T10:04:35.927

specify the imex mode in connectionstring to handle mixed values

See: Mixed values in excel rows

Missing values. The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type. (In a tie, the numeric type wins.) Most cell formatting options in the Excel worksheet do not seem to affect this data type determination. You can modify this behavior of the Excel driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window

unable to read a particular cell from excel using reader

2 Answers2