Simple CSV and Excel problem

Ruby
1

Hello all!
Im a real newbie and I have been reading a lot of threads regarding csv
and excel but still dont know how to solve my problem.

I have a csv file like this:

“title1”,“title2”,…
“value1”,“value2”,…
“value11”,“value22”,…

The problem:
I would like to import this csv to an excel workbook. Later on work with
the cells but thats another issue. Right now I would like to start with
just import the csv.

So in my mind the solution should be something like this:

require ‘win32ole’
excel = WIN32OLE::new(‘excel.Application’)
excel.visible=1
wb = excel.Workbooks.Add()
ws = wb.Worksheets(1)

Now an excel workbook is created and I would like to import the CSV.

How? Is there a method or something that can be used?

I made this but dont know what to do for each iteration:

def printCSV
CSV.foreach(@filename,:headers => true, :quote_char => ‘"’, :col_sep
=> ‘,’, :row_sep =>:auto) do |row|
"This code should write one value to each cell in the row of the
worksheet "#puts row[0]
end
end

Is this the best way of solving my problem?

Any suggestions are appreciated!

Br
cristian

2

On Dec 28, 2011, at 3:40 AM, Vimal Selvam wrote:

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’.

I must be missing something, why not leave it as a .csv file? These are
readily opened by Excel to start with, why go through the trouble of
converting them to native .xls files? I’m sure I’m missing something,
which is why I’ve asked.

Wayne

3

Hello!
Thank you!
Where can I find the WIN32OLE excel documentation?

I did this and it works fine:

require ‘win32ole’

To load the WIN32OLE’s built in constants in to the module

module EXCEL_CONST
end

begin

Create a WIN32OLE excel object

excel = WIN32OLE::new(‘excel.Application’)

Load all the constants

WIN32OLE.const_load(excel, EXCEL_CONST)

Open the excel workbook

filename = ‘C:\Ruby\Lab\test.csv’
excel.workbooks.open(filename)

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

4

cristian cristian wrote in post #1038431:

Hello!
Thank you!
Where can I find the WIN32OLE excel documentation?

eg Wrox Excel 2007 VBA Programmer’s Reference (Programmer to Programmer)

It is also documented on Microsoft sites eg
Excel Object model overview - Visual Studio (Windows) | Microsoft Learn

I would have thought you could just open the file and then save it in
Excel format using the _Workbook.SaveAs method.

5

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

6

-----Messaggio originale-----
Da: Mike S. [mailto:[email protected]]
Inviato: mercoled 28 dicembre 2011 23:22
A: ruby-talk ML
Oggetto: Re: Simple CSV and Excel problem.

cristian cristian wrote in post #1038431:

Hello!
Thank you!
Where can I find the WIN32OLE excel documentation?

eg Wrox Excel 2007 VBA Programmer’s Reference (Programmer to Programmer)

It is also documented on Microsoft sites eg

I would have thought you could just open the file and then save it in
Excel
format using the _Workbook.SaveAs method.

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

–
Caselle da 1GB, trasmetti allegati fino a 3GB e in piu’ IMAP, POP3 e
SMTP autenticato? GRATIS solo con Email.it http://www.email.it/f

Sponsor:
Capodanno a Riccione, Pacchetto Relax: Mezza Pensione + bagno turco +
solarium + massaggio. Wifi e parcheggio gratis. 2 giorni euro 199 a
persona
Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid978&d)-12

7

Now Im trying to save the workbook as an excel workbook but it saves the
file as an CSV. I want a .xlsx

So I did this:

…
ensure

Save the workbook and close

filename is ‘C:\Ruby\Lab\test.csv’

excel.activeworkbook.SaveAs(filename.split(".").first+".xlsx")
excel.activeworkbook.close(false)

Quit Excel

excel.quit
end

This saves the file without problem but I cant open it because the file
format or the file extension is not valid. How should I save it??

Any suggestions?

Br
cristian

8

-----Messaggio originale-----
Da: cristian cristian [mailto:[email protected]]
Inviato: marted 27 dicembre 2011 22:15
A: ruby-talk ML
Oggetto: Simple CSV and Excel problem.

Hello all!
Im a real newbie and I have been reading a lot of threads regarding csv
and
excel but still dont know how to solve my problem.

I have a csv file like this:

“title1”,“title2”,…
“value1”,“value2”,…
“value11”,“value22”,…

The problem:
I would like to import this csv to an excel workbook. Later on work with
the
cells but thats another issue. Right now I would like to start with just
import the csv.

So in my mind the solution should be something like this:

require ‘win32ole’
excel = WIN32OLE::new(‘excel.Application’)
excel.visible=1
wb = excel.Workbooks.Add()
ws = wb.Worksheets(1)

Now an excel workbook is created and I would like to import the CSV.

How? Is there a method or something that can be used?

I made this but dont know what to do for each iteration:

def printCSV
CSV.foreach(@filename,:headers => true, :quote_char => ‘"’, :col_sep
=>
‘,’, :row_sep =>:auto) do |row|
"This code should write one value to each cell in the row of the
worksheet "#puts row[0]
end
end

Is this the best way of solving my problem?

Any suggestions are appreciated!

Br
cristian

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

–
Caselle da 1GB, trasmetti allegati fino a 3GB e in piu’ IMAP, POP3 e
SMTP autenticato? GRATIS solo con Email.it http://www.email.it/f

Sponsor:
ING DIRECT Conto Arancio. 4,20% per 12 mesi, zero spese, aprilo in due
minuti!
Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid924&d)-12

9

cristian cristian wrote in post #1039540:

Now Im trying to save the workbook as an excel workbook but it saves the
file as an CSV. I want a .xlsx

So I did this:

…
ensure

Save the workbook and close

filename is ‘C:\Ruby\Lab\test.csv’

excel.activeworkbook.SaveAs(filename.split(“.”).first+“.xlsx”)
excel.activeworkbook.close(false)

Quit Excel

excel.quit
end

This saves the file without problem but I cant open it because the file
format or the file extension is not valid. How should I save it??

Any suggestions?

Br
cristian

The SaveAs() method accepts a second argument to specify the file
format.

The value for xlOpenXMLWorkbook (Excel 2007+ .xlsx) is 51.

So simply change your SaveAs code to the following:

excel.activeworkbook.SaveAs(filename.split(“.”).first+“.xlsx”, 51)

David

10

On Thu, Jan 5, 2012 at 12:41 PM, David M. [email protected]
wrote:

Any suggestions?

excel.activeworkbook.SaveAs(filename.split(“.”).first+“.xlsx”, 51)

It’s a good idea to strip the extension of the file with
File.basename(filename, File.extname(filename)), in case there is a
period in it that doesn’t immediately precede the extension.