July 16, 2015 at 11:40 am
Put double quotes around the column in the SELECT list:
SELECT ..., '"' + column_with_leading_zeros + '"' AS column_name, ...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 16, 2015 at 3:18 pm
Luis Cazares (7/16/2015)
If you can see the leading zeros in the file using notepad or something similar, that might be an excel problem. Be sure to define that column as string when opening the file.
+1
with no other info - it's likely Excel. Excel likes to "guess" what the data types are, and will treat and format numeric looking content as numbers (and thus drop leading zeroes).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 16, 2015 at 3:24 pm
Matt Miller (#4) (7/16/2015)
Luis Cazares (7/16/2015)
If you can see the leading zeros in the file using notepad or something similar, that might be an excel problem. Be sure to define that column as string when opening the file.+1
with no other info - it's likely Excel. Excel likes to "guess" what the data types are, and will treat and format numeric looking content as numbers (and thus drop leading zeroes).
Actually, Excel uses the first 8 rows to decide what data type the column is, so if you have all pure numbers, even with leading zeroes, you will have to format the column as text ahead of time, or include double quotes both leading and following the value.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 16, 2015 at 8:27 pm
sgmunson (7/16/2015)
Matt Miller (#4) (7/16/2015)
Luis Cazares (7/16/2015)
If you can see the leading zeros in the file using notepad or something similar, that might be an excel problem. Be sure to define that column as string when opening the file.+1
with no other info - it's likely Excel. Excel likes to "guess" what the data types are, and will treat and format numeric looking content as numbers (and thus drop leading zeroes).
Actually, Excel uses the first 8 rows to decide what data type the column is, so if you have all pure numbers, even with leading zeroes, you will have to format the column as text ahead of time, or include double quotes both leading and following the value.
Even with looking at the first 8 rows - it's still a guess albeit an educated one. As in - it is presuming to know that I didn't in fact intend to used that element containing only digits as an actual number and not some ID, say a SSN or FEIN.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 16, 2015 at 8:46 pm
Matt Miller (#4) (7/16/2015)
sgmunson (7/16/2015)
Matt Miller (#4) (7/16/2015)
Luis Cazares (7/16/2015)
If you can see the leading zeros in the file using notepad or something similar, that might be an excel problem. Be sure to define that column as string when opening the file.+1
with no other info - it's likely Excel. Excel likes to "guess" what the data types are, and will treat and format numeric looking content as numbers (and thus drop leading zeroes).
Actually, Excel uses the first 8 rows to decide what data type the column is, so if you have all pure numbers, even with leading zeroes, you will have to format the column as text ahead of time, or include double quotes both leading and following the value.
Even with looking at the first 8 rows - it's still a guess albeit an educated one. As in - it is presuming to know that I didn't in fact intend to used that element containing only digits as an actual number and not some ID, say a SSN or FEIN.
This is known behavior, and it really should be better, but for better or worse, that's the way it works. If I recall correctly, that number of lines might be adjustable, ... my recall on that is a bit fuzzy. Google it and you'll probably find something on it. I thought I remembered seeing posts here in the forums about this too...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply