0

I have a csv with values like this:

"a very long value, with a comma","387621937291732193"

The number, despite being enclosed in quotes gets turned into a number in excel and shows up in scientific notation. How do you prevent excel from assuming that everything is a number? Why does excel not show any options when opening a CSV as it does for .txt files?

Swaroop
  • 101
  • 1

1 Answers1

0

You can add = to the start of the number string:

"a very long value, with a comma",="387621937291732193"

Update

If you can't change the CSV then you have two options. You can just set the column property to display in number format after the CSV loads. Just right click on the cell and select Format Cell.... From there just select Number in the list. It is the second item. Close and it should look fine.

If you need the file to import into Excel already correct then you'll need to process the file yourself. You can do this in any programming language. Just read in the fields and output them however you like.

Here is a vbscript example:

' The columns begin at index 0, this array should include indexes for each column which should be treated literally.
' The script will add a = before these columns if it doesn't already exist.

' If you want you could add the ability to set this at the command line to make this more flexible.
literalColumns=Array(1)

'----------------------------------------------------------
' Nothing else should need to be changed.

IsOK=True
FileNotFound=False
CSVFileName=""
OutputFileName="output.csv" ' This is the default output file to use when none is given via the command line.

If WScript.Arguments.Count = 1 Or WScript.Arguments.Count = 2 Then
    CSVFileName = WScript.Arguments.Item(0)
    If WScript.Arguments.Count = 2 Then
        OutputFileName = WScript.Arguments.Item(1)
    End If
Else
    CSVFileName = InputBox("Enter a CSV file name to process:", "CSV Input File")
    If Len(CSVFileName) < 1 Then
        IsOK=False
        FileNotFound = True
    End If
End If

If IsOK Then
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    If objFSO.FileExists(CSVFileName) Then
        ProcCSV CSVFileName, OutputFileName
    Else
        IsOK = False
        FileNotFound = True
    End If
End If

If IsOK = False Then
    msg="Usage: PrepCSV.vbs CSVFileName [OutputFileName]"
    If FileNotFound Then
        msg = msg & vbCrLf & vbCrLf &"File Not Found."
    End If
    Wscript.Echo msg
    Wscript.Quit
End If

Sub ProcCSV(InFileName, OutFileName)
    ReDim ToInsert(0)
    Set FS = CreateObject("Scripting.FileSystemObject")
    Set InFile = FS.OpenTextFile(InFileName, 1, False, -2)
    Set OutFile = objFSO.CreateTextFile(OutFileName)
    Set Regex = CreateObject("VBScript.RegExp")
    Regex.Pattern = """[^""]*""|[^,]*"
    Regex.Global = True
    Do While Not InFile.AtEndOfStream
        ReDim ToInsert(0)
        CSVLine = InFile.ReadLine
        For Each Match In Regex.Execute(CSVLine)
            If Match.Length > 0 Then
                ColDX = UBound(ToInsert)
                ReDim Preserve ToInsert(ColDX + 1)
                If InArray(ColDX, literalColumns) And Left(Match.Value, 1) <> "=" Then
                    ToInsert(ColDX) = "=" & Match.Value
                Else
                    ToInsert(ColDX) = Match.Value
                End If
            End If
        Next
        CSVLine = Join(ToInsert, ",")
        OutFile.Write Left(CSVLine, Len(CSVLine) - 1) & vbCrLf
    Loop
    InFile.Close
    OutFile.Close
End Sub

Function InArray(item, arr)
    For i=0 To UBound(arr)
        If arr(i) = item Then
            InArray=True
            Exit Function
        End If
    Next
    InArray=False
End Function

To use this just save the above text to a file named PrepCSV.vbs. You can then click on it and enter the file name to process or you can call it from the command line like:

PrepCSV.vbs inputfile.csv outputfile.csv
krowe
  • 5,629