Exporting CSV properly open Office (save numbers as TEXT)

user1023021 picture user1023021 · Jun 5, 2012 · Viewed 35.1k times · Source

I am using OPEN-OFFICE to work and save CSV. I am using the comma delimiter and ' " ' also. However, when saving the CSV, all numbers are not encapasulated. How can I force Open-Office to treat numbers as a Text and have them encapsulated too. Example:

"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"

Thank you for your help. PS: I am using MAGMI script for Magento. Uploading products

Answer

tohuwawohu picture tohuwawohu · Jun 6, 2012

This requires two steps:

  1. Format the cells holding numerals as text (since the entire column will need that formatting, just apply that format to the complete column, so you don't need to format every new cell if you add rows);

  2. Modify the export filter settings to quote all text cells:

edit filter settings

quote all text cells

With this setting, Calc should save this sheet:

sheet

as follows:

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

BTW, Calc is completely standards-compliant not to quote every number by default. At least, RFC 4180 doesn't require quoting every field.