☰

Excel 插件用于打开 CSV 并将所有值导入为文本

tag-icon • tag-icon microsoft-excel formatting
Excel 插件用于打开 CSV 并将所有值导入为文本

尽管我尽量避免这种情况，但有时我不得不在 Excel 中打开 CSV 文件。当我这样做时，它会格式化包含数字的列，这对我来说毫无用处。据我所知，防止这种情况发生的唯一方法是进口方法是重命名文件，使扩展名不再是 .csv，然后使用导入向导单独指定每列的格式。对于包含 50-60 列的文件，这是不切实际的。

由于互联网上对这个常见问题的每个答案都建议在文件打开后将格式化的数字转换回来（这对我来说不起作用 - 我想解决一般问题，而不是少数特定情况）或手动选择每列的格式类型（我不想这样做），我正在寻找一种方法来设置全局首选项或样式，以便所有打开的 CSV 文件的所有列始终格式化为文本。我也知道用引号“保护”数字，但我得到的文件不是那样的，我希望避免对文件进行预处理，这样 Excel 就不会搞砸它们。

有没有办法具体来说这个：始终将打开的 CSV 文件中的所有列格式化为文本，而不是每次导入时手动选择每一列？

我使用的是 Excel 2003，但如果您知道的话，我会获取 2007 年的答案。

答案1

这有效：

Sub OpenCsvAsText(ByVal strFilepath As String)

    Dim intFileNo As Integer
    Dim iCol As Long
    Dim nCol As Long
    Dim strLine As String
    Dim varColumnFormat As Variant
    Dim varTemp As Variant

    '// Read first line of file to figure out how many columns there are
    intFileNo = FreeFile()
    Open strFilepath For Input As #intFileNo
    Line Input #intFileNo, strLine
    Close #intFileNo
    varTemp = Split(strLine, ",")
    nCol = UBound(varTemp) + 1

    '// Prepare description of column format
    ReDim varColumnFormat(0 To nCol - 1)
    For iCol = 1 To nCol
        varColumnFormat(iCol - 1) = Array(iCol, xlTextFormat)
        ' What's this? See VBA help for OpenText method (FieldInfo argument).
    Next iCol

    '// Open the file using the specified column formats
    Workbooks.OpenText _
            Filename:=strFilepath, _
            DataType:=xlDelimited, _
            ConsecutiveDelimiter:=False, Comma:=True, _
            FieldInfo:=varColumnFormat

End Sub

用法：

OpenCsvAsText "C:\MyDir\MyFile.txt"

逗号分隔的文件现在作为 Excel 表打开，其中所有列都格式化为文本。

请注意，@Wetmelon 的向导解决方案工作得很好，但如果您打开许多文件，那么您可能会像我一样，每次都厌倦滚动到第 60 列才能按住 Shift 键单击它。

编辑@GSerg 在下面的评论中指出，这“不起作用”并且“占用空格和前导零”。我仅引用对这个问题的评论，它更具描述性：

由于未知原因，即使您在 VBA 中明确提供所有列的格式，如果文件扩展名是 CSV，Excel 也会忽略它。只要您更改扩展名，相同的代码就会产生正确的结果。

因此，上面的代码“有效”，但却被这种荒谬的 Excel 行为所破坏。无论你如何修改，你都必须将扩展名更改为“.csv”以外的其他内容，很抱歉！之后，你就大功告成了。

答案2

如何在 Excel 中打开 CSV

好办法

  • Excel → 数据 → 获取外部数据 → 选择所有列Shift并选择文本

    优点：正确地将所有值视为文本，没有任何例外

    缺点：比简单方法多几个步骤双击

坏方法

  • 使用以下方式打开 CSV双击或 Excel 的打开用对话

    缺点：Excel 的内部 CSV 处理程序会将带有前导-或=符号的值误解为公式而不是文本

    0001缺点：由于 Excel 自动检测列格式，您将丢失二进制值的前导零

好办法（适用于 VBA）

  • 使用 QueryTables（的 VBA 对应部分Get external data）→示例代码

    优点：正确地将所有值视为文本，没有任何例外

    OpenText缺点：代码比方法略多

坏方法（适用于 VBA）

  • 使用Workbooks.OpenText方法→示例代码

    缺点：此方法仍然使用 Excel 的内部 CSV 导入处理程序，存在诸多缺陷

    缺点：此外，fieldinfo如果扩展名为 CSV，则忽略参数OpenText。通常，此参数允许您选择每列格式，但如果扩展名为 CSV，则不允许。您可以在堆栈溢出

    如果您对源文件有完全控制权，则暂时将源扩展名从 CSV 重命名为 TXT，然后再重命名回 CSV 是一种有效的解决方法

其他方法

  • 如果您有权访问创建 CSV 的源，则可以更改 CSV 语法。
    用双引号将每个值括起来，并在每个值前面加上等号，例如="00001"或在每个值前面加上制表符。这两种方法都会强制 Excel 将值视为文本

    原始 CSV 内容
    在此处输入图片描述

    双击打开 Excel 中的 CSV
    在此处输入图片描述

    请注意，第 2 行（双引号方法）和第 3 行（制表符方法）不会被 Excel 更改

  • 在以下位置打开 CSV记事本并将所有值复制粘贴到 Excel。然后使用数据 - 文本到列
    缺点：列中的文本将列格式从常规格式改回文本格式会产生不一致的结果。如果某个值包含-由字符包围的 （例如“=E1-S1”），Excel 会尝试将该值拆分为多个列。该单元格右侧的值可能会被覆盖
    （行为文本分列在 Excel 2007 和 2013 之间发生了更改，因此不再起作用）

Excel 插件用于打开 CSV 并将所有值导入为文本

这是一个 Excel 插件，用于简化 CSV 导入操作。
主要优点：一键式解决方案，使用QueryTables与背后相同的万无一失的方法获取外部数据

  • 它为 Excel 添加了一个新的菜单命令，允许您导入 CSV 和 TXT 文件。所有值都将从当前选定的单元格开始导入到活动工作表中
  • Excel 插件适用于 Windows 和 Mac 上的所有 Office 版本
  • 整个插件只有 35 行代码。查看注释源代码如果你好奇
  • 使用的 CSV 列表分隔符（逗号或分号）取自您的本地 Excel 设置
  • 编码设置为 UTF-8

安装

  1. 下载加入并将其保存到您的 Add-Ins 文件夹：%appdata%\Microsoft\AddIns
  2. 打开 Excel 并激活插件：File tab → Options → Add-Ins → Go To并选择ImportCSV.xla
  3. 启用 VBA 宏：File tab → Options → Trust Center → Trust Center Settings → Macro Settings → Enable all macros
  4. 重新启动 Excel

您会注意到菜单栏中有一个名为“插件”的新条目，您可以使用此按钮快速打开 CSV 文件，而无需经过导入对话框的麻烦

在此处输入图片描述

PowerShell 脚本可直接从 Windows 资源管理器打开 CSV

您可以使用 PowerShell 脚本打开 CSV 文件并自动将其传递给 Excel。该脚本会默默使用 Excel 的文本导入方法，该方法始终将值视为文本，并且作为额外功能，还可以处理 UTF-8 编码

  1. 新建一个文本文件并粘贴以下脚本。可以找到注释版本这里
$CSVs = @()
$args.ForEach({
    If ((Test-Path $_) -and ($_ -Match "\.csv$|\.txt$")) {
        $CSVs += ,$_
    } 
})

if (-Not $null -eq $CSVs) {

    $excel = New-Object -ComObject excel.application 
    $excel.visible = $true
    $excel.SheetsInNewWorkbook = $CSVs.Count    
    $workbook = $excel.Workbooks.Add()

    for ($i=0; $i -lt $CSVs.Count; $i++){

        $csv = Get-Item $CSVs[$i]
        $worksheet = $workbook.worksheets.Item($i + 1)
        $worksheet.Name = $csv.basename

        $TxtConnector = ("TEXT;" + $csv.fullname)
        $Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
        $query = $worksheet.QueryTables.item($Connector.name)
        $query.TextFilePlatform = 65001
        $query.TextFileTextQualifier = 1
        $query.TextFileOtherDelimiter = $Excel.Application.International(5) 
        $query.TextFileParseType  = 1
        $arrFormats = ,2 * $worksheet.Cells.Columns.Count
        $query.TextFileColumnDataTypes = $arrFormats
        $query.AdjustColumnWidth = 1
        $query.Refresh()
        $query.Delete()
    }
}
  1. 将其保存在类似的地方C:\my\folder\myScript.ps1。（注意扩展名.ps1）
    • WinR通过» shell:sendto» 打开您的发送文件夹Enter
  2. 通过右键单击 » 新建 » 快捷方式创建一个新的快捷方式并粘贴此行。不要忘记将路径更改为您放置脚本的路径。命名快捷方式，例如，Excel

“％SystemRoot％\ system32 \ WindowsPowerShell \ v1.0 \ powershell.exe”-NoProfile -NonInteractive -WindowStyle Hidden -File“C:\my\folder\myScript.ps1”

现在，您可以选择（多个）CSV 并通过以下方式在 Excel 中打开它们Right-click » SendTo » Excel

答案3

您可以先尝试打开 .xlsx，然后创建数据连接并导入 .csv。选择“逗号”分隔，然后选择将所有列视为文本而不是“常规”的选项。

编辑：哦，我没有完全读懂这个问题。在导入向导中，选择要作为文本导入的第一个列标题，滚动到最后一个列标题，然后按住 Shift 并单击该标题。然后选择“文本”径向选项。

答案4

小心！

当使用手动方法“Excel→数据→获取外部数据→选择所有列并选择文本”时......

这只会将列设置为“第一行包含数据”的文本（通常是标题行）。此向导不会向您显示右侧的列，这些列可能包含下方但不在第一行的数据。例如：

行 1 列 1、行 1 列 2、行 1 列 3

行 2 列 1、行 2 列 2、行 2 列 3、行 2 列 4

您永远不会在导入向导中看到 Col 4，因此在导入之前您将无法选择将其从常规格式更改为文本格式！！！！

相关内容