☰

在 Excel 2010 中控制 CSV 导入

tag-icon • tag-icon microsoft-excel-2010 csv
在 Excel 2010 中控制 CSV 导入

我有一个 csv，其值如下：

“一个非常长的值，带有逗号”，“387621937291732193”

尽管数字被引号括起来，但它在 Excel 中会变成数字并以科学计数法显示。如何防止 Excel 假设所有内容都是数字？为什么 Excel 在打开 CSV 时不显示任何选项，而打开 .txt 文件时却显示？

答案1

您可以将其添加=到数字字符串的开头：

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

更新

如果您无法更改 CSV，则您有两个选择。您可以将列属性设置为在 CSV 加载后以数字格式显示。只需右键单击单元格并选择Format Cell...。从那里只需Number在列表中选择。它是第二项。关闭它应该看起来不错。

如果您需要将文件导入到 Excel 中，并且文件已经正确，那么您需要自己处理文件。您可以使用任何编程语言来执行此操作。只需读取字段并按您喜欢的方式输出即可。

以下是一个 vbscript 示例：

' 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

要使用此功能，只需将上述文本保存到名为的文件中PrepCSV.vbs。然后您可以单击它并输入要处理的文件名，也可以从命令行调用它，例如：

PrepCSV.vbs inputfile.csv outputfile.csv

相关内容