db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Segel <de...@segel.com>
Subject Re: Import data directly from Excel?
Date Fri, 31 Mar 2006 19:21:16 GMT
On Thursday 30 March 2006 3:43 pm, Rajesh Kartha wrote:
> Carol Gloyd wrote:
> > Hi,
> >
> > I’m new to Derby and was wondering if there is any way to directly
> > import an Excel spreadsheet into a Derby table. If this isn’t
> > possible, is there a work-around?
> >
> > Thanks!
The quick answer is no.
Now for the longer answer...

> The first thing that came to my mind was to export the Excel spreadsheet
> as a CSV (Comma Separated) file and use the
> Derby Import utilities to load it into the database table created
> (manually).
Yes, you can convert the .xls spreadsheet to a CSV format. In this format, the 
first row is your "header" or column row.
Note that all data is "text" data and is surrounded by ""s.
As to parsing the data, there's a bit of a snag.
Java's native string token parser ignores the \"s so that if you have a number 
1,499.99 for example,  it will get parsed as two separate fields. So you're 
going to have to write your own. (Ok, so its a trivial problem...)

The negative thing about CSVs also include the fact that you no longer have a 
viable excel spread sheet. So you'll have to "export" your spreadsheet to a 
csv to work with.

Another option is to use a new class of objects that allow you to work with 
native Microsoft data structures. 
Again, here, you'll have to write your own code...

The bottom line, you'll have some work to do to so its not a simple "drag and 
drop" type of activity....

> More info at:
> http://db.apache.org/derby/docs/10.1/tools/
> The other option is to to use some API like Apache Jakarta POI :
> http://jakarta.apache.org/poi/
> I have not used this, but a search on the web turned up some pointers:
> http://forum.java.sun.com/thread.jspa?threadID=445015&start=0
> Hope this helps.
> -Rajesh

Michael Segel
Michael Segel Consulting Corp.
(312) 952-8175 [mobile]

View raw message