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
Author
Message
Posted Tuesday, October 29, 2013 8:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:48 AM
Points: 12, Visits: 63
Friends,
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

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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:20 PM
Points: 13,252, Visits: 10,133
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 9, 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: Wednesday, April 30, 2014 10:27 AM
Points: 930, Visits: 160

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: http://www.koukia.ca
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: 2 days ago @ 4:26 PM
Points: 386, Visits: 622
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


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:54 PM
Points: 36,722, Visits: 31,171
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."

(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
Posted Tuesday, April 29, 2014 7:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:53 AM
Points: 356, Visits: 2,241
Jeff Moden (4/12/2014)
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.


Ok I know this isn't really an active topic but I prefer to think that NULL means the cell or column has no value, rather than not knowing what the value is. For instance, say you left join one table to another, and in the other table you list a column. When there is no match in that other table on the column you are left joining, the arbitratry column in the other table you are trying to list is NULL, and its not that you don't KNOW if there is a value, its rather that you are SURE there is NO value.

If on the other hand, we describe NULL has "we just don't know", this could casually imply that arbitrarily replacing a NULL'ed column with random information constitutes a valid operation and that all programs would be required to provide correct answers even when presented with random input. Ie., "we just don't know" very easily morphs into "it could be anything", whereas I like the certainty of "there is no value stored in this column of this row."

I absolutely acknowlege in advance the citations out there for the "we just don't know" business, but I'm just posting an opposing viewpoint. I also acknowledge that one of the reasons that a column can be null is that we don't know what it is, but then this precludes app developers from using a much more reasonable "unknown" selection which in this case now codifies (and stores) an indicator that we really for real don't know what the value is.

I really like Hugo Kornelis' take on the topic:

And saving the best (read: most controversial) for the last, NULL is also definitely not meant to signify “unknown”. Again, a NULL in a table might result from the value being unknown at data entry time (e.g., when we forget to ask a customer for his or her birthday), but there might be other reasons as well. Unfortunately, many text books insist on explaining the behaviour of NULL in expressions by describing NULL as unknown, rather than missing or absent, causing this misconception about NULL to be the most widespread and the hardest to combat.


http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/06/null-ndash-the-database-rsquo-s-black-hole.aspx
Post #1565897
Posted Tuesday, April 29, 2014 7:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:49 AM
Points: 7,047, Visits: 6,790
Jeff Moden (4/12/2014)
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.


It's shame that this cannot be applied to the date data types
(And assuming 1900-01-01 is no date is an anathema)



Far away is close at hand in the images of elsewhere.

Anon.

Post #1565921
Posted Wednesday, April 30, 2014 10:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, May 4, 2014 10:21 PM
Points: 202, Visits: 307
Technically, if you don't know the value, you don't have a complete representation of the entity.

If you assume a value, you're basically saying that the current "measure" here is 0 - in other words you have a measure, and at this time, its 0.

Yes I know this is technical. But to understand this is to "think like the computer thinks".

Now, if you don't have a complete picture of an entity, what does that do to "selectivity value"? It goes down, right? I mean, to the computer, you have high value targets that maintain complete representations. Selectivity value of a target the computer sees as "incomplete" goes down. Not such a big deal if you don't need high degrees of selectivity for that value (you won't use it in a where clause or a join, its used in aggregation only).

A zero - even if its proven inaccurate later, is simply "your understanding" of the point in space-time of the measure. If you're applying good design rules, in either case of an OLTP or an OLAP model, you should be able to deal with this.

Of course this precludes "pre-processing" of things like the result set of an expensive left outer join - where you know you're going to have NULL conditions in the data - its unavoidable. However even there, you may have to carefully consider what makes sense for that condition. 0 or null? Will I have to use it in a where clause or a join? Generally that's how to look at it...

Yes I know this is a hot-button topic and there is a lot of strong feelings. But when you start going in the direction of massive amounts of data stored in SQL Server, you have to consider these things carefully.

Discuss it with your team / manager. Find out what makes sense. If you have a "null", is it an accurate depiction of that measure or entity? In other words, does it truly exist? Is that attribute necessary for the existence? If not, is it really an attribute?

This is where we cross the boundary of human fuzzy logic to computer hard logic. The computer at its heart looks at ON or OFF. It doesn't consider "maybe on but it could be off". Humans, different story.



Post #1566477
Posted Wednesday, April 30, 2014 10:53 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:59 PM
Points: 23,000, Visits: 31,482
Aram Koukia (12/9/2013)

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


I do know this is an old post but if you have a column that is a mix of values and nulls, a null value will not be replaced by a default value provided in a default constraint.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1566505
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse