locked
no scientific notation in csv file RRS feed

  • Question

  • Hi, All

    I have a package is extracting data from DB to CSV file.

    And I made up a column like '48484848484848484', when I load this column into CSV file,

    it shows me scientific notation along with the data.

    How can I get rid of the scientific notation ?

    I've tried data conversion, derived column. Nothing works.

    This really makes my frustrated.

    Thank you in advance.

    Monday, February 10, 2014 4:29 PM

Answers

  • This is not a SQL Server SSIS problem.  This is the "auto typing" function in Excel which is causing this problem.  You have no control over how Excel decides to format your number on when you open the CSV file.

    You can export the data as a text field, but putting a single quote in front of the number, '12232234343555.  However, then the number is really a text field in Excel and you will not be able to do calculations on it.

    Please see:

    http://office.microsoft.com/en-us/excel-help/import-or-export-text-txt-or-csv-files-HP010099725.aspx

    • Marked as answer by Leading120 Wednesday, February 12, 2014 2:05 PM
    Wednesday, February 12, 2014 1:55 PM

All replies

  • I assume you used data conversion to decimal or integer, right?

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Monday, February 10, 2014 4:42 PM
  • I assume you used data conversion to decimal or integer, right?

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    No, I used data conversion to string[DT_STR]
    Monday, February 10, 2014 4:44 PM
  • Hi,

    Are you opening the CSV file with Excel?

    If so, what happens if you open your CSV file with Notepad?


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Monday, February 10, 2014 4:45 PM
  • Try converting the column to long using the data conversion and make sure that the flat file connection manager has long as its data type for that column.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Monday, February 10, 2014 4:47 PM
  • Hi,

    Are you opening the CSV file with Excel?

    If so, what happens if you open your CSV file with Notepad?


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Yes, I open it by Excel.

    In Notepad, the data is fine, no scientific notation.

    But my boss just wants to open it by double click the CSV file.

    Monday, February 10, 2014 4:50 PM
  • Hi,

    In that case, the SQL export process is fine.

    It's just Excel suggesting a scientific notation where it's not needed.

    You can get rid of the scientific notation by forcing your "long" numeric value into a string.

    For example

    "A001","Item code", "123376265892759026"

    instead of

    "A001", "Item code", 123376265892759026


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Monday, February 10, 2014 4:55 PM
  • Right click on the column, go to format cell, select Custom on the left hand side.  From the menu, select the number "0".  In the box above the menu, enter as many zeros as you have places for that number.  I.e. if it has 13 places, enter thirteen zeros.  

    I'm not sure if this is the "right" way to do this, but it will work.

    Monday, February 10, 2014 4:55 PM
  • Right click on the column, go to format cell, select Custom on the left hand side.  From the menu, select the number "0".  In the box above the menu, enter as many zeros as you have places for that number.  I.e. if it has 13 places, enter thirteen zeros.  

    I'm not sure if this is the "right" way to do this, but it will work.

    This is not what I am asking. You can not tell your boss to convert the column by themselfs.


    Monday, February 10, 2014 5:09 PM
  • Hi,

    In that case, the SQL export process is fine.

    It's just Excel suggesting a scientific notation where it's not needed.

    You can get rid of the scientific notation by forcing your "long" numeric value into a string.

    For example

    "A001","Item code", "123376265892759026"

    instead of

    "A001", "Item code", 123376265892759026


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    My data souce is like:

    Select '1234567891123' as column1,

                                 Id  as column2,

                          Name as column3,

    From Table1

    I think I already give column1 as a string. And I also tried to cast column1 to nvarchar or varchar or decimal or numeric, nothing works.

    Monday, February 10, 2014 5:12 PM
  • Hi,

    What about this?

    Select '"1234567891123"' as column1 ... (add double quotes)


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Monday, February 10, 2014 5:14 PM
  • Try converting the column to long using the data conversion and make sure that the flat file connection manager has long as its data type for that column.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    what do you mean long data type ?

    The column has 13 numbers, like '1234567891123'.    I tried to data conversion to two-byte signed integer, it is always give me error.

    after I set RD_IgnoreFailure, it went throught, but that column didn't insert into csv file.

    Monday, February 10, 2014 5:14 PM
  • Hi,

    What about this?

    Select '"1234567891123"' as column1 ... (add double quotes)


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    I tried this, not working.

    the data still has scientific notation.

    Monday, February 10, 2014 5:17 PM
  • Hi,

    I could open my CSV file with no scientific notation by inserting TEXT function in the CSV itself

    Try with the following CSV

    "A0001",12345,"=TEXT(111156789012,""0"")"
    "A0002",22222,"=TEXT(222267890123,""0"")"
    "A0003",55555,"=TEXT(333378901234,""0"")"
    "A0004",98889,"=TEXT(343434343434,""0"")"


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Monday, February 10, 2014 5:28 PM
  • what do you mean long data type ?


    eight-byte signed integer

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Monday, February 10, 2014 5:30 PM
  • Hi,

    I could open my CSV file with no scientific notation by inserting TEXT function in the CSV itself

    Try with the following CSV

    "A0001",12345,"=TEXT(111156789012,""0"")"
    "A0002",22222,"=TEXT(222267890123,""0"")"
    "A0003",55555,"=TEXT(333378901234,""0"")"
    "A0004",98889,"=TEXT(343434343434,""0"")"


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    I just don't want to touch the CSV file. I mean you can tell your customer to open the csv file and do some modification, right ?

    There must be some solutions by SSIS tools

    Wednesday, February 12, 2014 1:42 PM
  • Hi,

    SSIS works fine, it's generating the proper CSV.

    Problem is Excel, which capriciously displays a bigint with scientific notation


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Wednesday, February 12, 2014 1:47 PM
  • This is not a SQL Server SSIS problem.  This is the "auto typing" function in Excel which is causing this problem.  You have no control over how Excel decides to format your number on when you open the CSV file.

    You can export the data as a text field, but putting a single quote in front of the number, '12232234343555.  However, then the number is really a text field in Excel and you will not be able to do calculations on it.

    Please see:

    http://office.microsoft.com/en-us/excel-help/import-or-export-text-txt-or-csv-files-HP010099725.aspx

    • Marked as answer by Leading120 Wednesday, February 12, 2014 2:05 PM
    Wednesday, February 12, 2014 1:55 PM
  • what do you mean long data type ?


    eight-byte signed integer

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    I tried, but no working. 
    Wednesday, February 12, 2014 2:03 PM
  • Now I got what you are talking about, Thank you so much.
    Wednesday, February 12, 2014 2:05 PM
  • Thank you so much !!!
    Wednesday, February 12, 2014 2:06 PM
  • Can I know how this was resolved please? I have same issue. I am trying to output SSIS data into .CSV file. First column has numeric and string data. Numbers are being converted to scientific notation when file is generated.

    Thanks,


    • Edited by ManikYOKO Wednesday, January 20, 2016 11:54 PM
    Wednesday, January 20, 2016 11:52 PM