Tell me more ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.
up vote 0 down vote favorite
1

I'm working on some project where I have to make a database for data which is currently kept in excel files, so I have to do some changes before writing the data into the database. I'm going to read those files using Java, and write them from Java into the db. Should I use Hibernate for that job? Or how else can I connect to those Excel sheets, read data in Java, with database?

I'm new in this, so I don't have any experience. Also english isn't my native language, so I'm sorry because of grammar errors.

share|improve this question
You might also want to take a look at jexcelapi.sourceforge.net – GreyBeardedGeek Jul 21 '12 at 3:33
It helps to mention the version of Excel. If it's a newer version, using openxml then take a look at the spec, it's basically a zipfile with an easy parsable xml file inside. – Pieter B Jul 22 '12 at 10:45
Is the data that is in the file raw delimited data? or is it something that has functions? Are the data files .xls? or are they .csv (many systems associated .csv with excel)? – MichaelT Feb 4 at 14:40

3 Answers

up vote 2 down vote

LibreOffice and OpenOffice could scale you to the moon. Headless Linux-Mac-Win. You can get the contents of spreadsheet with about 200 lines of code.

Use the OpenOffice.org (or LibreOffice) APIs to open the spreadsheet files, access the data, perform the transformations, and then write the proper data into the new database.

As long as your are not trying to do fancy things like inserting and updating formulas it's very simple and cross platform and because it's open source you can rack as many instances as you need. It opens up xls files by converting them to oxd. In Linux you can run in headless mode and if you multi procs you can startup multi OpenOffice daemons to process multi docs at once. It's not marketed much as the "pro" solution, many instantly frown down on it. But I spent many years working on it starting from 2003 building a platform that printed millions impressions a year and 90% of the code is still running.

We started the implemention with 1.0 and as of last year they are using 3.x version. We didn't have to upgrade any code to adjust for the upgrade. You don't need any other XML API that's version-dependent you won't need to worry about comparability Open Office 3.x will open 97-2007 xls. I recommend this because I was a Platfrom R&D analyst and Java dev. for 8 years and I spent better part of three years researching this solution agents others.

share|improve this answer
do you suggest to use the OpenOffice.org (or LibreOffice) APIs to open the spreadsheet files, access the data, perform the transformations, and then write the proper data into the new database? – gnat Jul 22 '12 at 10:37
Yes. Long as your are not trying to do fancy thing inserting and updating formulas it's very simple and cross platform and because it's opens source you can rack as many instances as you need. It open up xls files with converting them to oxd. You can run in Linux in headless mode and if you multi procs you can startup multi oo daemons to process multi docs at once. It's not marketed much as the "pro" solutions many instantly frown down on it. But I spent many working on it starting from 2003 building a platform that printed millions impressions a year and 90% of the code is still running. – Pareshkumar Jul 22 '12 at 19:43
Oh and we started the implemention with 1.0 and as of last year they are using 3.x version. We didn't have to upgrade any code to adjust for the upgrade. You don't need any other XML API that's version dependent you won't need to worry about comparability oo 3.x will open 97-2007 xls. I recconend this because I was a Platfrom R&D analyst and Java dev. For 8 years and I spent better part of three years researching this solution agents others. – Pareshkumar Jul 22 '12 at 20:01
1  
+1 after @gnat edited the question. Nice save! :-) – Dynamic Jul 23 '12 at 14:47
up vote 0 down vote

If I were you, I wouldn't use dedicated code for that.

Prefer a tool like ETL (Talend, kettle, by example).

It will be more easy to parse your file and you will have lot of problems (compatibility, maintainability, etc.) already solved.

share|improve this answer
up vote 4 down vote

It's possible to query Excel sheets using SQL with ODBC. The sheets are treated like tables. You can even join the sheets like you would database tables! I'd recommend you try this option first. Google java + ODBC + excel for details.

There is apache's POI libary. http://poi.apache.org/ I'm not sure if this works on the old "binary" format or just the new XML format of newer versions of excel. But I remember someone I worked with used some sort of apache libarry to work with binary-format excel sheets a few years back.

In .NET you can use the excel interop library to get the data out of the excel sheet (as well as do anything else that you could possibly do in excel).

Once you've extracted the data from excel, you can load it into the database how you normally would.

share|improve this answer
2  
The POI library works extremely well with the older binary formats! – James Anderson Jul 23 '12 at 1:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.