2

I am trying to import data from an .xlsx document. It keeps reading the data types incorrectly. I need every column to come in as a string.

My connection string looks like this:

"Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + FileName + "; Extended Properties = 'Excel 12.0 Xml; HDR = Yes; IMEX = 1';"
informatik01
  • 16,038
  • 10
  • 74
  • 104
Price Jones
  • 1,948
  • 1
  • 24
  • 40
  • 1
    Specify _"incorrectly"_. – Tim Schmelter Jan 22 '13 at 15:32
  • 1
    Have you looked at this? [Reading Excel InterMixed DataType Without Modifying Registry Key](http://stackoverflow.com/questions/9936646/reading-excel-intermixed-datatype-without-modifying-registry-key) – user959729 Jan 22 '13 at 15:33
  • 1
    IMEX uses a tiny row sampling to determine the datatype, if you have 20 numeric value then 100k strings your out of luck as its decided the column is numeric – Alex K. Jan 22 '13 at 15:43
  • Good to know about IMEX. Yes, my columns appear to be numeric for the first 700 rows, and then begin to display string characters. – Price Jones Jan 22 '13 at 16:02

1 Answers1

4

You may want to try something like linqtoexcel this turns excel worksheets into objects which can be queried using linq queries. Then you can turn columns into string if you want to by quering the linq objects.

Peter Campbell
  • 661
  • 1
  • 7
  • 35