正确地导出 CSV 文件到 Open Office（将数字保存为文本）

Question

我正在使用OPEN-OFFICE处理和保存 CSV 文件。 我使用逗号分隔符和双引号。 然而，在保存 CSV 时，所有数字都没有被封装。 如何强制 Open-Office 将数字视为文本并将它们封装起来？示例：

"store","website","attribute_set","type","sku","name","price","categories","description","qty","sizet","is_in_stock","status","visibility","tax_class_id"
"admin","base","test","simple","T010013-012","Test12","12","test/test","Desc12",12,"S","1","Enabled","Catalog, Search","Taxable Goods"
"admin","base","test","simple","T010013-013","Test13","13","test/test","Desc13",13,"M","1","Enabled","Catalog, Search","Taxable Goods"
"admin","base","test","simple","T010013-014","Test14","14","test/test","Desc14",14,"L","1","Enabled","Catalog, Search","Taxable Goods"
"admin","base","test","simple","T010013-015","Test15","15","test/test","Desc15",15,"XL","1","Enabled","Catalog, Search","Taxable Goods"
"admin","base","test","simple","T010013-016","Test16","16","test/test","Desc16",16,"XXL","1","Enabled","Catalog, Search","Taxable Goods"
"admin","base","test","configurable","T010013","TestParent","5","test/test","DescParent","30","","1","Enabled","Catalog, Search","Taxable Goods"

感谢您的帮助。 PS：我正在使用MAGMI脚本上传Magento产品。

- user1023021

---

请注意，CSV不需要引用任何不包含逗号或换行符的内容。您可以从示例中删除所有引号，它仍将是有效的CSV。 - Christopher Creutzig

Answer 1

这需要两个步骤：

1. 将包含数字的单元格格式化为文本（由于整个列都需要该格式，因此将该格式应用于整个列，这样如果添加行，则不需要为每个新单元格进行格式化）;

2. 修改导出过滤器设置以引用所有文本单元格:

使用这种设置，Calc 应该会将这张表格：

保存成如下形式：

"bar","42"
"foo","57"

顺便提一下，Calc完全符合标准，但默认不会引用每个数字。至少RFC 4180并没有要求引用每个字段。

- tohuwawohu

---

我正在做完全相同的事情。但是当我用Notepad++打开它时，所有数字都没有引号。 - user1023021

@user1023021：刚在Ubuntu Linux上测试了一下，对我来说完美运行（同样是LibreOffice 3.5.4.2）。所以我绝对确定这个解决方案可行。 - tohuwawohu

@user1023021：所以你只是将已经有值的单元格格式化为文本？我认为格式化整个列会更容易。只需单击一次列标题（在我的示例中为 B），以选择整个列。然后，右键单击并选择“格式化单元格”（或菜单“格式”->“单元格…”或简单地按CTRL+1），然后设置整个列的文本格式。 - tohuwawohu

我在OpenOffice或Excel中没有看到这些选项。 - boulder_ruby

@boulder_ruby：我没有使用Excel测试解决方案，只是用LibreOffice测试了一下。OpenOffice应该也是同样的方式，因为user1023021接受了这个答案。如果你仍然遇到问题，可以创建一个新问题，并提供关于你正在使用的操作系统和OpenOffice版本的额外细节。 - tohuwawohu

Answer 2

使用正确的SQL导入语法，有一种稍微简单的方法可以绕过这个问题。请考虑以下内容：

LOAD DATA LOCAL INFILE '/yourfile.csv' 
INTO TABLE yourtable  
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"';

"

使用OPTIONALLY ENCLOSED BY '"' 行应该解决数字行的问题，而无需花费时间来处理从Open/LibreOffice等辅助应用程序导出的CSV格式。

"

- Josh Wieder

Answer 3

写了一个小的 Ruby 脚本来完成这个任务，而不是试图在 OpenOffice 或 Excel 中找到选项。但仍然找不到它们。

require 'csv'

rows = CSV.open(ARGV[0]).readlines
CSV.open("#{ARGV[0].split('.')[0]}_qgisfriendly.csv", "w", {:force_quotes => true}) do |csv|
  rows.each do |row|
    csv << row
  end
end

- boulder_ruby