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

and x is NULL <> and x = 'NULL' Expand / Collapse
Author
Message
Posted Thursday, August 22, 2013 1:26 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:34 AM
Points: 488, Visits: 1,992
Just when I thought this was about the silliest thing I'd seen...

I had someone using NULL as a string in an Excel file that I imported, which made some count queries go b-a-n-a-n-a-s.

Some days I think those Amish are on to something.
Post #1487483
Posted Thursday, August 22, 2013 1:46 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:47 AM
Points: 13,542, Visits: 11,345
You'd be suprised what you can find these days



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 #1487497
Posted Thursday, August 22, 2013 2:03 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:34 AM
Points: 488, Visits: 1,992
Koen Verbeeck (8/22/2013)
You'd be suprised what you can find these days


In the same file something was doing MATH on a phone number column during import.

So 212-555-5555

or 212-555-5555 / 212-555-5556

Were being treated as expressions. They were saved as General format in Excel. When I changed the format to Text, they went in fine.
Post #1487508
Posted Thursday, August 22, 2013 4:22 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 17, 2014 2:27 AM
Points: 418, Visits: 652
Love it

Reminds me of one of my favourite jokes:

Q: Why does Excel get Christmas and Halloween confused?

A: OCT31 = DEC25 (actually OCT2DEC(31) = 25 but it doesn't sound so good when you write it like that.


The general rule with Excel always seems to be don't trust it unless Excel created it in the first place.

I have a CSV file I need to bring into SQL occasionally and it contains product codes. The two which always confuse Excel are "012" which it sees as 12 (Apparently it knows better than me that numbers don't start with a leading zero so it kindly removes them to save me the trouble) and bizarrely "2E2" which it assumes I meant two Exponent two so saves as 200 2E2 couldn't possibly be a string, it is a valid numerical notation even though the other 3000 cells in the column are strings.




Post #1487569
Posted Thursday, August 22, 2013 5:28 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:34 AM
Points: 488, Visits: 1,992
aaron.reese (8/22/2013)
Love it

Reminds me of one of my favourite jokes:

Q: Why does Excel get Christmas and Halloween confused?

A: OCT31 = DEC25 (actually OCT2DEC(31) = 25 but it doesn't sound so good when you write it like that.


The general rule with Excel always seems to be don't trust it unless Excel created it in the first place.

I have a CSV file I need to bring into SQL occasionally and it contains product codes. The two which always confuse Excel are "012" which it sees as 12 (Apparently it knows better than me that numbers don't start with a leading zero so it kindly removes them to save me the trouble) and bizarrely "2E2" which it assumes I meant two Exponent two so saves as 200 2E2 couldn't possibly be a string, it is a valid numerical notation even though the other 3000 cells in the column are strings.


It does that a lot with zip codes for me. It also does a bang up job of converting any foreign number that starts with 0 into exponential notation, from where there is no return. It's especially fond of doing that to UK numbers, for some reason.

Many people, including my boss, seem to think that this will fix it:

update table set columntofix= convert(numeric(38,0),cast(columntofix as float))

But it usually just gives me a close but no cigar number that has the last three digits rounded up. So, if a phone number ends in 9679, it will be 9700.
Post #1487589
Posted Thursday, August 22, 2013 5:59 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:45 PM
Points: 1,080, Visits: 3,170
I personally loathe the one where something like
7/21
gets converted to
Jul-21
Post #1487596
Posted Thursday, August 22, 2013 8:42 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:53 AM
Points: 3,422, Visits: 5,368
I hate it when OPENROWSET imports a value of ' (empty string in the Excel cell) as an empty string and thus thinks there's extra rows in the input set.

Have to teach people how to properly delete rows of spreadsheets you import from.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1487618
Posted Thursday, August 22, 2013 9:31 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:34 AM
Points: 488, Visits: 1,992
dwain.c (8/22/2013)
I hate it when OPENROWSET imports a value of ' (empty string in the Excel cell) as an empty string and thus thinks there's extra rows in the input set.

Have to teach people how to properly delete rows of spreadsheets you import from.


I work with one client who puts a copyright notice at the end of his data pulls, so the six lines of that and 2 blank rows on either end of it get brought in.

And an insurance company with a large elderly subscriber base in Western PA. So elderly that there is sometimes a 00 for their birth month or day, which causes all sorts of arithmetic errors when trying to calculate current age. I have no idea why or how this is allowed.
Post #1487624
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse