SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Null instead of 0


Null instead of 0

Author
Message
scuby.me
scuby.me
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 84
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!
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63680 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Aram Koukia
Aram Koukia
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1103 Visits: 162
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
aaron.reese
aaron.reese
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2471 Visits: 907
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218751 Visits: 41998
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. :-P

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
patrickmcginnis59 10839
patrickmcginnis59 10839
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4412 Visits: 5923
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. :-P


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

to properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
David Burrows
David Burrows
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16824 Visits: 10127
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 Crazy
(And assuming 1900-01-01 is no date is an anathema)


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

Anon.


Jeremy Brown
Jeremy Brown
SSC Eights!
SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)

Group: General Forum Members
Points: 851 Visits: 313
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.



Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96627 Visits: 38988
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.

Cool
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search