vba 将包含换行符的文本文件导入 excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/340400/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
import text file containing line breaks into excel
提问by Maximilian Tyrtania
I have a plain text file looking like this:
我有一个看起来像这样的纯文本文件：
"some
text
containing
line
breaks"
I'm trying to talk excel 2004 (Mac, v.11.5)into opening this file correctly. I'd expect to see only one cell (A1) containing all of the above (without the quotes)...
我正在尝试excel 2004 (Mac, v.11.5)正确打开此文件。我希望只看到一个单元格 (A1) 包含上述所有内容（不带引号）...
But alas, I can't make it happen, because Excel seems to insist on using the CR's as row delimiters, even if I set the text qualifier to double quote. I was sort of hoping that Excel would understand that those line breaks are part of the value - they are embedded in double quotes which should qualify them as part of the value. So my Excel sheet has 5 rows, which is not what I want.
但唉，我不能让它发生，因为 Excel 似乎坚持使用 CR 作为行分隔符，即使我将文本限定符设置为双引号。我有点希望 Excel 能够理解这些换行符是值的一部分 - 它们嵌入在双引号中，应该将它们限定为值的一部分。所以我的 Excel 工作表有 5 行，这不是我想要的。
I also tried this Applescript to no avail:
我也试过这个 Applescript 无济于事：
tell application "Microsoft Excel"
activate
open text file filename ?
"Users:maximiliantyrtania:Desktop:linebreaks" data type delimited ?
text qualifier text qualifier double quote ?
field info {{1, text format}} ?
origin Macintosh with tab
end tell
If I could tell Excel to use a row delimiter other than CR (or LF), well, I'd be a happy camper, but excel seems to allow the change of the field delimiter only, not the row delimiter.
如果我可以告诉 Excel 使用 CR（或 LF）以外的行分隔符，那么，我会是一个快乐的露营者，但 excel 似乎只允许更改字段分隔符，而不是行分隔符。
Any pointers?
任何指针？
Thanks,
谢谢，
Max Excel's open
Max Excel 已打开
回答by Maximilian Tyrtania
Looks like I just found the solution myself. I need to save the initial file as ".csv". Excel honors the line breaks properly with CSV files. Opening those via applescript works as well.
看起来我自己刚刚找到了解决方案。我需要将初始文件保存为“.csv”。Excel 使用 CSV 文件正确处理换行符。通过applescript打开那些也可以。
Thanks again to those who responded.
再次感谢回答的人。
Max
最大限度
回答by Michel de Ruiter
If your file has columns separated by list separators (comma's, but semicolons for some non-English region settings), rename it to .csv and open it in Excel. If your file has columns separated by TABs, rename it to .tab and open it in Excel. Importing (instead of opening) a csv or tab file does not seem to understand line feeds in between text delimiters. :-(
如果您的文件包含由列表分隔符（逗号，但某些非英语区域设置使用分号）分隔的列，请将其重命名为 .csv 并在 Excel 中打开。如果您的文件包含由 TAB 分隔的列，请将其重命名为 .tab 并在 Excel 中打开它。导入（而不是打开）一个 csv 或 tab 文件似乎无法理解文本分隔符之间的换行符。:-(
回答by LeppyR64
The other option is to create a macro to handle the opening. Open the file for input, and then read the text into the worksheet, parsing as you need, using a Range object.
另一种选择是创建一个宏来处理打开。打开文件进行输入，然后将文本读入工作表，根据需要使用 Range 对象进行解析。
回答by Alterlife
Is it just one file? If so, don\'t import it. Just copy paste the content of your text file into the first cell (hit f2, then paste).
只有一个文件吗？如果是这样，请不要导入它。只需将文本文件的内容复制粘贴到第一个单元格中（按 f2，然后粘贴）。
If you absolutely must script this, Excel actually uses only one of those two chars (cr, lf) as the row delimiter, but I'm not sure which. Try first stripping out the lf's with an external util (leave the cr's) and then import it... if that does't work, strip out the cr's (leave the lf's) and thenimport it.
如果您绝对必须编写此脚本，Excel 实际上只使用这两个字符 (cr, lf) 中的一个作为行分隔符，但我不确定是哪个。尝试首先使用外部实用程序去除 lf（保留 cr），然后导入它...如果这不起作用，请去掉 cr（保留 lf），然后导入它。
