0

I created a sub which reads all the sheets from an Excel .xlsx workbook into seprate DataTables in a DataSet using the Microsoft.ACE.OLEDB.12.0 data provider.

Now, I have one workbook with multiple sheets - All of which have the same column names - and what I'm trying to do is, once they are all loaded into the dataset, to merge them all into one large DataTable.

My problem is that even though all the sheets have the same columns, based upon the data in each sheet, sometimes the same columns get a Double Data Type and other times they get a String Data Type in the different tables of the DataSet, so, the Merge function is giving me an error.

How can I fix this? Is there a way to set the Column datatypes ahead of time to all be string or is there a way to ignore column-type while merging or is there a better way to solve this altogether?

Thanks.

John Bustos
  • 19,036
  • 17
  • 89
  • 151

1 Answers1

2

I have worked with this before.. I'm trying to go mostly from memory. There is a registry entry called TypeGuessRows that you can set. This will disable guessing the type for Excel.

Additionally, try adding the IMEX argument in your ACE connection string.

Other side note: This is probably a duplicate question.

Edit: From Google, the registry location: http://support.sas.com/kb/31/765.html

Another SO Helpful post: Reading Excel InterMixed DataType Without Modifying Registry Key

Community
  • 1
  • 1
user959729
  • 1,127
  • 2
  • 12
  • 19
  • Thanks! - I have the IMEX in, but it's still creating the issue... As for the reistry entry - I saw that but was hoping for a better solution than having to mess with that (Thanks so much for th elink, though!!) and the solution proposed on the other link is actually pretty cool, but relies on the data having a header column which may not always be the case.... I was hoping for something more generic, but maybe it just can't be done... THANKS!!! – John Bustos Jan 11 '13 at 19:32