0

I am trying to read the following excel file with C#.

Excelsheet

I've tried both of these connection strings:

First connection string: I get the correct values for the header and null for all the other cells.

sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
              strExtendedProperties = "Excel 12.0;HDR=Yes;";

Second connection string: I get incorrect values for the header and the correct ones for all the other cells.

sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
              strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";

Using the extended properties solved my problem in reading mixed type data(header). But, now, it is not reading the decimal ,dates and percent values in my data.

With both connection strings I get for some cells null although I have values in them. How could I modify the connection string in order to read the Excel file properly?

Any help would be most appreciated.

  • What cells report nulls? Why do you think that changing the connection string will fix it? – Emond Feb 19 '16 at 10:55
  • @ErnodeWeerd I update my question –  Feb 19 '16 at 10:58
  • Have a look at this: http://stackoverflow.com/questions/3909216/oledb-read-excel-decimal-value It could be that the decimals could not be parsed – Emond Feb 19 '16 at 11:28

1 Answers1

0

Check out the following that will answer your question below. I found this from the following source http://www.codeproject.com/Questions/385351/decimal-data-in-xls-file-not-being-read-and-or-con

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

"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 [Column Name One], [Column Name Two] FROM [Sheet One$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

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. Please also note that adding the IMEX=1 option might cause the IMEX feature to set in after just 8 rows. Use IMEX=0 instead to be sure to force the registry TypeGuessRows=0 (scan all rows) to work.

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."

pyro13g
  • 11
  • 4