Read data from Excel

A client of mine has a ton of data stored in Excel spreadsheets. I’m
building a web application for her (backed by a real db of course) and
want
to import all that data. Is there a library available to read data from
Excel? Is there an easy way to upload the spreadsheets and then read
the
data?

Thanks,

Ken K.

Kenneth A. Kousen, Ph.D.

President

Kousen IT, Inc.

http://www.kousenit.com http://www.kousenit.com

mailto:[email protected] [email protected]

If you have access to the spreadsheets, the easiest approach is
probably to export them to csv and write your own importer to read
those csv files. If you’re running ruby on windows, you can also use
the win32ole library to read the data directly from excel, though this
is more complicated. I’m not aware of any library to directly access
excel documents, but if anyone knows different, I’d really like to
hear.

Tom W.

On 1/16/06, Ken K. [email protected] wrote:

President
Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

Excel has really good support for XML as well, so exporting to that
format
may be an option.


Larry W.
http://www.approachingnormal.com
http://www.welcometoparenthood.com
http://www.approachingnormal.com

I thought of exporting the data to CSV format and I may still do that.
The
problem is that the data is highly structured - it’s not just a couple
of
simple tables; specific cells have specific pieces of data in them in
addition to the tables.

And yes, I’m using MySQL, so mysqlimport sounds very helpful.

Is there some way to do the export to CSV programmatically? Otherwise
this
is going to be very labor intensive.

Thanks for the help,

Ken K.

Kenneth A. Kousen, Ph.D.

President

Kousen IT, Inc.

http://www.kousenit.com

[email protected]


From: [email protected]
[mailto:[email protected]] On Behalf Of Will B.
Sent: Monday, January 16, 2006 10:26 AM
To: [email protected]
Subject: Re: [Rails] Read data from Excel

What DB are you using to back it? If you are using mysql, just export
the
spreadsheet to CSV and use the mysqlimport command to pull the data into
the
DB.

-Will

On 1/16/06, Tom W. [email protected] wrote:

If you have access to the spreadsheets, the easiest approach is
probably to export them to csv and write your own importer to read
those csv files. If you’re running ruby on windows, you can also use
the win32ole library to read the data directly from excel, though this
is more complicated. I’m not aware of any library to directly access
excel documents, but if anyone knows different, I’d really like to
hear.

Tom W.

On 1/16/06, Ken K. [email protected] wrote:

A client of mine has a ton of data stored in Excel spreadsheets. I’m
building a web application for her (backed by a real db of course) and
want

What DB are you using to back it? If you are using mysql, just export
the
spreadsheet to CSV and use the mysqlimport command to pull the data into
the
DB.

-Will

On 1/16/06, Ken K. [email protected] wrote:

Ken K.

http://www.kousenit.com

[email protected]

What I’m suggesting will certainly sound like nails on a chalkboard to
most
people on this list, but here goes anyway: If it’s highly structured,
but
consistently formatted, you may want to consider writing some VBScript
(gasp!) to extract the data.

Ken K. wrote:

A client of mine has a ton of data stored in Excel spreadsheets. I’m
building a web application for her (backed by a real db of course) and
want
to import all that data. Is there a library available to read data from
Excel? Is there an easy way to upload the spreadsheets and then read
the
data?

The easiest way to go about this is using the OLE DB driver in windows
to extract the data trough SQL.

Another way would be windows automation to save the data in the
worksheets / export the files to xml.

Both require a windows platform though.

Hi

a bit late maybe, but have a look at this which I just discovered (not
tested yet) : the poi ruby bindings (
POI Ruby Bindings)

===========

The POI library can now be compiled as a Ruby extension, allowing the
API to
be called from Ruby language programs. Ruby users can therefore read and
write OLE2 documents, such as Excel files with ease

The bindings are generated by compiling POI with
gcjhttp://gcc.gnu.org/java/,
and generating the Ruby wrapper using SWIG http://www.swig.org/. The
aim
is the keep the POI api as-is. However, where java standard library
objects
are used, an effort is made to transform them smoothly into Ruby
objects.
Therefore, where the POI API takes an OutputStream, you can pass an IO
object. Where the POI works java.util.Date or java.util.Calendar object,
you
can work with a Ruby Time object.

Thibaut

[blog] http://www.dotnetguru2.org/tbarrere

On Jan 16, 2006, at 6:46 AM, Ken K. wrote:

A client of mine has a ton of data stored in Excel spreadsheets.
I?m building a web application for her (backed by a real db of
course) and want to import all that data. Is there a library
available to read data from Excel? Is there an easy way to upload
the spreadsheets and then read the data?

Not the best approach, but the most convenient for me: install the
Spreadsheet::ParseExcel <http://search.cpan.org/~kwitknr/Spreadsheet-
ParseExcel-0.2603/> module from CPAN, and use a Perl script to
transform the Excel data to delimited text.

Scott

Hi.

I have used win32ole several times for exctracting data from Excel. If
you
under Win try this way. It is very easy to use.
Only problem that it does not work properly with Non-ASCII symbols. Bug
present in 1.8.2 and reported that it fixed in 1.8.4

Interesting. Before I started digging into RoR, I was planning to do
this
project in Java and had looked at the POI library with that in mind.
Now
that thereÂ?s a port to Ruby, maybe itÂ?ll all work out.

Thanks,

Ken

Kenneth A. Kousen, Ph.D.

President

Kousen IT, Inc.

http://www.kousenit.com

[email protected]


From: [email protected]
[mailto:[email protected]] On Behalf Of Thibaut
Barrère
Sent: Thursday, January 19, 2006 7:25 AM
To: [email protected]
Subject: Re: [Rails] Re: Read data from Excel

Hi

a bit late maybe, but have a look at this which I just discovered (not
tested yet) : the poi ruby bindings
(POI Ruby Bindings)

===========

The POI library can now be compiled as a Ruby extension, allowing the
API to
be called from Ruby language programs. Ruby users can therefore read and
write OLE2 documents, such as Excel files with ease

The bindings are generated by compiling POI with gcj
http://gcc.gnu.org/java/ , and generating the Ruby wrapper using SWIG
http://www.swig.org/ . The aim is the keep the POI api as-is. However,
where java standard library objects are used, an effort is made to
transform
them smoothly into Ruby objects. Therefore, where the POI API takes an
OutputStream, you can pass an IO object. Where the POI works
java.util.Date
or java.util.Calendar object, you can work with a Ruby Time object.

===========

Thibaut

[blog] http://www.dotnetguru2.org/tbarrere

On 18/01/06, Mischa K. [email protected] wrote:

Ken K. wrote:

A client of mine has a ton of data stored in Excel spreadsheets. I’m
building a web application for her (backed by a real db of course) and
want
to import all that data. Is there a library available to read data from
Excel? Is there an easy way to upload the spreadsheets and then read
the
data?

The easiest way to go about this is using the OLE DB driver in windows
to extract the data trough SQL.

Another way would be windows automation to save the data in the
worksheets / export the files to xml.

Both require a windows platform though.


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

Interesting. Before I started digging into RoR, I was planning to do
this
project in Java and had looked at the POI library with that in mind.
Now
that thereÂ?s a port to Ruby, maybe itÂ?ll all work out.

Thanks,

Ken

Kenneth A. Kousen, Ph.D.

President

Kousen IT, Inc.

http://www.kousenit.com

[email protected]


From: [email protected]
[mailto:[email protected]] On Behalf Of Thibaut
Barrère
Sent: Thursday, January 19, 2006 7:25 AM
To: [email protected]
Subject: Re: [Rails] Re: Read data from Excel

Hi

a bit late maybe, but have a look at this which I just discovered (not
tested yet) : the poi ruby bindings
(POI Ruby Bindings)

===========

The POI library can now be compiled as a Ruby extension, allowing the
API to
be called from Ruby language programs. Ruby users can therefore read and
write OLE2 documents, such as Excel files with ease

The bindings are generated by compiling POI with gcj
http://gcc.gnu.org/java/ , and generating the Ruby wrapper using SWIG
http://www.swig.org/ . The aim is the keep the POI api as-is. However,
where java standard library objects are used, an effort is made to
transform
them smoothly into Ruby objects. Therefore, where the POI API takes an
OutputStream, you can pass an IO object. Where the POI works
java.util.Date
or java.util.Calendar object, you can work with a Ruby Time object.

===========

Thibaut

[blog] http://www.dotnetguru2.org/tbarrere

On 18/01/06, Mischa K. [email protected] wrote:

Ken K. wrote:

A client of mine has a ton of data stored in Excel spreadsheets. I’m
building a web application for her (backed by a real db of course) and
want
to import all that data. Is there a library available to read data from
Excel? Is there an easy way to upload the spreadsheets and then read
the
data?

The easiest way to go about this is using the OLE DB driver in windows
to extract the data trough SQL.

Another way would be windows automation to save the data in the
worksheets / export the files to xml.

Both require a windows platform though.


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

Interesting. Before I started digging into RoR, I was planning to do
this
project in Java and had looked at the POI library with that in mind.
Now
that thereÂ?s a port to Ruby, maybe itÂ?ll all work out.

Thanks,

Ken

Kenneth A. Kousen, Ph.D.

President

Kousen IT, Inc.

http://www.kousenit.com

[email protected]


From: [email protected]
[mailto:[email protected]] On Behalf Of Thibaut
Barrère
Sent: Thursday, January 19, 2006 7:25 AM
To: [email protected]
Subject: Re: [Rails] Re: Read data from Excel

Hi

a bit late maybe, but have a look at this which I just discovered (not
tested yet) : the poi ruby bindings
(POI Ruby Bindings)

===========

The POI library can now be compiled as a Ruby extension, allowing the
API to
be called from Ruby language programs. Ruby users can therefore read and
write OLE2 documents, such as Excel files with ease

The bindings are generated by compiling POI with gcj
http://gcc.gnu.org/java/ , and generating the Ruby wrapper using SWIG
http://www.swig.org/ . The aim is the keep the POI api as-is. However,
where java standard library objects are used, an effort is made to
transform
them smoothly into Ruby objects. Therefore, where the POI API takes an
OutputStream, you can pass an IO object. Where the POI works
java.util.Date
or java.util.Calendar object, you can work with a Ruby Time object.

===========

Thibaut

[blog] http://www.dotnetguru2.org/tbarrere

On 18/01/06, Mischa K. [email protected] wrote:

Ken K. wrote:

A client of mine has a ton of data stored in Excel spreadsheets. I’m
building a web application for her (backed by a real db of course) and
want
to import all that data. Is there a library available to read data from
Excel? Is there an easy way to upload the spreadsheets and then read
the
data?

The easiest way to go about this is using the OLE DB driver in windows
to extract the data trough SQL.

Another way would be windows automation to save the data in the
worksheets / export the files to xml.

Both require a windows platform though.


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

Interesting. Before I started digging into RoR, I was planning to do
this
project in Java and had looked at the POI library with that in mind.
Now
that thereÂ?s a port to Ruby, maybe itÂ?ll all work out.

Thanks,

Ken

Kenneth A. Kousen, Ph.D.

President

Kousen IT, Inc.

http://www.kousenit.com

[email protected]


From: [email protected]
[mailto:[email protected]] On Behalf Of Thibaut
Barrère
Sent: Thursday, January 19, 2006 7:25 AM
To: [email protected]
Subject: Re: [Rails] Re: Read data from Excel

Hi

a bit late maybe, but have a look at this which I just discovered (not
tested yet) : the poi ruby bindings
(POI Ruby Bindings)

===========

The POI library can now be compiled as a Ruby extension, allowing the
API to
be called from Ruby language programs. Ruby users can therefore read and
write OLE2 documents, such as Excel files with ease

The bindings are generated by compiling POI with gcj
http://gcc.gnu.org/java/ , and generating the Ruby wrapper using SWIG
http://www.swig.org/ . The aim is the keep the POI api as-is. However,
where java standard library objects are used, an effort is made to
transform
them smoothly into Ruby objects. Therefore, where the POI API takes an
OutputStream, you can pass an IO object. Where the POI works
java.util.Date
or java.util.Calendar object, you can work with a Ruby Time object.

===========

Thibaut

[blog] http://www.dotnetguru2.org/tbarrere

On 18/01/06, Mischa K. [email protected] wrote:

Ken K. wrote:

A client of mine has a ton of data stored in Excel spreadsheets. I’m
building a web application for her (backed by a real db of course) and
want
to import all that data. Is there a library available to read data from
Excel? Is there an easy way to upload the spreadsheets and then read
the
data?

The easiest way to go about this is using the OLE DB driver in windows
to extract the data trough SQL.

Another way would be windows automation to save the data in the
worksheets / export the files to xml.

Both require a windows platform though.


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

Interesting. Before I started digging into RoR, I was planning to do
this
project in Java and had looked at the POI library with that in mind.
Now
that thereÂ?s a port to Ruby, maybe itÂ?ll all work out.

Thanks,

Ken

Kenneth A. Kousen, Ph.D.

President

Kousen IT, Inc.

http://www.kousenit.com

[email protected]


From: [email protected]
[mailto:[email protected]] On Behalf Of Thibaut
Barrère
Sent: Thursday, January 19, 2006 7:25 AM
To: [email protected]
Subject: Re: [Rails] Re: Read data from Excel

Hi

a bit late maybe, but have a look at this which I just discovered (not
tested yet) : the poi ruby bindings
(POI Ruby Bindings)

===========

The POI library can now be compiled as a Ruby extension, allowing the
API to
be called from Ruby language programs. Ruby users can therefore read and
write OLE2 documents, such as Excel files with ease

The bindings are generated by compiling POI with gcj
http://gcc.gnu.org/java/ , and generating the Ruby wrapper using SWIG
http://www.swig.org/ . The aim is the keep the POI api as-is. However,
where java standard library objects are used, an effort is made to
transform
them smoothly into Ruby objects. Therefore, where the POI API takes an
OutputStream, you can pass an IO object. Where the POI works
java.util.Date
or java.util.Calendar object, you can work with a Ruby Time object.

===========

Thibaut

[blog] http://www.dotnetguru2.org/tbarrere

On 18/01/06, Mischa K. [email protected] wrote:

Ken K. wrote:

A client of mine has a ton of data stored in Excel spreadsheets. I’m
building a web application for her (backed by a real db of course) and
want
to import all that data. Is there a library available to read data from
Excel? Is there an easy way to upload the spreadsheets and then read
the
data?

The easiest way to go about this is using the OLE DB driver in windows
to extract the data trough SQL.

Another way would be windows automation to save the data in the
worksheets / export the files to xml.

Both require a windows platform though.


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

Interesting. Before I started digging into RoR, I was planning to do
this
project in Java and had looked at the POI library with that in mind.
Now
that thereÂ?s a port to Ruby, maybe itÂ?ll all work out.

Thanks,

Ken

Kenneth A. Kousen, Ph.D.

President

Kousen IT, Inc.

http://www.kousenit.com

[email protected]


From: [email protected]
[mailto:[email protected]] On Behalf Of Thibaut
Barrère
Sent: Thursday, January 19, 2006 7:25 AM
To: [email protected]
Subject: Re: [Rails] Re: Read data from Excel

Hi

a bit late maybe, but have a look at this which I just discovered (not
tested yet) : the poi ruby bindings
(POI Ruby Bindings)

===========

The POI library can now be compiled as a Ruby extension, allowing the
API to
be called from Ruby language programs. Ruby users can therefore read and
write OLE2 documents, such as Excel files with ease

The bindings are generated by compiling POI with gcj
http://gcc.gnu.org/java/ , and generating the Ruby wrapper using SWIG
http://www.swig.org/ . The aim is the keep the POI api as-is. However,
where java standard library objects are used, an effort is made to
transform
them smoothly into Ruby objects. Therefore, where the POI API takes an
OutputStream, you can pass an IO object. Where the POI works
java.util.Date
or java.util.Calendar object, you can work with a Ruby Time object.

===========

Thibaut

[blog] http://www.dotnetguru2.org/tbarrere

On 18/01/06, Mischa K. [email protected] wrote:

Ken K. wrote:

A client of mine has a ton of data stored in Excel spreadsheets. I’m
building a web application for her (backed by a real db of course) and
want
to import all that data. Is there a library available to read data from
Excel? Is there an easy way to upload the spreadsheets and then read
the
data?

The easiest way to go about this is using the OLE DB driver in windows
to extract the data trough SQL.

Another way would be windows automation to save the data in the
worksheets / export the files to xml.

Both require a windows platform though.


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

Interesting. Before I started digging into RoR, I was planning to do
this
project in Java and had looked at the POI library with that in mind.
Now
that thereÂ?s a port to Ruby, maybe itÂ?ll all work out.

Thanks,

Ken

Kenneth A. Kousen, Ph.D.

President

Kousen IT, Inc.

http://www.kousenit.com

[email protected]


From: [email protected]
[mailto:[email protected]] On Behalf Of Thibaut
Barrère
Sent: Thursday, January 19, 2006 7:25 AM
To: [email protected]
Subject: Re: [Rails] Re: Read data from Excel

Hi

a bit late maybe, but have a look at this which I just discovered (not
tested yet) : the poi ruby bindings
(POI Ruby Bindings)

===========

The POI library can now be compiled as a Ruby extension, allowing the
API to
be called from Ruby language programs. Ruby users can therefore read and
write OLE2 documents, such as Excel files with ease

The bindings are generated by compiling POI with gcj
http://gcc.gnu.org/java/ , and generating the Ruby wrapper using SWIG
http://www.swig.org/ . The aim is the keep the POI api as-is. However,
where java standard library objects are used, an effort is made to
transform
them smoothly into Ruby objects. Therefore, where the POI API takes an
OutputStream, you can pass an IO object. Where the POI works
java.util.Date
or java.util.Calendar object, you can work with a Ruby Time object.

===========

Thibaut

[blog] http://www.dotnetguru2.org/tbarrere

On 18/01/06, Mischa K. [email protected] wrote:

Ken K. wrote:

A client of mine has a ton of data stored in Excel spreadsheets. I’m
building a web application for her (backed by a real db of course) and
want
to import all that data. Is there a library available to read data from
Excel? Is there an easy way to upload the spreadsheets and then read
the
data?

The easiest way to go about this is using the OLE DB driver in windows
to extract the data trough SQL.

Another way would be windows automation to save the data in the
worksheets / export the files to xml.

Both require a windows platform though.


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

Sorry about the multiple posts everybody. My email was acting very
flaky
yesterday. I could blame Outlook, but IÂ?ll just do what I normally do
and
blame Bill Gates directly.

Sigh,

Ken K.

Kenneth A. Kousen, Ph.D.

President

Kousen IT, Inc.

http://www.kousenit.com

[email protected]


From: [email protected]
[mailto:[email protected]] On Behalf Of Ken K.
Sent: Thursday, January 19, 2006 5:05 PM
To: [email protected]
Subject: RE: [Rails] Re: Read data from Excel

Interesting. Before I started digging into RoR, I was planning to do
this
project in Java and had looked at the POI library with that in mind.
Now
that thereÂ?s a port to Ruby, maybe itÂ?ll all work out.

Thanks,

Ken

Kenneth A. Kousen, Ph.D.

President

Kousen IT, Inc.

http://www.kousenit.com

[email protected]


From: [email protected]
[mailto:[email protected]] On Behalf Of Thibaut
Barrère
Sent: Thursday, January 19, 2006 7:25 AM
To: [email protected]
Subject: Re: [Rails] Re: Read data from Excel

Hi

a bit late maybe, but have a look at this which I just discovered (not
tested yet) : the poi ruby bindings
(POI Ruby Bindings)

===========

The POI library can now be compiled as a Ruby extension, allowing the
API to
be called from Ruby language programs. Ruby users can therefore read and
write OLE2 documents, such as Excel files with ease

The bindings are generated by compiling POI with gcj
http://gcc.gnu.org/java/ , and generating the Ruby wrapper using SWIG
http://www.swig.org/ . The aim is the keep the POI api as-is. However,
where java standard library objects are used, an effort is made to
transform
them smoothly into Ruby objects. Therefore, where the POI API takes an
OutputStream, you can pass an IO object. Where the POI works
java.util.Date
or java.util.Calendar object, you can work with a Ruby Time object.

===========

Thibaut

[blog] http://www.dotnetguru2.org/tbarrere

On 18/01/06, Mischa K. [email protected] wrote:

Ken K. wrote:

A client of mine has a ton of data stored in Excel spreadsheets. I’m
building a web application for her (backed by a real db of course) and
want
to import all that data. Is there a library available to read data from
Excel? Is there an easy way to upload the spreadsheets and then read
the
data?

The easiest way to go about this is using the OLE DB driver in windows
to extract the data trough SQL.

Another way would be windows automation to save the data in the
worksheets / export the files to xml.

Both require a windows platform though.


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

Interesting. Before I started digging into RoR, I was planning to do
this
project in Java and had looked at the POI library with that in mind.
Now
that thereÂ?s a port to Ruby, maybe itÂ?ll all work out.

Thanks,

Ken

Kenneth A. Kousen, Ph.D.

President

Kousen IT, Inc.

http://www.kousenit.com

[email protected]


From: [email protected]
[mailto:[email protected]] On Behalf Of Thibaut
Barrère
Sent: Thursday, January 19, 2006 7:25 AM
To: [email protected]
Subject: Re: [Rails] Re: Read data from Excel

Hi

a bit late maybe, but have a look at this which I just discovered (not
tested yet) : the poi ruby bindings
(POI Ruby Bindings)

===========

The POI library can now be compiled as a Ruby extension, allowing the
API to
be called from Ruby language programs. Ruby users can therefore read and
write OLE2 documents, such as Excel files with ease

The bindings are generated by compiling POI with gcj
http://gcc.gnu.org/java/ , and generating the Ruby wrapper using SWIG
http://www.swig.org/ . The aim is the keep the POI api as-is. However,
where java standard library objects are used, an effort is made to
transform
them smoothly into Ruby objects. Therefore, where the POI API takes an
OutputStream, you can pass an IO object. Where the POI works
java.util.Date
or java.util.Calendar object, you can work with a Ruby Time object.

===========

Thibaut

[blog] http://www.dotnetguru2.org/tbarrere

On 18/01/06, Mischa K. [email protected] wrote:

Ken K. wrote:

A client of mine has a ton of data stored in Excel spreadsheets. I’m
building a web application for her (backed by a real db of course) and
want
to import all that data. Is there a library available to read data from
Excel? Is there an easy way to upload the spreadsheets and then read
the
data?

The easiest way to go about this is using the OLE DB driver in windows
to extract the data trough SQL.

Another way would be windows automation to save the data in the
worksheets / export the files to xml.

Both require a windows platform though.


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

It is only labor intensive once though…I had to do this exact same
thing, and while it took time to get all the data extracted, lined up
properly, and inserted, you never look back.

  • Nic.