16-digit number, scientific notation, and saving as csv file

[squidmark]

I have a file with a column full of 16-digit numbers. I get this file as a csv file, and when I open it up, those 16-digit numbers are shown using scientific notation. I can change the formatting to get the numbers to display properly, but then I have to save the file as a csv file again. When I do that, I lose the new formatting and the numbers go back to scientific notation.

I need this to be saved as a csv file for importing into our system. But it can't import using the scientific notation numbering. How can I save the file as a csv file while keeping all 16 digits as, you know . . . . digits?

Man, this is frustrating.

 

[shg]

Excel will only retain 15 significant digits, so you're losing precision in the conversion (the last digits are all zero).

The only way I know to do this is to change the extension to .txt and open the file using the text import wizard, specifying those fields as Text. Or do the same thing using VBA.

 

[ttt123]

Here is how to do it in VBA with as little looping as possible. Note I change the number format of the cells when reading to Text so as to preserve all of the numerical precision.

Public Sub ReadCSV(strFile As String)
    Application.ScreenUpdating = False
    Dim oFSO As New FileSystemObject, oFS As TextStream
    Dim strLine As String, aLine As Variant, nRow As Long, nColumn As Long
    
    nRow = 1
    nColumn = 1
   
    Set oFS = oFSO.OpenTextFile(strFile)
    Do Until oFS.AtEndOfStream
        strLine = oFS.ReadLine
        aLine = Split(strLine, ",")
        With Range(Cells(nRow, nColumn), Cells(nRow, nColumn + UBound(aLine)))
            .NumberFormat = "@"
            .Value = aLine
        End With
        nRow = nRow + 1
    Loop
    oFS.Close
    Application.ScreenUpdating = True
End Sub

Public Sub SaveCSV(ByVal strFile As String)
    Dim oFSO As New FileSystemObject, oFS As TextStream
    Set oFS = oFSO.OpenTextFile(strFile, ForWriting, True)
   
    Dim i As Long, lastRow As Long, lastColumn As Long, strLine As String, aTemp As Variant
    lastRow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
    lastColumn = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column - 1
    For i = 1 To lastRow
        aTemp = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(Range(Cells(i, 1), Cells(i, lastColumn))))
        strLine = Join(aTemp, ",")
        oFS.WriteLine strLine
    Next i
    oFS.Close
End Sub

 

[Just Jan]

Just a (silly) idea.

Couldn't you split the 16 digit number in two (=LEFT(A1,8) and =MID(A1,9,LEN(A1)) then CONCATENATE them later. Since Excel only has a 15 digit capacity the data must be diplayed as text anyway.

 

[squidmark]

Excel will only retain 15 significant digits, so you're losing precision in the conversion (the last digits are all zero).

The only way I know to do this is to change the extension to .txt and open the file using the text import wizard, specifying those fields as Text. Or do the same thing using VBA.

Did this, and it worked perfectly as far as getting it into Excel properly. Thank you.

My problem now is I have to save the file as a csv file. When I do so, and open it back up again, the scientific notation is back. How do I keep the 16-digits on full display when the file is a csv file?

 

[kpark91]

Not too sure but try formatting all the cells by TEXT
and then importing the text file.

 

[Joe4]

The problem isn't with the CSV file, it is opening the CSV file in Excel. Opening in it Excel does automatic formatting and the those values are imported as numbers, not text, which is why you are getting that.

If you want to see how those numbers REALLY appear in the CSV file use a Text Editor (like NotePad) to look at the final CSV file instead of Excel.

Otherwise, if you need/want to view it in Excel, you will need to open it invoking the Text Import Wizard like shg explained and select that field to Text, just like you did when you opened it the first time.

BTW, there is another way to do this without changing the file extension to ".txt". Use Get External Data -> From Text and select your file. This will invoke the Import Wizard just like changing the extension to ".txt" would and opening it (these directions are for Excel 2007 or later, but similar Get External Data options exist in earlier versions).

 

[squidmark]

Did this, and it worked perfectly as far as getting it into Excel properly. Thank you.

My problem now is I have to save the file as a csv file. When I do so, and open it back up again, the scientific notation is back. How do I keep the 16-digits on full display when the file is a csv file?

Well, if anyone is interested, I found a fix. Don't close the file before importing. If I save the file and don't close it, the digits import just fine. If I save the file, close the file, THEN import, then it's a no-go.

thanks for your help.

 

[rbghrs]

if you are using vb6 or any vb.net then
put " ' "+16 digit number so that in excel it is converted in text automatically


command in vb is like this


If I = 23 Then
ws.Cells(J, colcount) = "'" + VPLAN.TextMatrix(J - 3, I)
Else
ws.Cells(J, colcount) = VPLAN.TextMatrix(J - 3, I)
End If

'1234567890123456

ok bye

 

[Fari m]

Thaaanks a lot. I was searching for this solution... now i can import the real long numbers.