|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
May 3rd, 2003, 02:34 PM
|
|||
|
|||
|
importing Excel and Access data with JDBC
I'm researching this subject at this time as I am building an application based on Java and PostgreSQL and I would like to be able to import Access and Excel files into it .
I've read about JDBC that it works with ODBC , however i've never had to implement such a thing , and as a matter of fact , i've never worked with any database / developing tools in Windows , nor have I ever connected JDBC to a file as oposed to a database server / service . So I thought it would not hurt to shoot a question here as I continue my research on the matter . Any sugestions are welcome Thanks
|
|
#2
May 4th, 2003, 03:25 AM
|
||||
|
||||
|
For Excel you can use the great POI project to read Excel files and convert them to whatever you want (I have experimented with Excel <-> database insertion and extraction).
Access will be a bit more difficult, you'll need at least a windows PC and Sun jdbc-odbc bridge or RMIJdbc driver.
__________________
My blog Tutorials about OSS databases, DBMonster ... Contribute to OSS Development, fill bug reports! Developer Shed eSupport Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins Random data (with a bias)
|
|
#4
May 4th, 2003, 02:01 PM
|
||||
|
||||
|
Yes, for Excel, probably not for Access.
Don't kow if the jdbc-odbc bridge works for UnixODBC, in that case there is an UnixODBC driver for Access on Sourceforge. It has been used to show records from Access with Php on Linux.
|
|
#6
May 5th, 2003, 12:16 AM
|
|||
|
|||
|
one more thing ...
i understand there is a way to convert an access database into a flat file of some sort ... would there be a way to read that file with jdbc ?
|
|
#7
May 5th, 2003, 02:57 AM
|
||||
|
||||
|
|
#9
May 5th, 2003, 12:49 PM
|
|||
|
|||
|
Hi , surprinsingly after a few hours of play and reading through the drivers source code i've come up with the following ( from my understanding this is preety much all it does ) :
Code:
import java.sql.*;
import java.util.*;
class Conx
{
public static Connection con = null;
public static void main ( String [] args )
{
try {
Class.forName("org.aarboard.jdbc.xls.XlsDriver").newInstance();
con = DriverManager.getConnection("jdbc:aarboard:xls:/java/ExcelImporter/work" );
Statement stmt;
ResultSet rs;
stmt = con.createStatement();
rs = stmt.executeQuery("Select * from m");
rs.next();
System.out.println("This shold not appear here cuz i'm a noob :" +
rs.getString("CatID"));
}
catch ( Exception e ) {
e.printStackTrace();
return;
}
}
}
luckly javac Conx showed me : This shold not appear here cuz i'm a noob :2 Doesn't seem to support types or any other queries other than "select * from xlsfilename" but for me it's enough ... Last edited by zapa : May 5th, 2003 at 12:51 PM.
|
|
#11
May 6th, 2003, 07:35 AM
|
|||
|
|||
|
oops
 Code:
-rw-r--r-- 1 zapa www 31873536 May 5 20:40 ab.xls j# java Conx Exception in thread "main" java.lang.OutOfMemoryError The file has about 100 000 rows each with some 60 columns . Now I'm a noob so I'd have to ask . It looks so far that the file is too big for the drivers 'select * from filename' limitation . Now do you guys have any sugestions ? Is there anything i could do to optimise it somehow and make it work ? Any input would be much appreciated 
|
|
#12
May 6th, 2003, 01:44 PM
|
||||
|
||||
|
Don't know, maybe it's trying to load everything into memory which can be a problem, you might want to try Jakarta POI to manipulate Excel files.
|
|
#13
May 6th, 2003, 02:09 PM
|
|||
|
|||
 yepp I was just going to do that for this one file . Rest of the files i need to work with seem to be fine ... i just wanted to make sure , i'm not dooing anything wrong myself since i'm new to java & jdbc so there is allwais that chance
|
|
| Viewing: Dev Shed Forums > Programming Languages > Java Help > importing Excel and Access data with JDBC |
| Thread Tools | Search this Thread |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
