Simple CSV and Excel problem

ruby-talk
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

···

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

2

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

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

···

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

4

-----Messaggio originale-----

···

Da: cristian cristian [mailto:currambero@hotmail.com]
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

5

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

···

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

6

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

···

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

7

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.

···

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

8

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

···

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

9

-----Messaggio originale-----

···

Da: Mike Stephens [mailto:rubfor@recitel.net]
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
http://msdn.microsoft.com/en-us/library/wss56bz7.aspx

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

10

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.

···

On Thu, Jan 5, 2012 at 12:41 PM, David Mullet <david.mullet@gmail.com> wrote:

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)