OleDb ignores the texts starting with 0

Question

I'm using OleDbDataAdapter

I'm trying to get data from excel to DataTable, Here is my code;

var conn = new OleDbConnection(strConn);
        var myCommand = new OleDbDataAdapter(" SELECT * FROM [UPLOADFILE$] ", strConn);
        var inputTable = new DataTable();
        try
        {
            myCommand.Fill(inputTable);
        }

Here is my Conn Str;

"Provider=Microsoft.Jet.OLEDB.4.0;" +
                          "Data Source=" + "MyPath" + ";" +
                          "Extended Properties=\"Excel 8.0;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"";

Somehow, It eliminates the strings starting with "0", but others are okay.

I used the following combinations, as well;

"Provider=Microsoft.Jet.OLEDB.4.0;" +
                      "Data Source=" + "MyPath" + ";" +
                      "Extended Properties=\"Excel 8.0;IMEX=0;TypeGuessRows=0;ImportMixedTypes=Text\"";

"Provider=Microsoft.Jet.OLEDB.4.0;" +
                      "Data Source=" + "MyPath" + ";" +
                      "Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"";

"Provider=Microsoft.Jet.OLEDB.4.0;" +
                      "Data Source=" + "MyPath" + ";" +
                      "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"";

Thanks in advance!

My Tries;

Excel 8.0;HDR=No;IMEX=1;

What are you trying to do with connection to say "Somehow, It eliminates the strings starting with "0""? Please post some minimal but complete code for people to execute it and see. What do you expect the code to do? — shahkalpesh, Dec 28 '18 at 07:32
@Slai, at Excel 0323, at DataTable "". Other ex is; at Excel 1234, at DataTable "1234". Only text starting with 0s are eliminated. — , Dec 28 '18 at 08:01
you can try without `TypeGuessRows=0;ImportMixedTypes=Text` https://stackoverflow.com/questions/9936646/reading-excel-intermixed-datatype-without-modifying-registry-key — Slai, Dec 28 '18 at 08:17
@Slai Thank you for your answer! I tried. This time, the missing out values are seen correctly, but other values are shown wrong. Results; 2.3305e+009 0730311111 3051411111 1.44091e+010. The result with e's are not correct, but others(0730311111) are correct. — , Dec 31 '18 at 06:18
@ÖzerErdönmez OLEDB usually gets only the actual value without the Number Format that you probably see in the cell. That is why I asked to check the value in the Excel Formula Bar instead of the cell. To get the Number format, you can try Office Interop or some of the .xlsx libraries such as OpenXML, ClosedXML, EPPlus, etc. — Slai, Dec 31 '18 at 13:46

score 0 · Answer 1 · answered Dec 29 '18 at 02:00

For what it's worth, this is my go-to connection string for reading excel via OleDb.

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
    filename + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'; ";

For what it's worth I tried it on some data and had no issues when I had text with leading zeroes.

Is your data in a table or is it just a standard range? Does it have headers? Can you show some sample data that is erroring out?

Not standart range. It ranges 7 to 11 digits. Yes, it has header. — , Dec 31 '18 at 06:10

OleDb ignores the texts starting with 0

1 Answers1