Hi! I'm trying to import some XLS files that I receive from some suppliers. The problem is that every time they send some columns with text values but formatted as number. When I read those columns with SSIS Excel Source, they come all with null values. I don't want to change the columns data types every time, so I would like to know if there's a way to bypass the column types that are already there. I tried to use both the Jet driver and the Office 12 driver. I've already used the IMEX=1 on ExtendedProperties too with no success. Is there a way to force reading the columns as text, even if they have data types assigned to them? Thanks! Fernando Moyses

Fernando, There is no such thing as a "data type" in Excel. There is only formatting, and the Excel provider doesn't respect formatting in Excel spreadsheets. What it does is sample the first eight rows (by default) in order to "guess" at what data type it should use. Again, formatting on columns is not relevant at all. The IMEX argument isn't a cure-all (unfortunately). It's typically (incorrectly) described as "reading all values as text" - I think I've made that mistake. What it does is instruct the Excel driver to go into "import mode", which instructs the driver to interpret columns that have numbers AND text to be imported as text only, instead of interpreted according to it's regular "guessing". See this extensive series of blog posts for more details. Now - to your issue - there must be something else going on with your spreadsheet. Even with "regular" rules (non-IMEX), it doesn't seem possible that a columns with values in it would import as all NULLs. The regular behaviour would be to perhaps import some of the values as NULLs - but definitely not all of them. So - to help us troubleshoot with you, please describe the results when you try with the various settings you've tried. "It doesn't work" won't provide us with enough information. Talk to me now on

Same issue. I first set up the file with "hello" in every column to get string 255 on the source column properties. Some of my columns have 000 or 00 in them -- not exclusively, one row might contain 2EG. That cell is NULL on import. IMEX did nothing to help. The NULL is a big issue, I can handle it making other cells 0 (the 000 became 0 -- which I then fixed with a derived column). I can't fix a NULL when it basically deleted the value 2EG. Using Excel 2007 -- SQL Server 2008. Thoughts?

Hello, Just to make sure you are putting IMEX=1 at right place in Extended properties, One day i struggle alot with this issue and i was putting IMEX=1 at very end and that was not right.So i was still getting Nulls even values were there. I have Excel 2003 but IMEX position can be seen in connection string Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Test\New Microsoft Excel Worksheet.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1 "; See maybe you did the same mistake I was repeating:( Thanks

You would have to write C# script to loop though cells and load data. I have faced similar problems in the past and i have forced the end user to provide either CSV or Text files (which might not always be possible). So Script task is the way to go.....

I was afraid of that... Any sample scripts out there? What is the basic mentality -- use a script task to stage the data in a database table? Then flow it from that table instead of the excel file to wherever it needs to go? Or do you go to a recordset variable?

Aamir -- I have the IMEX=1 in the exact same position. Still get NULLs.

You really should read through Douglas Laudenschlager's posts. He explains ALL of the issues there. Failing that, you may want to try the open-source Excel2 Connection Manager, or CozyRoc's Excel Connection Manager. Talk to me now on

Thanks Todd... We have decided on a ghetto fix. We are having the users insert 5 rows of "do not delete" at the top of the data. The rows can be hidden but even with 1,000's of rows following it seems to do the trick. Hopefully I will have time post production to rewrite using some .NET code to access, but the users are so happy with everything else that they might not care. I had thought about Cozy's control, and have already adviced my next job to purchase the suite, but it would take too long to get it funded here. THANKS all...

Here are some examples and problem descriptions with mixed datatypes and IMEX: http://microsoft-ssis.blogspot.com/2011/06/mixed-data-types-in-excel-column.htmlPlease mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com