Ruby: Cannot Parse Excel File Exported as CSV in Os X

Ruby: cannot parse Excel file exported as CSV in OS X

Try: CSV.open('filename', 'r', ?,, ?\r)

As cantlin notes, for Ruby 2 it's:

CSV.new('file.csv', 'r', :col_sep => ?,, :row_sep => ?\r)

I'm pretty sure these will DTRT for you. You can also "fix" the file itself (in which case keep the old open) with the following vim command: :%s/\r/\r/g

Yes, I know that command looks like a total no-op, but it will work.

Exporting Ruby CSV from ActiveRecord single quote in Excel looks weird

I fixed my problem by adding BOM characters in the beginning of my file to force the UTF-8 in Excel.

CSV.open(fileNameInbound, 'wb', encoding: 'UTF-8') do |csv| 
  myModel.all.each do |m| 
    csv << ['\xef\xbb\xbf']
    csv << m 
  end 
end

Ruby CSV does not understand \r\n as row end

Works for me in 1.9.3:

mark@ubuntu:~$ irb
1.9.3p0 :001 > require 'csv'
 => true
1.9.3p0 :002 > CSV.foreach("rn.csv") do |row|
1.9.3p0 :003 >   p row
1.9.3p0 :004 > end
["1","2","3","4","5"]
["6","7","8","9","10"]

And the file does indeed have carriage returns in it:

mark@ubuntu:~$ od -a rn.csv
0000000   1   ,   2   ,   3   ,   4   ,   5  cr  nl   6   ,   7   ,   8
0000020   ,   9   ,   1   0  cr  nl
0000027

write.csv() writes a different result from Mac OS than from Windows 10?

The problem isn’t R, the problem is Excel.

Excel has its own ideas about what a platform’s character encoding should be. Notably, it insists, even on modern macOSs, that the platform encoding is naturally Mac Roman. Rather than the actually prevailing UTF-8.

The file is correctly written as UTF-8 on macOS by default.

To get Excel to read it correctly, you need to choose “File” › “Import…”, and from thre follow the import wizard which lets you specify the file encoding.

How to convert CSV to Excel?

According to this post, the spreadsheet gem is a possibility. It looks like this is a very popular gem. Check it out. Example:

book = Spreadsheet::Workbook.new
sheet1 = book.create_worksheet

header_format = Spreadsheet::Format.new(
  :weight => :bold,
  :horizontal_align => :center,
  :bottom => true,
  :locked => true
)

sheet1.row(0).default_format = header_format

FasterCSV.open(input_path, 'r') do |csv|
  csv.each_with_index do |row, i|
    sheet1.row(i).replace(row)
  end
end

book.write(output_path)

According to this post, write_xlsx is a possibility.

I've used the Apache POI library with JRuby to export xls files. Here's a quick example.

require 'java'
require 'poi.jar'
# require 'poi-ooxml.jar'
require 'rubygems'
require 'fastercsv'

java_import org.apache.poi.hssf.usermodel.HSSFWorkbook;

wb = HSSFWorkbook.new # OR XSSFWorkbook, for xlsx
sheet = wb.create_sheet('Sheet 1')

FasterCSV.open(ARGV.first) do |csv|
  csv.each_with_index do |csv_row, line_no|
    row = sheet.createRow(line_no)
    csv_row.each_with_index do |csv_value, col_no|
      cell = row.createCell(col_no)
      cell.setCellValue(csv_value) unless csv_value.nil? # can't pass nil.
    end
  end
end

f = java.io.FileOutputStream.new("workbook.xls")
wb.write(f)
f.close

Some useful methods for formatting POI spreadsheets are

• sheet.createFreezePane(0,1,0,1)
• wb.setRepeatingRowsAndColumns(0, -1, -1, 0, 1)
• sheet.setColumnWidth(i, 100 *256)
• sheet.autoSizeColumn(i), but beware, if you're running in headless mode, you have to call java.lang.System.setProperty("java.awt.headless", "true")

You can also use Win32ole on Windows, if you have Excel installed

require 'win32ole'
require 'rubygems'
require 'fastercsv'

xl = WIN32OLE.new('Excel.Application')
xl.Visible = 0
wb = xl.Workbooks.Add
ws = wb.Worksheets(1)

FasterCSV.open(ARGV.first) do |csv|
  csv.each_with_index do |csv_row, line_no|
    csv_row.each_with_index do |value, col|
      ws.Cells(line_no + 1, col + 1).Value = value
    end
  end
end

wb.SaveAs("workbook.xls", 56) # 56 = xlExcel8 aka Excel 97-2003. i.e. xls
wb.SaveAs("workbook.xlsx", 51) # 51 = xlOpenXMLWorkbook
wb.SaveAs("workbook.xlsb", 50) # 50 = xlExcel12

wb.Close(2) #xlDoNotSaveChanges
xl.Quit

Some useful methods for formatting with Excel are

• xl.Rows(1).Font.Bold = true
• ws.Cells.EntireColumn.AutoFit

Yet another option is to write directly to Microsoft's XML Spreadsheet format, as Ryan Bates at Railscasts.com does at the end of his Exporting CSV and Excel episode.

<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:o="urn:schemas-microsoft-com:office:office"
  xmlns:x="urn:schemas-microsoft-com:office:excel"
  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:html="http://www.w3.org/TR/REC-html40">
  <Worksheet ss:Name="Sheet1">
    <Table>
      <Row>
        <Cell><Data ss:Type="String">ID</Data></Cell>
        <Cell><Data ss:Type="String">Name</Data></Cell>
        <Cell><Data ss:Type="String">Release Date</Data></Cell>
        <Cell><Data ss:Type="String">Price</Data></Cell>
      </Row>
    <% @products.each do |product| %>
      <Row>
        <Cell><Data ss:Type="Number"><%= product.id %></Data></Cell>
        <Cell><Data ss:Type="String"><%= product.name %></Data></Cell>
        <Cell><Data ss:Type="String"><%= product.released_on %></Data></Cell>
        <Cell><Data ss:Type="Number"><%= product.price %></Data></Cell>
      </Row>
    <% end %>
    </Table>
  </Worksheet>
</Workbook>

This gem looks promising, too.

CHCSVWriter Unicode Problem

As nobody could resolve my problem,

I found that Unicode encoding is not very well supported with Microsoft Excell ( mac ).
In other hand, because of this fact that I'm using Perisan language for my data entries so I have to use NSUTF16StringEncoding ( why UTF8StringEncoding didn't work ? I don't know !! ) and when I open the .csv file I have all my data in just one column ( I have 16 column ) and Microsoft Excell can not detect comma (,) as separation delimiter !!!

Anyway, now, I am able to open the UTF16 Encoded CSV file using NeoOffice or OpenOffice that well supported Unicode .csv files without any problem !

So, this all about Microsft not Dave deLong's CHCSVWriter. ( thank you Dave, Again )

this my 5th question that I solved by myself !!!

Thank you all anyway.

---

Related Topics