在 Excel2007中导入带换行符的 CSV

我正在开发一个功能，可以将搜索结果导出到一个 CSV 文件中，以便在 Excel 中打开。其中一个字段是自由文本字段，可能包含换行符、逗号、引号等。为了解决这个问题，我用双引号(“)包装了这个字段。

但是，当我将数据导入到 Excel2007中，设置合适的分隔符，并将文本限定符设置为双引号时，换行符仍然会在换行符处创建新记录，在这种情况下，我希望在单个单元格中看到整个文本字段。

我也试过用 CR (r)代替 CR/LF (r n) ，再用 LF (n)代替，但没有成功。

有没有其他人遇到过这种行为，如果有，你是如何解决的？

短暂性脑缺血发作,
J

编辑:
这里有一个我手写的快速文件来复制这个问题。

身份证，姓名，描述
“12345”“史密斯，乔”“嘿”。
我叫乔”

当我将其导入 Excel2007时，最终得到一个标题行和两条记录。请注意，“ Smith，Joe”中的逗号处理得当。只是线路断了才会引起问题。

209078 次浏览
小开

如果字段包含前导空格，Excel 将忽略双引号作为文本限定符。解决方案是消除逗号(字段分隔符)和双引号之间的前导空格。例如:

破碎:
姓名，名称，描述
“约翰”、“先生”、“我的详细描述”

工作:
姓名，名称，描述
“约翰”、“先生”、“我的详细描述”

小开
最佳答案

我终于找到问题所在了！

结果是，我们使用 Unicode 编码而不是 ASCII 或 UTF-8编写文件。更改 FileStream 上的编码似乎解决了这个问题。

谢谢大家的建议！

小开

所有建议的解决办法对我都不起作用。

实际工作原理(使用任何编码) :

复制/粘贴来自 csv-file 的数据(在文本编辑器中打开) ，然后执行“ text to column”—— > data gets Transform unwrong。

下一步是转到最近的空列或空工作表，再次复制/粘贴(与剪贴板中的内容相同)—— > 现在自动工作了。

小开

Excel (至少在 XP 上的 Office2007中)的行为可能会有所不同，这取决于是否从 File-> Open 菜单打开 CSV 文件，或者在 Explorer 中双击该文件。

我有一个用 UTF-8编码的 CSV 文件，它在某些单元格中包含换行符。如果我从 Excel 的 File-> Open 菜单中打开这个文件，“ import CSV”向导会弹出，文件无法正确导入: 即使引用了新行，新行也会开始一个新行。如果我在资源管理器窗口中双击该文件打开它，那么它将在没有向导干预的情况下正确打开。

小开

简短的回答

删除换行符/换行符(带记事本 + + 的 \n)。 Excel 仍然会识别回车符(\r)来分隔记录。

长答案

正如前面提到的，在 CSV 字段中支持换行符，但是 Excel 并不总是优雅地处理它们。我在第三方 CSV 中遇到了类似的问题，它可能有编码问题，但是没有随着编码的改变而改进。

对我有效的方法是删除所有换行符(\n)。假设您的记录是由回车和换行(CR/LF)组合分隔的，那么这会将字段折叠为单个记录。然后 Excel 将正确导入该文件，并通过回车返回识别新记录。

显然，一个更简洁的解决方案是首先用一个临时字符组合替换真正的换行符(\r\n) ，用你选择的分隔字符(例如分号文件中的逗号)替换换行符(\n) ，然后再用适当的换行符替换临时字符。

小开

如果您手动执行此操作，请下载 LibreOffice 并使用 LibreOfficeCalc 导入您的 CSV。它比我尝试过的任何版本的 Excel 都做得更好，如果你需要在之后转移到 Excel，它可以根据需要保存到 XLS 或 XLSX。

但是如果你被 Excel 卡住了，需要一个更好的解决方案，似乎有一个办法。它似乎是依赖于区域设置的(在我看来，这似乎很愚蠢)。我没有 Excel2007，但我有 Excel2010，以及给出的示例:

ID,Name,Description
"12345","Smith, Joe","Hey.
My name is Joe."

没用。我把它写在记事本上，然后选择 Save as... ，在 Save 按钮旁边你可以选择编码。我按照建议选择了 UTF-8，但运气不佳。不过，把逗号改成分号对我来说很有用。我没有改变任何东西，它只是工作。因此，我将示例更改为如下所示，并在记事本中保存时选择了 UTF-8编码:

ID;Name;Description
"12345";"Smith, Joe";"Hey.
My name is Joe."

但是有个问题！它的唯一工作方式是双击 CSV 文件在 Excel 中打开它。如果我尝试从文本中导入数据并选择了这个 CSV，那么在换行符上仍然会失败。

但是有 另一个接球！工作字段分隔符(在最初的例子中是逗号，在我的例子中是分号)似乎依赖于系统的区域设置(设置在控制面板-> 区域和语言下)。在挪威，逗号是小数点。Excel 似乎避免使用这个字符，而更喜欢使用分号。我可以访问另一台设置为英国英语地区的计算机，在那台计算机上，第一个使用逗号分隔符的示例工作正常(只在双击时) ，而使用分号的示例实际上失败了！互操作性到此为止了。如果你想在线发布这个 CSV 并且用户可能有 Excel，我想你必须同时发布这两个版本，并且建议人们检查哪个文件给出了正确的行数。

所以我能收集到的所有能让这个起作用的细节是:

  1. 文件必须保存为带有 BOM 的 UTF-8，这是选择 UTF-8时记事本所做的。我尝试了不带 BOM 的 UTF-8(可以在记事本 + + 中轻松切换) ，但是双击文档失败。
  2. 您必须使用逗号或分号分隔符，但不能使用“区域设置”中的小数点分隔符。也许其他角色也有用，但我不知道是哪个。
  3. 必须引用包含“字符”换行符的字段。
  4. 我在文本字段和记录分隔符中都使用了 Windows 行结束符(r n) ，这很有效。
  5. 您必须双击该文件才能打开它，从文本导入数据不起作用。

希望这对谁有帮助。

小开

粘贴到记事本 + + ，选择编码 > 编码在 ANSI，复制所有再粘贴到 Excel:)

小开

我也遇到了这个问题，原来 Excel 需要:

  • 引号字符串中的换行符(“ n”)

  • 每行之间的回车和换行符。

例如。

"Test", "Multiline item\n
multiline item"\r\n
"Test2", "Multiline item\n
multiline item"\r\n

我使用记事本 + + 来正确地分隔每一行，并且只在字符串中使用换行符。通过在一个空的 excel 文档中创建多行条目并在记事本 + + 中打开 csv，发现了这一点。

小开

我的经验与 Excel2010在 WinXP 与法国地区设置

  • 导入的 csv 的分隔符必须对应于区域设置的列表分隔符(在我的例子中)
  • 您必须双击资源管理器中的文件。不要从 Excel 中打开它
小开

如果有人无意中发现了这个问题，并在这里寻找一个明确的答案，那么可以这样说(这要归功于提到 LibreOffice 的人:

1)安装 LibreOffice 2)打开计算和导入文件 3)我的文本文件中的字段由“ 4)另存为 ODS 档案 5)在 Excel 中开启 ODS 档案 6)另存为.xls (x) 7)成交。 8)这对我来说完美的工作，并节省了我的大时间！

小开

这对我来说很有用，直接导入到 Excel 中，只要导入是以文本格式而不是 csv 格式完成的。 先生

小开

只要创建一个新的单元格与换行符，保存到 csv，然后打开一个编辑器，可以显示行结束字符(如记事本 + +)。通过这样做，您将注意到单元格中的换行符是用 LF 编码的，而“真正的”行尾是用 CR LF 编码的。好了，现在您知道如何为 excel 生成一个“正确的”csv 文件了。

小开

我也有过类似的问题。我在 MySQL 里有一些 twitter 数据。数据中包含 Line feed (LF 或 n)。我需要将 MySQL 数据导出到 excel 中。LF 搞乱了我导入的 csv 文件。所以我做了下面这些

1. From MySQL exported to CSV with Record separator as CRLF
2. Opened the data in notepad++
3. Replaced CRLF (\r\n) with some string I am not expecting in the Data. I used ###~###! as replacement of CRLF
4. Replaced LF (\n) with Space
5. Replaced ###~###! with \r\n, so my record separator are back.
6. Saved and then imported into Excel

注意-在替换 CRLF 或 LF 时，不要忘记检查 Exend (n，r，t。.复选框[查看对话框左下方)

小开

我也遇到了这个问题: 比如，csv 文件(逗号分隔，双引号分隔的字符串)使用 LF 加引号的字符串。这些是下载的 Square 文件。我做了一个数据导入，但不是作为文本文件导入，而是作为“从 HTML”导入。这次它忽略了引用字符串中的 LF。

小开

这在 Mac 上是可行的，使用 csv 并在 Excel 中打开文件。

使用 python 编写 csv 文件。

Data =’“第一行细胞 a1 r 第二行细胞 a1 r 第三行细胞 a1”，“细胞 b1”，“第一行细胞 c1 r 第二行细胞 c1”n“第一行细胞 a2”n’

Write (data)

小开

在 MacOS 上尝试使用 Numbers

如果你能访问 Mac OS，我发现 Apple 的电子表格 数字能很好地解析 Excel 无法处理的复杂的多行 CSV 文件。打开带有数字的 .csv，然后导出到 Excel。

小开

在我的例子中，在记事本 + + 中打开 CSV，并添加 SEP=","作为第一行，这样就可以在 Excel 中打开带有换行符的 CSV 和 utf-8，而不会出现问题

小开

将分隔符替换为 TAB (t)而不是逗号(,)。 然后在编辑器中打开该文件(记事本等) ，从中复制内容，然后将其粘贴到 Excel 文件中。

小开

根据 CSV 标准，双引号中的换行完全可以。 Excel 中分行符的解析取决于列表分隔符的 OS 设置:

  1. Windows: 您需要将列表分隔符设置为逗号(区域和语言“格式”高级) 资料来源: https://superuser.com/questions/238944/how-to-force-excel-to-open-csv-files-with-data-arranged-in-columns#answer-633302

  2. Mac: 需要将该区域更改为 US (然后手动将其他设置更改为您的首选项) 来源: https://answers.microsoft.com/en-us/mac/forum/macoffice2016-macexcel/line-separator-comma-semicolon-in-excel-2016-for/7db1b1a0-0300-44ba-ab9b-35d1c40159c6(见 Newman Lee 的回答)

不要忘记在再次尝试之前完全关闭 Excel。

我已经成功地复制了这个问题，并且能够在 Max 和 Windows 中使用上述方法修复它。

小开

Excel 在处理 CSV 的时候出现了令人难以置信的故障。 LibreOffice 做得更好。所以，我发现:

  • 该文件必须使用带 BOM 的 UTF-8编码，因此请考虑以下所有要点
  • 到目前为止，最好的结果是从文件资源管理器打开它
  • 如果从 Excel 中打开它，有两种可能的结果:
    • 如果它只有 ASCII 字符，那么它很可能会正常工作
    • 如果它具有非 ASCII 字符，它将混乱您的换行符
  • 它似乎严重依赖小数点的配置 操作系统的区域设置，所以你必须选择正确的一个
  • 我敢打赌，它也可能会根据操作系统和 办公室版本
小开

使用 GoogleSheets 并导入 CSV 文件。

然后您可以将其导出到 Excel 中使用

小开

概述

在最初的帖子发布10年后，Excel 在导入 CSV 文件方面没有任何改进。但是，我发现它在导入 HTML 表时要好得多。因此，可以使用 Python 将 CSV 转换为 HTML，然后将生成的 HTML 导入到 Excel 中。

这种方法的优点是: (a)它工作可靠，(b)你不需要把你的数据发送到第三方服务(例如谷歌工作表) ，(c)对大多数用户来说不需要额外的“肥胖”安装(LibreOffice，Numbers 等) ，(d)高于干预 CR/LF 字符和 BOM 标记，(e)不需要干预本地设置。

步骤

只要安装了 Python3，就可以在任何类似 bash 的 shell 上运行以下步骤。尽管 Python 可以用来直接读取 CSV，但是 CSVKit用于执行到 JSON 的中间转换。这使我们可以避免在 Python 代码中处理复杂的 CSV。

首先，将下面的脚本保存为 json2html.py。该脚本从 stdin 读取 JSON 文件并将其转储为 HTML 表:

#!/usr/bin/env python3
import sys, json, html


if __name__ == '__main__':
header_emitted = False
make_th = lambda s: "<th>%s</th>" % (html.escape(s if s else ""))
make_td = lambda s: "<td>%s</td>" % (html.escape(s if s else ""))
make_tr = lambda l, make_cell: "<tr>%s</tr>" % ( "".join([make_cell(v) for v in l]) )
print("<html><body>\n<table>")
for line in json.load(sys.stdin):
lk, lv = zip(*line.items())
if not header_emitted:
print(make_tr(lk, make_th))
header_emitted = True
print(make_tr(lv, make_td))
print("</table\n</body></html>")

然后，在虚拟环境中安装 csvkit，并使用 csvjson将输入文件提供给脚本。使用 -I参数禁用单元格类型猜测是一个好主意:

$ virtualenv -p python3 pyenv
$ . ./pyenv/bin/activate
$ pip install csvkit
$ csvjson -I input.csv | python3 json2html.py > output.html

现在可以在 Excel 中导入 output.html。单元格中的换行符将被保留。

您可以选择清理 Python 虚拟环境:

$ deactivate
$ rm -rf pyenv
小开

这是针对 Excel 2016的:

只是在使用 Excel 向导的 csv 文件中出现了相同的换行问题。

之后，我尝试了“新查询”功能: Data-> New Query-> From File-> From CSV-> Select the File-> Import-> Load

对于你们这些有同样问题的人来说，这是一个非常快速的解决方案。

小开

使用 Excel 2019时，我在处理通过 Data-> Import from text file/CSV 的 CSV 文件时遇到了类似的问题。一旦建立了连接并同步了数据，它就会报告 谢谢错误，因为换行导致列移位。

我设法解决了这个问题

  1. 编辑查询(Query-> Edit)

  2. 这将打开 Power 查询编辑器

  3. 转到开始-> 高级编辑器

  4. 这将以文本格式打开查询，其中第2行具有如下指令

    Source = CSv.Document (File. content (“ my.Csv”) ，[ Dlimiter = “ ,”，... . ，QuoteStyle = QuoteStyle. None ]) ,

  5. 更改 QuoteStyle。无到 QuoteStyle

  6. 单击 Finish

  7. 申请及关闭

这里找到的文档: https://learn.microsoft.com/en-us/powerquery-m/csv-document

注意。我后来发现这是“隐藏”在用户界面。在 Power Query 编辑器中，单击 Data source sets，Change source (左下) ，换行组合应该是 忽略引号之间的换行符。

NB2。从荷兰 Excel 工作在这里，所以我上面提到的按钮标题等翻译可能有点关闭。

小开

在最新版本的 Excel 中，这似乎要容易得多:

  1. 点击“数据”-> “获取数据(电源查询)”
  2. 在打开的对话框中，选择右边的“ Text/CSV”
  3. 搜索该文件，然后单击“ Next”并按照建议操作(在我的例子中，Excel 现在正确地认识到它是 UTF8，单元格由“ ;”分隔，文本标识符是双引号(“)
  4. 你完蛋了！

这需要一点时间加载，但之后我有一个自动格式化的表，看起来真的很好，这确实理解多行条目仍然是同一条目的一部分。

如果您希望正确显示多行，只需格式化单元格并在“对齐”下，单击“包装文本”复选框。这应该能解决你最后一个问题了。

祝你好运! ; -)

小开

使用 Power Query，可以使用以下步骤(在 Excel365版本2207中进行了测试)轻松地导入 Excel 版本中的多行 CSV:

  1. 转到 Data-tab
  2. 在色带上点击“ From Text/CSV”
  3. 选择 file 并单击 Import
  4. 单击“转换数据”打开电源查询编辑器
  5. 点击“数据源设置”在电源查询编辑器色带
  6. 点击“更改来源”
  7. 从“换行”下拉列表中选择“忽略带引号的换行”。
  8. 点击 OK-> Close-> Close & Load

提交答案