0

I am trying to read data from Excel (xls) file via this connection string but some data's are missing under specific column.

Connection String is below; 

        props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
        props["Extended Properties"] = "\"Excel 12.0;HDR=YES;IMEX=1;ImportMixedTypes=Text\"";
        props["Data Source"] = path;

My Excel data's are like that, Missing Data's are under column "G" because of date value in the second row..

enter image description here

I tried everything under OleDB & mixed Excel datatypes : missing data but not worked..

Thx!

Community
  • 1
  • 1
bakin
  • 45
  • 8

1 Answers1

0

ACE.OLEDB driver uses a registry setting to determine each column's data type. By default the number of rows to examine and guess the column's type is set to 8.

Your date value fits exactly into this first block of cells. Therefore, most probably ACE tries to read the rest of rows in G column as dates.

Here is what you can do:

  1. Remove the date value in G column (probably your requirements doesn't allow that).
  2. Force Excel to treat your 07.11.2016 date value as text without applying any auto-format. To achieve this prepend your date value with the single quote character ' in the same cell or manually set the date's cell data type to Text via Cell properties. Then try reading again.

HTH

andrews
  • 2,173
  • 2
  • 16
  • 29
  • The second advice solved my problem but , I can't request user to add single quote to this cell.. I need technical solution(code or configuration). – bakin Feb 15 '17 at 06:29
  • @bakin do you have control over the blank .xlsx templates which are then populated with data? If yes then you can pre-format all first cells in the G column as text by right-clicking then Format Cells...and choose Text in the Number tab. Then, when your users will be entering dates there, those dates should be saved as text. Try reading ur xls using this scenario. – andrews Feb 15 '17 at 08:14