3

I'm using an OleDbDataAdapter (Microsoft.ACE.OLEDB.12.0 to be precise) to retrieve data from an Excel workbook. For one table I'm using a typed dataset but for another table I can't do that since the number of columns is unknown (the Excel template may generate extra columns).

The problem was that if someone enters too many numeric values in a column, "JET" seems to assume it's a numeric column and the textual values are not loaded anymore. I know you can change the template and set the specific data type for that column but the template is already widely spread so I'd rather resolve it during import.

Now what I tried was first counting the number of used columns and preparing a new DataTable with a defined Columns collection and setting their DataType property to typeof(string). Sadly JET doesn't seem to be looking at this and still chooses it's own way. I'm guessing that even if I could use a strongly typed dataset here, it wouldn't help either...

Does anyone know how to tell JET how to import the data so I don't have to face the burden of delivering a new template version?

Please *PLEASE*: don't come with an Excel automation solution...

Koen
  • 3,626
  • 1
  • 34
  • 55

1 Answers1

2

If you have access to the registry set TypeGuessRows=0 and/or ImportMixedTypes=Text. See here for more info INITIALIZATION SETTINGS

  • 1
    I found you can set those in the extended properties of my oledb connection string as well so that would indeed solve my problem. I need to set IMEX=1 as well for this to work... – Koen Jun 09 '11 at 15:53
  • 2
    In fact, only setting IMEX=1 does the trick already. Seems like those "initialization settings" don't really affect the connection... – Koen Jun 09 '11 at 15:59
  • 2
    @Koen IMEX and HDR can be set in the extended properties but I'm certain TypeGuessRows is a registry only setting. It depends on the layout of the data and where the mixing of data types occurs that influence the affect of the settings. –  Jun 09 '11 at 16:01
  • 1
    Never change in the registry what can be changed at runtime. Indeed, I think one should almost never edit the registry for something like this, since you never know what dependencies there might be on the setting you happen to be changing. – David-W-Fenton Jun 12 '11 at 22:26