none
Long number in CSV file appearing in scientific notation by default

    Question

  • Hi,

     

    How can I stop a long number in a CSV file that is opened in excel from appearing in scientific notation by default?

    eg.

     

    "hello","778002405501 ", "yes"


    becomes:

     

    hello | 7.78002E+11 | yes

     

    I have tried wrapping the data in quotes in the csv but to no avail.

     

    Thanks in advance,

    Alistair

    Thursday, November 25, 2010 12:47 AM

Answers

  • I think the best way is to read the file as text by renaming the file extension to ".txt".  Then when you open the file a dialog window will come up.  Select Delimited file and go through each window.  One the 2nd window check only the comma for the delimiter column.  Now here is the trick.  the third window you can select each field and specify if you want the column to be read in as text or number.  Choose Text for the field with large numbers.

    You can also use from the Data menu Import External Data which will bring up the same menu as above with a CSV file.  the problem with excel is the normal opening of a CSV file bypasses the Dialog Box to allow you to select the column type as Text.


    jdweng
    • Marked as answer by Bessie Zhao Thursday, December 02, 2010 10:20 AM
    Thursday, November 25, 2010 10:44 AM
  • After opening the CSV file format the columns number format to text, then widen the columns.

    This can be done with ws.UsedRange.EntireColumn.AutoFit()

    Where ws is a reference to the worksheet in question.

    Hope you find this helpful.

    • Proposed as answer by Scott Gall Friday, November 26, 2010 4:19 AM
    • Marked as answer by Bessie Zhao Thursday, December 02, 2010 10:20 AM
    Friday, November 26, 2010 4:18 AM

All replies

  • I think the best way is to read the file as text by renaming the file extension to ".txt".  Then when you open the file a dialog window will come up.  Select Delimited file and go through each window.  One the 2nd window check only the comma for the delimiter column.  Now here is the trick.  the third window you can select each field and specify if you want the column to be read in as text or number.  Choose Text for the field with large numbers.

    You can also use from the Data menu Import External Data which will bring up the same menu as above with a CSV file.  the problem with excel is the normal opening of a CSV file bypasses the Dialog Box to allow you to select the column type as Text.


    jdweng
    • Marked as answer by Bessie Zhao Thursday, December 02, 2010 10:20 AM
    Thursday, November 25, 2010 10:44 AM
  • After opening the CSV file format the columns number format to text, then widen the columns.

    This can be done with ws.UsedRange.EntireColumn.AutoFit()

    Where ws is a reference to the worksheet in question.

    Hope you find this helpful.

    • Proposed as answer by Scott Gall Friday, November 26, 2010 4:19 AM
    • Marked as answer by Bessie Zhao Thursday, December 02, 2010 10:20 AM
    Friday, November 26, 2010 4:18 AM
  • Joel,we have a separate application that creates the .csv files, and don't have a choice of using .txt files instead.

    The users double-click .csv file and it launches Excel and opens the doc.  When the user views the the columns the longer numbers all display in scientific notation.  The users don't want to have to re-format the doc each time they go in.

    Is there anything we can embed inside the .csv file in text format to set the column width to be larger than the default 8.43?

    Monday, January 17, 2011 10:00 PM
  • I CSV file contains only text (COMMA SEPERATED VALUES) and no formating.  You can't include anything into the CSV file to format to automaticaly set the column widths.  You can create a workbook with a macro which will automatically open the CSV file and set the formating to the correct style.

     

    You can have your application put the CVS file in the same folder as a macro enabled workbook so the users would open the workbook instead of the CSV file.  The workbook can contain a workbook open macro that would allow the user to select the CSV file to import.

    What application are you using to create the CSV?  Maybe that can be modified. 


    jdweng
    Monday, January 17, 2011 11:12 PM
  • Joel,  one of our other developers embeds "=TEXT(1234567890,0)" including the quotes inside the .csv file and it appears to leave the number in standard format.  The column width doesn't change, so the user still has to expand the column width to see it completely.
    Tuesday, January 18, 2011 2:34 PM
  • the statement has an error.  The parameter following the comma should be a text string indicating th eformat.  Since the string already is in double quotes you must put two sets of double quotes in the statement like this

    "=TEXT(1234567890,""0"")"


    jdweng
    Tuesday, January 18, 2011 4:14 PM
  • Joel, I'm starting to be concerned about your advice.

    First you stated we could not embed any functionality inside the .csv, now you're stating that yes we can, but my format is incorrect.

    The statement I listed is precisely correct, and is not in error - when I open excel it displays the number in standard form inside the 8.43 column width.  Below is from the raw .csv file itself that produces the correct excel result -

    Loan Number,Type,Vendor,Ord By,Reason,Valid,Done,Order #,Text #,Sent
    "=TEXT(646750736,0)",FHA,AAAIPP,VIRE,,Pass,No,,"=TEXT(,0)",No
    "=TEXT(703437862,0)",FHA,PATC,MCMI,P,Pass,Yes,91003050,"=TEXT(200003613,0)",No

    What I'm still after is whether there's an embed I can use to autofit the column width on .csv open to the largest cell in that column - that would complete my issue.  Can you assist there please?

    Tuesday, January 18, 2011 4:32 PM
  • II'm sorry that I confused you.  In this case FORMAT means two diffferent things

    1. The format of the cell which can be the background color yellow
    2. The Text formula the 2nd parameter is also refered to as the format.  In this case youi are not changing the cell format but instead the format of the text string displayed.

    When you open a CSV by double clicking on the file name in a window explorer all the cells on the worksheet default initially to GENERAL FORMAT. Excel will test each file to see if it recognizes the data in the file as one of the standard formats.  For example if you have a DATE in the CSV file excel will automatically change the format of the cell to Date.  You can't specifically upload any formating information to force a cell to a particular format like specifying the column width.

    Excel will be able to recognize a formula in the CSV file since all formula will start with an equal sign.

    The formula you are uploading below is not correct

    =TEXT(646750736,0)

    It should be

    =TEXT(646750736,"0")  which doesn't make any sense.

    Usually people use something like this

    =TEXT(1234567890,"#0.00")

     

    So you CSV file should look like this

    "=TEXT(646750736,""#0.00"")",FHA,AAAIPP,VIRE,,Pass,No, .......

    Notice I put two sets of double quotes around ""#0.00""

     

     


    jdweng
    Tuesday, January 18, 2011 6:03 PM
  • You can change the extension from ".csv" to ".xls" and use table to form the data and use style=”mso-number-format:\@;”
    Please read the sample code below in Classic ASP:

    You can also read in my blog http://sarbashish.wordpress.com/2012/11/30/export-to-excel-how-to-prevent-long-numbers-from-scientific-notation/

    <%

    Response.Clear
    Response.CacheControl = “no-cache”
    Response.AddHeader “Pragma”, “no-cache”
    Response.Expires = -1
    Response.ContentType = “application/vnd.ms-excel”
    Dim FileName
    FileName = “TestDB Lookup-” & month(now)&”-”&day(now)&”-”&year(now)&”.xls”
    Response.AddHeader “Content-Disposition”, “inline;filename=” & FileName
    %>
    <html xmlns:o=”urn:schemas-microsoft-com:office:office” xmlns:x=”urn:schemas-microsoft-com:office:excel” xmlns=”http://www.w3.org/TR/REC-html40″;>
    <head>
    <meta http-equiv=Content-Type content=”text/html; charset=UTF-8″>
    <!–[if gte mso 9]>
    <xml>
    <x:ExcelWorkbook>
    <x:ExcelWorksheet>
    <x:WorksheetOptions>
    <x:DisplayGridlines/>
    </x:WorksheetOptions>
    </x:ExcelWorksheet>
    </x:ExcelWorksheets>
    </x:ExcelWorkbook>
    </xml>
    <![endif]–>
    </head>
    <body>
    <table border=”0″>
    <tr>
    <td>ID</td>
    <td>Name</td>
    </tr>

    <tr>
    <td style=”mso-number-format:\@;”>01234567890123456567678788989909000030</td>
    <td>Sarbashish B</td>
    </tr>
    </table>
    </body>
    </html>


    Sarbashish Bhattacharjee http://sarbashish.wordpress.com

    Friday, November 30, 2012 1:57 PM