This week's book giveaway is in the Mac OS forum.
We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell on-line!
See this thread for details.
The moose likes Other Open Source Projects and the fly likes Copy excel file to access table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Products » Other Open Source Projects
Bookmark "Copy excel file to access table" Watch "Copy excel file to access table" New topic
Author

Copy excel file to access table

sonia arora
Ranch Hand

Joined: Mar 10, 2010
Posts: 31
How can i copy a excel sheet data to a access table from java code?
Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2902
    
    1

http://poi.apache.org/spreadsheet/index.html


[LEARNING bLOG] | [Freelance Web Designer] | [and "Rohan" is part of my surname]
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42061
    
  64
POI only helps with reading an Excel file; you'll also need to set up an ODBC datasource for Access. Then you can use the JDBC API to write to the DB.


Ping & DNS - my free Android networking tools app
sonia arora
Ranch Hand

Joined: Mar 10, 2010
Posts: 31
Actually I am developing a java application in which I want to develop an import utility that will use JFileChooser, so how can I import Excel file appended to Access database.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42061
    
  64
POI and JDBC, respectively, solve those two problems. Have you read the web sites we pointed you to yet? Do you understand how they work? If not, what specifically are you unclear about?

(I don't understand what JFileChooser has to do with reading an Excel file, or with writing to a DB, so I can't speak to that.)
sonia arora
Ranch Hand

Joined: Mar 10, 2010
Posts: 31
yes i have read the mentioned sites.
i am using jexcelapi for handling excel instead of POI.
and to connect with access i am using JDBC.

But the issue is that the database is in access and i want to develop a utility in my tool,
such that when ever one wants to add/update many records at a time, the user would not add each record by the GUI individually,
instead just import the excel and records get copied into access.

so i am thinking of using a JFileChooser to browse the excel file and then on click of a button to do the import thats copying of data to access.
so if any one has any idea about how to do so.
thanks...
sonia arora
Ranch Hand

Joined: Mar 10, 2010
Posts: 31
i can use JXL to copy data from one excel to another but can i copy excel to access?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42061
    
  64
If you can read data from an Excel sheet, then you can use JDBC to insert it into a DB, no?
sonia arora
Ranch Hand

Joined: Mar 10, 2010
Posts: 31
i am able to read data in form of object of jexcelapi i.e sheet
eg:import java.io.File;
import java.util.Date;
import jxl.*;

...

Workbook workbook = Workbook.getWorkbook(new File("myfile.xls"));
Sheet sheet = workbook.getSheet(0);



so how do i pass this sheet into an access db existing table that is how to build the JDBC SQL query which picks data from excel to access
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42061
    
  64
You can't. You need to iterate over the cell contents of the sheet, and then issue INSERT statements to the DB as appropriate.
 
GeeCON Prague 2014
 
subject: Copy excel file to access table