GeekZilla
DTS, Excel and mixed format columns
I recently had a problem DTS importing an Excel spreadsheet which contained columns with mixed text and data values. After a bit of hunting around, I found an extended property IMEX=1. The IMEX=1 property forces everything to text when reading from a source.
Easy to change in your connection string, but how do you edit it in DTS? Answer: Dynamic Properties.
In DTS:
- Add a Dynamic Properties step (before the copy data step)
- Add a new "change" for "Your Connection > OLE DB Properties > Extended Properties"
- Give it a constant value of Excel 8.0;HDR=YES;IMEX=1
Paul is the COO of kwiboo ltd and has more than 20 years IT consultancy experience. He has consulted for a number of blue chip companies and has been exposed to the folowing sectors: Utilities, Telecommunications, Insurance, Media, Investment Banking, Leisure, Legal, CRM, Pharmaceuticals, Interactive Gaming, Mobile Communications, Online Services.
Paul is the COO and co-founder of kwiboo (http://www.kwiboo.com/) and is also the creator of GeekZilla.
Comments
Anonymous
said:
I've been trying to fix this for 2 hours now, reading all these potential solutions on the web.
But all I needed was your short article.
Thx so much!
Paul Hayman
said:
Glad to help 
Mastermnd
said:
Thank you! Best (simplest) explanation I've read on the web about this so far.
dabuskol
said:
It does work for some fields but my site no which have data such as '177-11' is missing from the field even trying to put IMEX=1.
Need your help..
dabuskol
said:
not working for me even added IMEX=1 such as '177-22' is missing.
Sudesh
said:
Gr8!, this works fine in my case
said:
Thanks this worked great!
