Recent Pos

Using JDBC To Access Excel Sheet CSV and Text file

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmail

excel csv text file using jdbc

Using JDBC to access the Microsoft Excel Sheet:

⇒ In this case we should use the JDBC ODBC bridge driver.
 
⇒ This is same as accessing the database like Oracle. We just need to consider the Excel Sheet name in the workbook followed by ‘$’, enclosed by “[]” (Square braces) taken as table name.
 
⇒ We need to create one DSN(Data Source Name) by following respective system’s documentation. For example in windows we can create DSN by opening Control Control Panel\All Control Panel Items\Administrative Tools\Data Sources (ODBC). A window will open where we can create our DSN.

⇒ To create the DSN for current user only then under “User DSN” tab select the appropriate “User data sources” from the list and then click on Add button available at the right side. Then select the appropriate driver from the list. Select your file and finish.
 
Example:

⇒ If the Excel sheet name is “userdetails” then the table name will be “[userdetails$]”.

 
 

Using JDBC to access the Comma Separated Values (.csv/.txt) file:

⇒ In this case the .txt/.csv file will have each line considered as one row. The first line is considered as column_name, and followed by the rows content.

⇒ The Columns are separated by ‘,’ (Comma) character.
 
Example:

uname,pass
user1,user1pass
user2,user2pass

⇒ In this case we should take the file name enclosed into square braces “[]” as a table name.

e.g.
select * from [userdetails.txt]
 

AccessingTextFile.java

 
 
Download this example:ODBC-csv-xls-txt.zip
 
 
<-Previous: Closing JDBC Connection Statement and ResultSet | | Next->JDBC Batch Processing (Batch insert, update and delete)

No Comments

Post a Comment