How cell data is stored in a CSV File

[ds_robb]

I get CSV files, which in one of the cells is supposed to store a client address. These are typically 12 digit plus numeric fields. When I open the CSV file, the column holding these fields is defined as general and typically the value is shown as 1.23457E+22 whereas the actual value should be 12345678910111213141516 Looking at that field or say converting it to numeric now shows the number as 12345678910111200000000.00, so past the 15th number it's all be set to zero. Does a CSV/Excel have the ability to store a longer number as it should be above or have I lost that the minute the application that created the CSV populated the data to it.

i.e. can I do anything to ensure that once the application writes to a CSV, but either pre-specifying the field type or digits to be retained ensure I get the whole number, not just the first 15 digits.

 

[Jeffrey Mahoney]

A CSV file is all text, it doesn't have numeric restrictions. The application that created the CSV file determines the format. Excel turns it into a number unless you specify elsewise. Can you store it as text in Excel?

 

[Alex Blakenburg]

This is related to your previous question in which I suggested using Power Query but you did not respond.

Large Numbers Display vs Storage

I wonder if someone could help as this is driving me bonkers! I have a very large excel file, close on a million lines which has in one of the columns partner EDI addresses which are a mix of alphanumeric characters. When it's totally numeric, the number could be 21+ digits long and the CSV...

www.mrexcel.com

Normally step one to determine where the issue lies is to open your csv file in notepad and make sure all the digits are there.
Since Excel is showing +22, I suspect the data is in the csv file.

Excel can't handle that many digits as a number and you need to handle it as Text.
Some options are:
• Load the data using Power Query and "Do not detect data types" or Delete or modify the Change Type step it creates.
• Change the file extension to something other than csv eg txt and when you load it you will get access to the import wizard at which point you can tell it to import that column as text
• Use a macro to import the file

Power Query

 

[Joe4]

I get CSV files, which in one of the cells is supposed to store a client address. These are typically 12 digit plus numeric fields. When I open the CSV file, the column holding these fields is defined as general and typically the value is shown as 1.23457E+22 whereas the actual value should be 12345678910111213141516 Looking at that field or say converting it to numeric now shows the number as 12345678910111200000000.00, so past the 15th number it's all be set to zero. Does a CSV/Excel have the ability to store a longer number as it should be above or have I lost that the minute the application that created the CSV populated the data to it.

i.e. can I do anything to ensure that once the application writes to a CSV, but either pre-specifying the field type or digits to be retained ensure I get the whole number, not just the first 15 digits.

Also note if you want to REALLY see how data is stored in a CSV file, do NOT open the CSV file in Excel!
Excel will NOT necessarily show you the data exactly as it exists, because Excel will perform automatic conversions on data as "it sees fit" when opening the file in Excel.
If you TRULY want to see what the data in a CSV file looks like, view it in a Text Editor like NotePad. It will show you the data as it actually exists, without any conversions done to it.

One of my biggest pet peeves about Microsoft is that it determined that Excel should be the default program to open/view CSV files. This is problematic for the reasons I stated above.
One of the very first things I do when I get a new computer is change that setting so that NotePad (or an after-market Text Editor like NotePad++) is the default program to open and view my CSV files.

 

[rlv01]

ie. can I do anything to ensure that once the application writes to a CSV, but either pre-specifying the field type or digits to be retained ensure I get the whole number, not just the first 15 digits.

If you are open to a VBA solution, here is a routine I use when I need to open a CSV file as text to avoid the automatic data conversion process.

'''
''' Open a CSV as text file to avoid formatting the data
'''
Sub CSV_Open_Text(fname As String)
    '
    Dim QueryConnection As String
    Dim QueryName As String
    Dim ColCnt As Integer, I As Integer
    Dim QT As QueryTable
    Dim WS As Worksheet
    Dim FormatArray() As Integer
    Dim WB As Workbook
    
    QueryConnection = "TEXT;" & fname
    QueryName = "CSV_Import"
    
    Set WB = Application.Workbooks.Add(xlWBATWorksheet)
    WB.Activate
    Set WS = ActiveSheet
    
    Set QT = WS.QueryTables.Add(Connection:=QueryConnection, Destination:=Range("A1"))
    
    With WS.QueryTables.Add(Connection:=QueryConnection, Destination:=Range("A1"))
        .Name = QueryName
        .FillAdjacentFormulas = False
        .RefreshPeriod = 0
        .RefreshStyle = xlInsertDeleteCells
        .RowNumbers = False
        .SaveData = True
        .SavePassword = False
        .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
        .TextFileCommaDelimiter = True
        .TextFileConsecutiveDelimiter = False
        .TextFileOtherDelimiter = ""                  ' Turns out this is sticky per the excel UI so it's important to explicitly set this to null.
        .TextFileParseType = xlDelimited
        .TextFilePlatform = xlWindows
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        '.AdjustColumnWidth = True
        '.FieldNames = True
        '.TextFilePromptOnRefresh = False
        '.TextFileSemicolonDelimiter = False
        '.TextFileSpaceDelimiter = False
        '.TextFileStartRow = 1
        '.TextFileTabDelimiter = False
        '.PreserveFormatting = True
        '.RefreshOnFileOpen = False
        .Refresh BackgroundQuery:=False
    End With
    
    ColCnt = ActiveSheet.UsedRange.Columns.Count
    If ColCnt > 20 Then
        ReDim FormatArray(ColCnt - 1)
        For I = 0 To ColCnt - 1
            FormatArray(I) = 2
            DoEvents
        Next I
        
        QT.TextFileColumnDataTypes = FormatArray
        QT.Refresh
    End If
End Sub