How to Connect to an Excel Spreadsheet using JDBC in Java

**JavaPF** · April 22nd, 2009, 06:59 AM

This tutorial will show you how to connect to and read data from an Excel spreadsheet using JDBC.

To start, we need to setup the local ODBC connection.

Navigate to your computers Control Panel and locate the Administrative Tools.

Once in the Administrative Tools section, locate Data Sources (ODBC)

The ODBC Data Source Administor menu will open

Select the System DSN tab and click Add

Find Driver do Microsoft Excel(*.xls) from the list and click Finish

Give the Data Source Name & Description

Next, click Select Workbook and locate the spreadsheet you wish to use

In this case, we are using worcester.xls. Select it and click OK.

Click OK again to exit the setup. The ODBC connection is now complete.

Now that the ODBC connection is setup, its time for the Java code.

import java.sql.*;
 
public class ExcelODBC {
 
    public static void main(String[] args) {
 
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection con = DriverManager.getConnection("jdbc:odbc:[B]worcester[/B]");
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("Select * from [Sheet1$]");
 
            ResultSetMetaData rsmd = rs.getMetaData();
            int numberOfColumns = rsmd.getColumnCount();
 
            while (rs.next()) {
 
                for (int i = 1; i <= numberOfColumns; i++) {
                    if (i > 1)
                        System.out.print(", ");
                    String columnValue = rs.getString(i);
                    System.out.print(columnValue);
                }
                System.out.println("");
            }
 
            st.close();
            con.close();
 
        } catch (Exception ex) {
            System.err.print("Exception: ");
            System.err.println(ex.getMessage());
        }
    }
}

Notice the bold worcester. This part of the code needs to match your ODBC Data Source Name.

ResultSet rs = st.executeQuery("Select * from [Sheet1$]");

This part of the code allows you to query the excel spreadsheet with SQL. This is a simple SQL query to select everything from Sheet1 in the spreadsheet.

If you are unfamiliar with SQL commands then there is a quick tutorial here:

A Gentle Introduction to SQL

Thats it! You can now connect to your Excel Spreadsheet and print its content to the console.

**JavaPF** · April 22nd, 2009, 07:08 AM

Please Note: This code will not print the first row in the spreadsheet as the jdbc-bridge assumes the first row to be akin to column names in the database.

You can use the following code to print the column names (which is the first row of the spread sheet).

                for (int i = 1; i <= numberOfColumns; i++) {
                    if (i > 1) System.out.print(", ");
                    String columnName = rsmd.getColumnName(i);
                    System.out.print(columnName);
                }
                    System.out.println("");

**Devaraj** · April 30th, 2010, 02:36 PM

I used the above given code to test and it works fine. But when i tried to use INSERT query , i get "java.sql.SQLException:[Microsoft][ODBC excel Driver] Operation must use an updateable query" exception message. Please help me to insert data. Please find the code below.

try {
conn=DriverManager.getConnection("jdbcdbc:Bookin g");
stmt=conn.createStatement();
sql="insert into [Sheet1$]([USERID],[FIRST_NAME],[LAST_NAME]) values('123456','Murugan','Saravanan')";
pS=conn.prepareStatement(sql);
pS.execute();
 
}

Thanks,
Dev.

**JavaPF** · May 1st, 2010, 10:16 AM

Hello Devaraj,

Welcome to the forums

Try this:

Follow the tutorial through to the part where you select the driver. Click Configure then click on Options and then uncheck the Read Only option.

**Devaraj** · May 3rd, 2010, 03:09 PM

Thank you.

Now i am able to insert data into the excel.

Dev.

**Mehul** · March 23rd, 2011, 03:21 PM

I work on Excel all day long and query 100s of sheets everyday . Is it possible to reuse the same connection somehow ? Or for each workbook I will need to have hundreds of excel connections ?

**JavaPF** · March 28th, 2011, 06:37 AM

Originally Posted by Mehul

I work on Excel all day long and query 100s of sheets everyday . Is it possible to reuse the same connection somehow ? Or for each workbook I will need to have hundreds of excel connections ?

I believe you will need to setup an ODBC connection for each spread sheet.

**zeddarn** · June 21st, 2011, 02:12 AM

Originally Posted by Devaraj

I used the above given code to test and it works fine. But when i tried to use INSERT query , i get "java.sql.SQLException:[Microsoft][ODBC excel Driver] Operation must use an updateable query" exception message. Please help me to insert data. Please find the code below.

try {
conn=DriverManager.getConnection("jdbcdbc:Bookin g");
stmt=conn.createStatement();
sql="insert into [Sheet1$]([USERID],[FIRST_NAME],[LAST_NAME]) values('123456','Murugan','Saravanan')";
pS=conn.prepareStatement(sql);
pS.execute();
 
}

Thanks,
Dev.

Try using executeUpdate(); Insert, update, delete use executeUpdate(). On select use executeQuery() .

**kinjan** · December 21st, 2011, 01:46 AM

Hello ,
My name is Kinjan Ajudiya.
I am new to this forum.
I have question about reading excel file in java.
I have implemented application which read excel file and store it in database.
But this application is limited to single change only.
Means ,if i want to read that file continuously then i cant do in my application.
I want to do such thing, one excel file is currently running.
First my application will read excel file .then change is made to excel file .Say some cell is modified.
Now in next request of file reading i should get that modified cell value .
Is this possible ?
If yes then please let me know ...

Thanks
Kinjan

**phatdogg** · August 18th, 2012, 10:55 PM

I found this information very useful, and thank you for sharing. I do have a question I am trying to pull data from Access but the information is stored on a web based application and to get the data I need I have to create a template using word, I can get some of the fields to work but not all of them. This is a project for work and I am trying to get this done and having little luck. Would you be able to give some advice? Or any help would be greatly appreciated.
Thank you

**JavaPF** · August 20th, 2012, 04:37 PM

Hello phatdogg,

Is this work project written in Java? If so, I suggest you start a new thread about it.
You will get a better response.

**veluruanil** · September 24th, 2012, 04:07 AM

Hi,

i have tried this but i'm facing an issue with it, got an error
Exception: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

can some one please help me to resolve this

**JavaPF** · October 3rd, 2012, 11:45 AM

Hello veluruanil.

Please try here. There is lots of information on this error:

Exception: [Microsoft][ODBC Driver Manager]

**nagashree** · March 10th, 2013, 12:50 AM

Originally Posted by JavaPF

Please Note: This code will not print the first row in the spreadsheet as the jdbc-bridge assumes the first row to be akin to column names in the database.

You can use the following code to print the column names (which is the first row of the spread sheet).

                for (int i = 1; i <= numberOfColumns; i++) {
                    if (i > 1) System.out.print(", ");
                    String columnName = rsmd.getColumnName(i);
                    System.out.print(columnName);
                }
                    System.out.println("");

hello sir.

The above mentioned way of selecting the excel sheet is unavailable in vista. I tries configuring it, but no use... Is there a way out to do it directly from netbeans inbuilt server... could you plz help me with that

**javeshwart** · August 27th, 2013, 11:57 AM

hi cn you explain the line
Connection con = DriverManager.getConnection("jdbc

dbc:worcester");

i don't understand what is the purpose of

Thread: How to Connect to an Excel Spreadsheet using JDBC in Java

LinkBack

Thread Tools

Display

How to Connect to an Excel Spreadsheet using JDBC in Java

Related threads:

Re: How to Connect to an Excel Spreadsheet using ODBC in Java

Re: How to Connect to an Excel Spreadsheet using JDBC in Java

Re: How to Connect to an Excel Spreadsheet using JDBC in Java

Re: How to Connect to an Excel Spreadsheet using JDBC in Java

Re: How to Connect to an Excel Spreadsheet using JDBC in Java

Re: How to Connect to an Excel Spreadsheet using JDBC in Java

Re: How to Connect to an Excel Spreadsheet using JDBC in Java

The Following User Says Thank You to zeddarn For This Useful Post:

Re: How to Connect to an Excel Spreadsheet using JDBC in Java

Re: How to Connect to an Excel Spreadsheet using JDBC in Java

Re: How to Connect to an Excel Spreadsheet using JDBC in Java

Re: How to Connect to an Excel Spreadsheet using JDBC in Java

Re: How to Connect to an Excel Spreadsheet using JDBC in Java

Re: How to Connect to an Excel Spreadsheet using ODBC in Java

Re: How to Connect to an Excel Spreadsheet using JDBC in Java

Similar Threads

Export to excel

Tags for this Thread