From: Vimal Selvam <email2vimalraj@...>
Date: 2011-12-28T18:40:42+09:00
Subject: Re: Simple CSV and Excel problem.

Hi Cristian,

Importing into excel, then looping into every cell or range is a time
consuming process. Instead, you rename the file from csv to xls and use
the WIN32OLE built-in method called: 'TextToColumns'.

The WIN32OLE excel documentation defines TextToColumns like this:
Function TextToColumns([Destination], [DataType As XlTextParsingType =
xlDelimited], [TextQualifier As XlTextQualifier =
xlTextQualifierDoubleQuote], [ConsecutiveDelimiter], [Tab], [Semicolon],
[Comma], [Space], [Other], [OtherChar], [FieldInfo], [DecimalSeparator],
[ThousandsSeparator], [TrailingMinusNumbers])
    Member of Excel.Range

So by considering the above points, I written a small utility to rename
the file from 'csv' to 'xls' and call TextToColumns method to convert
comma delimited text to normal readable excel columns:


require 'win32ole'

# To load the WIN32OLE's built in constants in to the module
module EXCEL_CONST
end

# Definition to rename the file into xls
# For ex: It renames file 'sample.csv' into 'sample.csv.xls'
def file_rename(filename)
  File.rename(filename, filename + ".xls")
end

begin
  file_rename("C:/Sample.csv")

  # Create a WIN32OLE excel object
  excel = WIN32OLE::new('excel.Application')

  # Load all the constants
  WIN32OLE.const_load(excel, EXCEL_CONST)

  # Open the excel workbook
  excel.workbooks.open("C:/Sample.csv.xls")

  # Read the used range
  range = excel.activeworkbook.activesheet.usedrange

  # Convert the comma seperated data to the column
  range.TextToColumns(excel.activeworkbook.activesheet.Range("A1"),
EXCEL_CONST::XlDelimited, EXCEL_CONST::XlDoubleQuote, false, false,
false, true)
rescue
  # Raises any exception caught in begin section
  puts $!
ensure
  # Save the workbook and close
  excel.activeworkbook.save
  excel.activeworkbook.close(false)

  # Quit Excel
  excel.quit
end

I hope this method is good enough to automate your task. Please let me
know if in case the method is not feasible.

Cheers,
Vimal Raj

-- 
Posted via http://www.ruby-forum.com/.