Post reply

Import Numbers with '-' or scientific notation

  • June 11, 2014 at 9:01 pm

    #396899

    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

    #1721096

    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?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • June 12, 2014 at 9:25 am

    #1721117

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply