Excel Source on SSIS 2008 brings null values (IMEX=1 not working!)

Question

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

Answer 1

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

Answer 2

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?

Answer 3

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

Answer 4

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.....

Answer 5

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?

Answer 6

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

Answer 7

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

Answer 8

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...

Answer 9

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.html

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Answer 10

From my own trial and error, it seems to me that IMEX=1 is only part of the answer.

Best I can tell if an import is left using the default settings, then the excel import will "scan" the first 8 rows (again a default setting) and make a determination on what to use based on the majority of the data in those rows. So, if 5 of the first 8 rows are numbers everything else is assumed to be numberic thus anything not numeric later in the excel file is imported as NULL.

With IMEX=1 set then the "scan" of the first 8 rows still occurs however the import makes some different assumptions.

With IMEX=1 set, if the first 8 rows are all numeric then just like before the import assumes every thing is numeric regardless of what follows and again any alphanumeric items are imported as nulls. However if only one of the 8 rows is an alphanumeric then the import assumes alphanumeric and all is good. So then the solution as I see it is to sort the incoming excel file such that you get some alphanumerics in the first 8 rows and set IMEX=1.

Or you can fudge it and insert a first line data marker row to the excel file with an alphanumeric in it, not pretty but it worked in my trials. I personally went for a re-sort of the import file not ideal be better than fudging data.

Answer 11

IMEX seems to be a programming decision. Why was it decided this way? Why not make IMEX=1 import all data as STRING regardless of what is set in the first 8 rows? Is this a business decision so programmers look for another solution? From searching the web is seems like people have had this issue forever. How come it has never been fixed or changed?

Answer 12

Hi Even iam facing the same issue can you tell me where exactly IMEX=1 should be used in the connection string so that the values such as 0001000-00023

and 1234 and 1234abc and abc1234 and abcd all of them will be uploaded to database through ssis from my excel file in a single go.

Any help will be greatly thankfull.

Thanks

Trinath

---

Trinath

Answer 13

Try inserting 5 dummy rows that includes only string values on top of the excel sheet.

This way, all the fields will be recognized as strings and all values will be displayed in the "Preview" as opposed to "NULL" displayed values.

Then you can do your data type conversions easily.

Otherwise there is no way to guarantee a datatype on a certain column when you are dependent on someone else's excelsheet.

For example, a date field might have all empty values and one incorrectly typed value on the last row such as"12s-12-1981'

The JET or ACE will think this is a string field.

Answer 14

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

For this Kind of issue simple solution is, you need to convert your Xls File to .CSV extn and Load the file That would work.

Thanks

---

Please Help People When they need..!!

Answer 15

I know this is super old but figure this might help someone.  I had a similar issue and was able to achieve what I needed using a derived column.  The expression field is pretty robust and you might be able to achieve what you need using this.  In my example I had null values for the first 5-6 rows and a integer in the last row.  It kept bring everything back as null ignoring the last row value.  I was able to use a derived column with the expression:

ISNULL([ColumnName]) ? NULL(DT_I4) : [ColumnName]

Hope this helps someone