wood burning stoves 2.0*
The moose likes JDBC and the fly likes JDBC or ODBC and Excel Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC or ODBC and Excel" Watch "JDBC or ODBC and Excel" New topic
Author

JDBC or ODBC and Excel

C. Patty
Greenhorn

Joined: Oct 03, 2003
Posts: 3
Hello,
Help!
I am trying to connect an Excel 2000 to ODBC or whatever I need to use.
Specifically, here is the data needed:
1)DB connection URL
2)Driver file - where do I get this?
3)Driver class
4)Username
5)password
6)catalog
7)schema
Any help would be greatly appreciated!!!
Thank you.
Patty
Joe Ess
Bartender

Joined: Oct 29, 2001
Posts: 8927
    
    9

Check out this Javaworld article. It should help you get started.


"blabbing like a narcissistic fool with a superiority complex" ~ N.A.
[How To Ask Questions On JavaRanch]
Cindy Glass
"The Hood"
Sheriff

Joined: Sep 29, 2000
Posts: 8521
Moved to the JDBC/ODBC forum.


"JavaRanch, where the deer and the Certified play" - David O'Meara
C. Patty
Greenhorn

Joined: Oct 03, 2003
Posts: 3
Joe,
Thanks a lot!
Patty
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

Patty C.
Hi, and Welcome to Javaranch.
Please change your Display Name to conform to our Naming Policy.
Last Name initials are not allowed. However, first name initials are, so you could change it to C. Patty, for example.
Thank you.
[ October 06, 2003: Message edited by: Gregg Bolinger ]

GenRocket - Experts at Building Test Data
C. Patty
Greenhorn

Joined: Oct 03, 2003
Posts: 3
Joe,
I looked at the article, but am confused about something:
The name of the Excel file is qa.xls, but it often refers to qa-list. Why does it add the -list on the end?
I am actually not working with Java code, but a tool instead that connects to a database and retreives it's structure.
1)It asks for a DB Connection URL. From the example, I would use
jdbc dbc:myExcelSheet ???
(What do I use if my spreadsheet file is myExcelSheet and the worksheet is "myExcelWorksheet"?)
2)It asks for you to browse to a jar file to be the driver. I saw how to "set-up" the driver, but I don't have anything tangible like a jar file to point to (browse to a jar file).
3)for driver class, I can use sun.jdbc.odbc.JdbcOdbcDriver it looks like from the code.
4)username and password, from the code, appear to be a blank space.
5)is also asks for schema and catalog when you do the reverse.
Thanks in advance for the help.
Patty
Sarath Mohan
Ranch Hand

Joined: Mar 17, 2001
Posts: 213
Hi Patty,
It worked perfectly.
I will clear your doubt.
First of all.. qa-list is the ODBC name you have to give when you create the in Windows (2000).
The steps are
1. Click Start > Settings > Control Panel > Administrative Tool >Data Sources
2. It will open a window ODBC data souce Administrator
3. Select User DSN tab. If you want to acces from different machine use System DSN
4. Select Excel Files
5. Click add
6. Select Driver do for MicroSoft Excel
7. Click on Finish
8. Give datasource name... Here you can give qa-list as suggested in the article
9. You can skip description
10. Select workbook in your system You can use myExcelSheet stored in your system
11. Click OK.
Now you have added successfully an ODBC DSN for MS_Excel.. Here it's name is qa-list
Now copy the class file from the javaworld site.
Open the XL file (I hope it is qa.xls) and check the work sheet name
or
You can use [myExcelWorksheet$]. If you use your own worksheet... change the query accordingly...
(URL is the column header name used in the sample.. Change accordingly)

Save, compile and execute...
I hope this will help you
You will get the


Sarath Mohan
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC or ODBC and Excel