SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Ying Zhang / How to Handle NULL Values Loaded by SSIS from Excel Files / Latest Posts

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

Sune de Vos — Fri, 19 Aug 2011 04:50:13 GMT

Thanks thejackfy :-) Great workaround!

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

Peter Schott — Tue, 05 Jan 2010 16:57:04 GMT

Sounds very much like the registry settings may have been edited - perhaps with a support ticket or similar. That sounds suspiciously like the option to read all rows in a column to determine its type. Otherwise, your package could be treating that column as just about any other type and running into issues w/ conversion.I assume that you've verified for all of the non-NULL columns that the data contains a valid date and not something that's almost a date. Is it possible that it's trying to read in some slightly different format for the date and choking on that? Perhaps trying to read dd-mm-yyyy instead of mm-dd-yyyy or yyyymmdd?Without knowing too much more about the file(s), is it also possible that the files that have run successfully have had fully populated date columns? I know that once you release a spec into the wild you don't really know what you'll get back.

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

niall.baird — Tue, 05 Jan 2010 16:39:16 GMT

Use a derived column to convert the datestring to the format yyyy-mm-dd [code]RIGHT(thedate,4) + "-" + SUBSTRING(thedate,4,2) + "-" + SUBSTRING(thedate,1,2)[/code] as a unicode string (DT_WSTR) then convert that (data conversion) do a date (dt_date). This is what works for me.

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

davidgr144 — Tue, 05 Jan 2010 16:23:18 GMT

First of all, regarding registry settings, noone in our office can edit registry settings, at least through regedit as it has been disabled for use across the organisation.As far as more information:The column I refer to has dates in. There are around 1000 rows of data. The first three rows in the particular column are empty. Then there are a few dates and the pattern sort of repeats itself like that. When the data is previewed in SSIS, the first three rows show as empty for that column but every row with a valid date in shows as "NULL".

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

Peter Schott — Tue, 05 Jan 2010 15:40:02 GMT

Perhaps he has the registry settings for IMEX or similar set differently on his machine? If you can be more specific with the issues, that might help some. My best guess is that your colleague's registry settings are different than yours.

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

davidgr144 — Tue, 05 Jan 2010 15:24:01 GMT

I'm having a similar problem to this but it is localised to my machine only. My colleague wrote a package to import an Excel spreadsheet in SQL Server. It works perfectly on his machine but when we transferred the package/solution in its entirety to my machine, the behaviour described in this forum appeared on mine. Does anyone have any ideas why this might be the case. We have the same software version and all drivers on both our computers are the same.

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

niall.baird — Wed, 21 Oct 2009 16:11:08 GMT

I found the answer - use a conditional split - work out which column can never be null for a valid row, divert all of the rows that do NOT fit that condition in to the remainder of the data flowEasy....innit....dunno why it stumped me for so long!

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

Peter Schott — Wed, 21 Oct 2009 16:04:47 GMT

Raj, If you add that expression, you will indeed not have a NULL in the column, but that doesn't help the case where there was actual data in the column that was lost because the JET driver looked at the column and treated it as a FLOAT instead of a CHAR(255). The best example I have for this is Zip+4 codes mixed with Zip codes in a single column. Excel will read the column, see that the majority are FLOAT and will treat anything with a hyphen as a NULL for purposes of import because it's not a FLOAT. That defeats the purpose.JW, That solution looks okay, but as one person commenting on it noted, it forces everything to a char(255) and you lose any Memo data that might have been stored. If one of your columns is > 255 characters, you lose the data. You also then have to deal with everything that is now being treated as a char(255) and any odd conversions that happen as a result. That in itself shouldn't be too bad, but it is a pain. It's also kind of frustrating because you can't set that option when using the Wizard. You need to code for it. :PAs noted in one of my earlier posts, I deal with this when receiving files from customers that have multiple sheets (making it impractical to try to export and choose a good delimiter), a mixture of data-types, and usually with some Note/Memo data. That leaves me with a lot of pain points when trying to load data directly from Excel. And there are a lot of workarounds, but hopefully people will be aware that there are definitely some areas to watch for when importing Excel using SSIS.

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

$JW — Wed, 21 Oct 2009 15:32:56 GMT

How about this simple solution:http://blog.tylerholmes.com/2007/12/walkthrough-connecting-to-excel-2007.html

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

rajgadiraju — Tue, 16 Jun 2009 10:10:16 GMT

Another way is to add a "Derived Column Transform" and use the expression "?" with a default value that replaces null.Example: ISNULL([testColumn])?0:[testColumn]Raj

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

rajgadiraju — Tue, 16 Jun 2009 10:10:06 GMT

Another way is to add a "Derived Column Transform" and use the expression "?" with a default value that replaces null.Example: ISNULL([testColumn])?0:[testColumn]Raj

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

niall.baird — Tue, 17 Mar 2009 19:34:48 GMT

Hmmm....I see your problem.You could probably create a script task to add the relevant commas if this is an ongoing issue, but that seems a bit like overkill.

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

Peter Schott — Tue, 17 Mar 2009 19:06:10 GMT

We're on different pages. We receive these from customers exporting their own data. The Excel file then contains multiple sheets, some filled out to the very end with data, some not. As an experiment, try creating a simple 3 column Excel sheet. Give each column a header. Fill out 4 rows, but don't fill in the last cell or two for some of the rows. Save as CSV. Open that in a text editor. Unless things have changed, you will not have extra commas indicating an empty column. That means that SSIS doesn't know what to do until it hits the actual EOL character or another comma/delimiter if it expects that first.We can also add in some fun with people typing things like " The person said, "I want to go home later." and then left." in one column. :DI'm not really having major issues with this myself as I've learned a lot of workarounds to the fun that is Excel. My main point in responding to this thread is to help people not get caught as easily by the same things I've seen way too many times. DTS handled things differently, from Unicode conversions to mixed data, to how it handled imperfectly formed CSV files. SSIS is more strict and that's generally a good thing, but it can be really frustrating when DTS "just worked" and SSIS seems to require a lot more hand-holding. :)

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

niall.baird — Tue, 17 Mar 2009 18:52:45 GMT

Hi Peter,If I understand you correctly, you have an excel file filled with a number of worksheets, and you would like to import each worksheet into 1 or more tables inside a database.I have not experienced the problems you mentioned with x number of 'blank' columns not being exported into CSV from Excel, however...If you create a (number of) flat file connections (NOTE - not excel connections), one per worksheet that has been exported to CSV, then create a data flow task, you can set the data type of the 'import' to whatever you like. If you need extra columns, you can (inside the data flow task) set the input of a 'derived column' to the original source (csv) file, and import a NULL or whatever your default value is - or you could talk to your DBA and set a default on the column(s). This should fix the "excel doesn't export blank columns" problem.I'm unsure if I have understood your problem correctly though. If I haven't, perhaps you could PM me and include the table create and a portion of the csv file?

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

Peter Schott — Tue, 17 Mar 2009 18:36:23 GMT

By itself, that's not too bad. However, when you have an Excel file with 15 sheets and need to import all of them, preferably into pre-existing/typed columns, it becomes a little more difficult. The ODBC interface to Excel leaves a lot to be desired.Also, see some of my other issues with Excel and saving out to CSV. If you have empty columns at the end of your data set (not too uncommon), Excel doesn't populate empty columns into your CSV for you. It leaves off those commas and ends the line. Excel can re-import that without too much trouble. SSIS cannot. It reads past the end of the line because the next delimiter it expects is a comma. Once it reaches the last field it expects as end of line based solely on the number of commas, it reads all the way up to the EOL character. That in turn almost always leads to some sort of error indicating that you're trying to put too much data into a column.Solution to that - a "dummy" column for your last column in Excel that is completely filled with data, even if just an integer or an 'x'.Sadly, this is all outside of the control of the SSIS/SQL Team as it has to do almost completely with how Excel and ODBC work together (read - not very well). :P-Peter

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

niall.baird — Tue, 17 Mar 2009 15:57:10 GMT

Why is this so difficult?1. Save excel file as CSV2. Create flat file connection in SSIS3. Click on Advanced tab4. Set the value for the required column to STRING (12)5. Use a data flow task to import it.(If I had access to a web server I could put up some screen captures, but unfortunately, I don't)cheers,niall

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

RJ Kelly — Fri, 23 Jan 2009 07:21:55 GMT

All rather interesting solutions and to some degree advanced. Being a newbie a few years back I never caught on to some of the solutions above, but I did find a quick and easy way to alleviate the issue.I myself have run into several issues moving data from Excel into SQL Server. Most of my hangups have to do with dates. When using dates derived from formulas (ex: creating a calendar table) I have found it easier to create a macro that inserts a single quote in front of the date value and forces the string value of the date into the cell, rather than the serial number of the date. This works well for alpha-numeric values like SSNs ZipCodes, Phone Numbers and such.Here is some code I added to my personal workbook that helps with this. It's crude but I am the only one to use this.[code]Private Sub Workbook_Open()'Reset Right Click MenuApplication.CommandBars("Cell").Reset 'Create Object reference to right click cell properties menu in excel With Application.CommandBars("Cell").Controls With .Add 'Caption for menu .Caption = "Convert Values" 'Reference to macro that will be called .OnAction = ThisWorkbook.Name & "!PasteSpecial" .Tag = "PasteValues" 'Tag reference not needed .BeginGroup = True End WithEnd Sub[/code][code]Sub ConvertVals()Dim rng_Selection As RangeSet rng_Selection = SelectionFor Each Cell In Range(rng_Selection.Address) Cell.Value = "'" & Cell.ValueNextEnd Sub[/code]I usually create my insert/update statements inside of excel so the single quote never shows up, so I do not know if this works well with SSIS. I only have the Express version and I can't really test. I would be willing to bet that it does not affect it since excel never prints a single quote by itself, and primarily uses it to force string values when there is only 1 quote in the beginning.

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

Yellowian — Fri, 21 Nov 2008 15:28:05 GMT

Hello there,I am new with this! I followed the steps and I got below error at the end. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteReader() at ScriptTask_16d19b565ae44ef8858257e2c7d700ba.ScriptMain.Main()

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

Peter Schott — Fri, 03 Oct 2008 09:08:00 GMT

I've just been bitten by this area hard. I had no issues with DTS, but with SSIS the game has been changed completely. I also wanted you to be well aware of this before you were hit by this like I was. (Customer called wondering where all there zip codes went. :P )-Pete

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

weida — Fri, 03 Oct 2008 09:01:13 GMT

Peter: You are absolutely correct! - and very generous of you to let me down in such a face-saving manner. That'll teach me to shoot off a comment without testing.

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

Peter Schott — Fri, 03 Oct 2008 08:37:24 GMT

That lazy method can work a lot of the time, but I know it won't in some cases. Example - US has a postal code system that has two common components - ZIP and ZIP+4. Zip is a 5 digit code. ZIP+4 has a format of #####-####. In Excel, if this column is just left as General, the underlying JET provider reading the sheet interprets this sheet based on what it sees in the column. In this case, that PostalCode column is treated as a FLOAT for import purposes. All of those ZIP+4 values are lost because they can't convert to a FLOAT type, regardless of your target column.Admittedly, I have not tried this in SQL2008's import. If that's changed, great, but the way I understand it is that we can't actually override the way JET works in any easy manner. There are some properties you can set at the driver level, but not through the Wizard. You can read through the table, but IIRC, that results in a "majority wins" type of determination for data types.In any case, Excel just isn't a friendly source for a variety of reasons if you're trying to handle a bunch of mixed data. When you're dealing with purely numeric or character data, it's not too bad. Once you start mixing in data types within a column, you're going to have issues.

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

weida — Fri, 03 Oct 2008 06:43:43 GMT

The article demonstrates some valuable techniques, but for a situation like this, seems overkill. My lazy method (using DTS): use the DTS import wizard, check off Drop and Recreate, and save the DTS package. Open the package, modify the create table statement to fix the datatype for the offending field (zipcode changes to varchar). Save and re-run the package.

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

Peter Schott — Mon, 28 Apr 2008 14:54:37 GMT

Only problem I've noted with exporting to some form of delimited text and then trying to use SSIS is that Excel tries to save space when exporting. If you have a possible 20 columns, but one row doesn't have the last 3 columns filled in, you get no delimiters for those columns. I usually find this out when I get some "too big" or "can't CAST" message for something like an INT field which now has text in it due to the fields getting wrapped.There are some CONNECT tickets open about allowing us to specify whether EOL characters can override the column delimiters, but the behavior won't change for SQL 2005. The best we've gotten so far are people suggesting script tasks to correctly read EOL characters. To make it easier on me, I add a "dummy" column as the last column and populate it with integers all the way to the end of the dataset. I can then export with no missing columns. Maybe Excel 2007 is better about that, but I never had good luck with it prior to 2007.I appreciate the article, though. This is one of the really annoying parts of trying to work with Excel and SSIS. Excel is really common and easy to use so we get it as source data from a lot of our customers. I just wish it played better with SSIS.

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

rkolsky — Mon, 28 Apr 2008 14:26:17 GMT

good post, thanks. I found another way around this similar to the first method you tried. If you save as tab delimited text and then use ssis it works fine (just did this last week). Not automated unfortunately but it's a very easy fix for a quick import.

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

rkolsky — Mon, 28 Apr 2008 14:20:38 GMT

This doesn't always work. From the research I've done excel is "smarter" (dumber) than that. It actually analyzes the first x rows and it's "majority rules" so if the first row (dummy row) is text and the next 6 rows are numbers then numbers it is. Very frustrating.

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

FibRock — Mon, 28 Apr 2008 13:16:02 GMT

I have to agree with David, IMEX=1 is a much easier solution and we use this at where I work with excel for a number of different packages. The only thing I have to add is that setting the registry value to 8 is a little low you may want to try 1000.

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

Igor Zaychik — Mon, 28 Apr 2008 13:07:28 GMT

I've read article and found that F5 does not work to repopulate fields.What is the right key?From other side I used different method.I open another workbook,mark all cells as textand copy the original one as textthrough the Pasta special option on Edit menu.Even if some columns are numeric SQL will recognize themat load.

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

Holly Kilpatrick — Mon, 28 Apr 2008 09:07:29 GMT

I agree that this situation can be very frustrating. I have always used the IMEX=1 solution and found it very useful.Thanks for the script for importing from Excel, I saved that in case it is helpful for something in the future.Holly

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

thejackfy — Mon, 28 Apr 2008 07:42:50 GMT

Simply changing the connection string doesn't work as I've tried before.[quote][b]David Greenberg (4/28/2008)[/b][hr]I found an easier way using steps in this MS KB article on the very same subject. I tried it and it works very well.Solution : Set ConnectionString property of Excel source file as following Provider=Microsoft.Jet.OLEDB.4.0;Data Source=filepath\filename.xls;Extended Properties=”EXCEL 8.0;HDR=YES;IMEX=1“; NOTE: Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric. You must be careful that IMEX=1 not be used indiscriminately. This is IMPORT mode, so the results may be unpredictable if you try to do appends or updates of data in this mode.Reference : http://support.microsoft.com/kb/194124/en-us[/quote]

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

JB-117464 — Mon, 28 Apr 2008 07:41:25 GMT

I have heard several 'fixes' for this problem. From what I have read it appears to be a major design flaw in the excel provider which covers all versions including 2007.Essentially it is down to these 2 registry keys (not sure which computer(s) these are read from; source, destination or SSIS run host)HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel\TypeGuessRows=8HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows=8Excel reads the first 8 lines and determines maximum length and data type from that. So if you haveheader12345678wordthe 9th line containing "word" will be come null because it isnt a float data type. It will also truncate all strings to either 255 or the longest in the first 8 lines if they are longer than 255. Unicode can also present some challenges!You can alter these registry keys to 16384 but that doesnt eliminate the problem with most excel files particularly since you can get over 1 million rows in 2007.The best way i have found is adding a fake first line which forces the right length and datatype e.g. A12345678....... and then skip this line in your ssis package. This is pretty similar to Ying Zhang suggest of leaving the header in. Unfortunatley the header is not guaranteed to be long enough to prevent truncation of long text fields (over 255).The only true way to get round this is to refuse to accept data in excel! If only:)

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

David Greenberg-358875 — Mon, 28 Apr 2008 07:39:07 GMT

I found an easier way using steps in this MS KB article on the very same subject. I tried it and it works very well.Solution : Set ConnectionString property of Excel source file as following Provider=Microsoft.Jet.OLEDB.4.0;Data Source=filepath\filename.xls;Extended Properties=”EXCEL 8.0;HDR=YES;IMEX=1“; NOTE: Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric. You must be careful that IMEX=1 not be used indiscriminately. This is IMPORT mode, so the results may be unpredictable if you try to do appends or updates of data in this mode.Reference : http://support.microsoft.com/kb/194124/en-us

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

Steve-524674 — Mon, 28 Apr 2008 07:37:20 GMT

Call me crazy, but I think I would do a global change on the column to eliminate the dash.

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

marklegosz — Mon, 28 Apr 2008 07:08:18 GMT

I may be missing something, but couldn't you just modify the select clause on the spreadsheet and manually cast to the correct datatype?

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

Jack Corbett — Mon, 28 Apr 2008 06:58:26 GMT

I thought the article was well-written and easy to follow. I do agree with others that loading the first row (header) as data in the source would be the simplest solution, yet I understand why you would miss this initially.

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

thejackfy — Mon, 28 Apr 2008 06:12:09 GMT

[b]Much easier way is:[/b]Load the Excel file with [b]First Row as Header[/b] off even it contains the header. This can ensure all the fields are read in text format. And then add a Conditional Split to skip the first row.

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

SuperDBA-207096 — Mon, 28 Apr 2008 06:03:17 GMT

I've had a similar problem - ended up resolving the problem as follows:1) save the data as csv2) create the table with the correct datatypes before importing.That worked for me, and was alot simpler; although I believe your solution does help demonstrate some of the capabilties of SSIS.Mark

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

Pieter-423357 — Mon, 28 Apr 2008 05:49:03 GMT

One could just save the Excel as a CSV file, load the file via BULK INSERT or OPENROWSET and be done. Why go through hoops on and develop SSIS package? The portability and predictability of SSIS isn't that great anyway.my 2 cents...

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

p.wiggins — Mon, 28 Apr 2008 03:34:20 GMT

Hi,This is an old problem with importing Excel, we just put in a dummy first row with data in the form rewuired for each column, e.g. TEXT for text, 1234 for number, etc. , then delete the first record imported. tends to solve the problem for us.Pete Wiggins

RE: How to Handle NULL Values Loaded by SSIS from Excel Files

Anipaul — Mon, 28 Apr 2008 00:09:15 GMT

Useful one. Good for the new bies in SSIS. Also the links provided at the end are useful. :)

How to Handle NULL Values Loaded by SSIS from Excel Files

yzhang — Sun, 27 Apr 2008 08:16:12 GMT

Comments posted to this topic are about the item [B]How to Handle NULL Values Loaded by SSIS from Excel Files[/B]