June 11, 2014 at 9:01 pm
Hi I'm trying to import and create a table from an excel file like the one I have attached.
I have two problems that I'm running into, one is when I import the file as an .xlsx with the import wizard it automatically sets the "Field1" to a float and won't let me change it in the "edit mappings" dialogue box. It then drops the records that have a dash like 389-1145.
If I instead save the file as a .csv and import it with the wizard, it will import the field as a varchar, but the entries which are in scientific notation like 1.1e+11, will be imported as simply '1.1e+11' instead of 110000150192.
I'm sure I'm not the first guy to run into this issue, so any tips are greatly appreciated.
June 12, 2014 at 8:43 am
What's the data type on the table for those values? string or numeric?
Have you tried formatting the columns correctly before converting them to csv?
June 12, 2014 at 9:25 am
To be safe, I make them all "Text".
Select the whole spreadsheet (if it's not too large) or each column separately (if it's very large), do "Format Cells...", and on "Number" tab, "Category:", choose "Text".
Once loaded, you can covert columns to the specific data type(s) you need.
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".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply