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
asked Aug 12, 2013 at 6:56
-
There are many solutions to access excel from Java. Apache POI is something I useJayan– Jayan2013-08-12 07:02:45 +00:00Commented Aug 12, 2013 at 7:02
-
1The 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 onceAbhishek Singh– Abhishek Singh2013-08-12 07:29:30 +00:00Commented Aug 12, 2013 at 7:29
Add a comment
|
3 Answers
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, 2013 at 7:08
3 Comments
Abhishek Singh
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
Pandiyan Cool
Provide default driver also
Abhishek Singh
What do you mean by default driver ?
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.
3 Comments
Abhishek Singh
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
Tarik
15MB is peanuts. Change the VM settings to allow for more heap and/or stack space.
Abhishek Singh
The system i am developing is to be used by different users on different machine. I dont find it as correct approach
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");
