14

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

dusan
  • 9,104
  • 3
  • 35
  • 55
user1023021
  • 343
  • 4
  • 8
  • 13
  • 1
    Note that CSV does not require quoting anything that does not contain a comma or a newline. You could remove all the quotes from your example and it would remain valid CSV. – Christopher Creutzig Jun 06 '12 at 11:31

3 Answers3

27

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.

Community
  • 1
  • 1
tohuwawohu
  • 13,268
  • 4
  • 42
  • 61
  • I am doing exactly the same thing. But when I open it with Notepad++, all numbers are not quoted. – user1023021 Jun 06 '12 at 13:26
  • Hmm - did you make sure that the cells are formatted as text? I assume the "`Quote all text cells`" affects only properly-formatted cells. I tested this with the latest LibreOffice 3.5.4.2 (Win 7), so i'm sure it works. But i will test again using Linux, maybe you've hit a bug? – tohuwawohu Jun 06 '12 at 14:46
  • @user1023021: just tested using Ubuntu Linux - works perfectly for me there, too (again, LibreOffice 3.5.4.2). So i'm definitly sure that the solution works. – tohuwawohu Jun 06 '12 at 14:51
  • If I don't change the cell value, the quotes stays, but if I fill a new cell with numbers and hit save as > filter etc... old cell are quoted, new cells are not. – user1023021 Jun 06 '12 at 18:38
  • ps: I am using open-office, not Libre-office ( I don't know if there is a difference between the two) – user1023021 Jun 06 '12 at 18:39
  • 2
    @user1023021: so you've just formatted only the cells as text that already had a value? I think formatting the complete column is much easier. Just click once on the column header (in my example: `B`) so the entire column is selected. Then, right-click and select `format cells`(or Menu `Format` -> `Cells...` or simply hit CTRL+1), then set text formatting for the complete column. – tohuwawohu Jun 06 '12 at 19:34
  • I'm not seeing these options in openoffice or excel – boulder_ruby Jan 31 '14 at 21:02
  • @boulder_ruby: I didn't test the solution with Excel, just with LibreOffice. OpenOffice should work the same way, since user1023021 accepted the answer. If you still encounter problems, you may create a new question, with additionaö details about the OS and OpenOffice version you're using. – tohuwawohu Feb 01 '14 at 08:54
  • when you re-import again.. make sure you include the checkbox to format all as text.. otherwise.. you will lose the changes again – Bhikkhu Subhuti Jul 02 '20 at 13:09
2

There is a slightly easier way to side-step this problem using the correct SQL import syntax. Consider the following: 

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

The OPTIONALLY ENCLOSED BY '"' line should resolve the issue with numeric lines, without having to spend time messing with how the CSV is formatted upon export from a secondary application like Open/LibreOffice.

Josh Wieder
  • 186
  • 6
0

Wrote a little ruby script for doing this rather trying to find the options in OpenOffice or Excel. Still can't find them.

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
  • 38,457
  • 9
  • 79
  • 100