October 29, 2013 at 8:51 am
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!
October 29, 2013 at 8:54 am
Use a derived column to replace the NULL values with 0.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 9, 2013 at 12:57 pm
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
December 11, 2013 at 9:50 am
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.
April 12, 2014 at 11:45 pm
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
Change is inevitable... Change for the better is not.
April 29, 2014 at 7:03 am
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.
April 29, 2014 at 7:38 am
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.
April 30, 2014 at 10:00 am
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.
April 30, 2014 at 10:53 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply