#1
  1. No Profile Picture
    Mentat of IX
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Bucuresti / Toronto
    Posts
    112
    Rep Power
    12

    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. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    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.
  4. #3
  5. No Profile Picture
    Mentat of IX
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Bucuresti / Toronto
    Posts
    112
    Rep Power
    12
    so there is no way of dooing this with a single FreeBSD application server ?
  6. #4
  7. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    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.
  8. #5
  9. No Profile Picture
    Mentat of IX
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Bucuresti / Toronto
    Posts
    112
    Rep Power
    12
    many thanks , it looks like u've sent me in a few good directions ...

    i'll let you know how it all went
  10. #6
  11. No Profile Picture
    Mentat of IX
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Bucuresti / Toronto
    Posts
    112
    Rep Power
    12
    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 ?
  12. #7
  13. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
  14. #8
  15. No Profile Picture
    Mentat of IX
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Bucuresti / Toronto
    Posts
    112
    Rep Power
    12
    thanks a lot i'll keep you posted
  16. #9
  17. No Profile Picture
    Mentat of IX
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Bucuresti / Toronto
    Posts
    112
    Rep Power
    12
    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 01:51 PM.
  18. #10
  19. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
  20. #11
  21. No Profile Picture
    Mentat of IX
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Bucuresti / Toronto
    Posts
    112
    Rep Power
    12
    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
  22. #12
  23. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
  24. #13
  25. No Profile Picture
    Mentat of IX
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Bucuresti / Toronto
    Posts
    112
    Rep Power
    12

    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
  26. #14
  27. No Profile Picture
    Mentat of IX
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Bucuresti / Toronto
    Posts
    112
    Rep Power
    12
    btw pabloj , your links are great , I've installed fabForce and it looks really nice , i'll get to use it quite a bit over this weekend
  28. #15

IMN logo majestic logo threadwatch logo seochat tools logo