Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Null instead of 0 Expand / Collapse
Posted Tuesday, October 29, 2013 8:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 12:01 PM
Points: 8, Visits: 42
I have a excel sheet with some data and blank columns. I have a ssis package using to import data from excel to sql table. For blank excel columns it is importing as null instead i want to show them as '0'. If data comes in it should update the data.
please help

Post #1509398
Posted Tuesday, October 29, 2013 8:54 AM



Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 12,206, Visits: 9,168
Use a derived column to replace the NULL values with 0.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1509400
Posted Monday, December 09, 2013 12:57 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:28 AM
Points: 831, Visits: 155

I think for the NULL values you can set the default value for the column to 0 in the column definition, so when the data is not provided, it will be set at the default value

Aram Koukia:
Post #1521278
Posted Wednesday, December 11, 2013 9:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:49 PM
Points: 366, Visits: 577
It will do. A blank column inicates an unknown value - the equivalent of NULL. One of the problems when dealing with Excel is that the cell value and the displayed value are not always the same - if may be that the cell is custom formatted #.00;-#.00;"Zero" you will see text in the display when the actual value stored is 0.

Excel and SSIS make uneasy bedfellows and I would strongly suggest that you try to get your data from source if possible rather than a user-modified spreadsheet. At the very least get the thing exported as XML or CSV.

Some of the problems I have run in to with Excel

Changed file extensions - users don't see any difference between .xls, .xlsx and .xlsm but SSIS does
32bit on development or source server and 64bit architecture on deployment server
Users protecting part of a workbook causes the xlsx to be encrypted which means that SSIS can't read it - I spent two days on this one!
Users are notorious for re-ordering or renaming sheets, ranges, changing cell formatting etc. You normally have very litte control over the format of the workbook.

If Excel is simply being used as a transport mechanism, then try to get access to the original source.

Post #1521967
Posted Saturday, April 12, 2014 11:45 PM



Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 35,955, Visits: 30,246
I know this is an old post and that the subject is controversial, but a blank means you know it is blank. NULL actually isn't a value. It's a condition. It means that you don't know what the value is.

I also think it's a problem that Excel has no default method of telling you that a cell is blank or that it is empty, which shouldn't be confused with NULL because empty cells are known to be empty whereas a NULL would mean that you don't know if the cell has a value (even if the value is "blank" as in spaces) or is truly empty.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1561264
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse