Connecting to excel sheet using jdbc without specifying DSN to Excel sheet

Question

I want to connect to an excel sheet using jdbc or some other method but i do not want to specify DSN for the same using administrative tool. Is their someway to do it using code? If yes how ?

Thanks in advance

There are many solutions to access excel from Java. Apache POI is something I use — Jayan, Aug 12 '13 at 07:02
The File i am using is more than 15 MB. Using POI gives me out of memory error. Because POI trys to load than entire sheet into JVM at once — Abhishek Singh, Aug 12 '13 at 07:29

score 7 · Accepted Answer · answered Aug 12 '13 at 07:08

7

It is also possible to connect to a spreadsheet without using DSN, which provides a more flexible way within code to point JDBC at an Excel file of interest without the accesses to a client registry to define the required DSN. Without DSN, the db connection is created as following, please not the difference of constructed JDBC URL:

java.sql.DriverManager.getConnection( "jdbc:odbc:Driver={Microsoft Excel Driver
(*.xls)};DBQ=C:/Documents and Settings/myPath/Desktop/qa.xls");

Here DBQ defines the path to the target spreadsheet file (qa.xls). Both backslash and forward slash work well.

Source: Available source

answered Aug 12 '13 at 07:08

Pandiyan Cool

6,381
8
51
87

1

I get the following error java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified I have added the ojdbc jar – Abhishek Singh Aug 12 '13 at 07:46
Provide default driver also – Pandiyan Cool Aug 12 '13 at 08:52
What do you mean by default driver ? – Abhishek Singh Aug 12 '13 at 11:50

score 1 · Answer 2 · answered Aug 12 '13 at 07:11

1

What you are eluding to is a DSN less connection string. See http://support.microsoft.com/kb/165866 for details. Yet, I would opt for Apache POI as mentioned by Jayan 14.

answered Aug 12 '13 at 07:11

Tarik

10,810
2
26
40

The File i am using is more than 15 MB. Using POI gives me out of memory error. Because POI trys to load than entire sheet into JVM at once – Abhishek Singh Aug 12 '13 at 07:31
1

15MB is peanuts. Change the VM settings to allow for more heap and/or stack space. – Tarik Aug 12 '13 at 07:36
The system i am developing is to be used by different users on different machine. I dont find it as correct approach – Abhishek Singh Aug 12 '13 at 07:44

score 0 · Answer 3 · edited Oct 15 '15 at 06:22

try changing the driver name from Microsoft Excel Driver(*.xls) to Driver do Microsoft Excel(*.xls)

java.sql.DriverManager.getConnection("jdbc:odbc:Driver={Driver do Microsoft Excel(*.xls)};DBQ=C:/Documents and Settings/myPath/Desktop/qa.xls");

and if you want to update the excel file use the following connection string:

java.sql.DriverManager.getConnection("jdbc:odbc:Driver={Driver do Microsoft Excel(*.xls)};DBQ=C:/Documents and Settings/myPath/Desktop/qa.xls;ReadOnly=0");

Connecting to excel sheet using jdbc without specifying DSN to Excel sheet

3 Answers3

Linked