Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to write into excel sheet

 
sachin naphade
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
I am reading excel file as a database by configuring it as the Db using Administrative tool,i made all the configuration there, I am able to read it without any problem but I want to write some data to excel sheet for that i used one update statement but when i compile the code it gives me syntax error in the update statement. The code llok like this

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc dbc:exceltest" );
Statement st = con.createStatement();
ResultSet rs = st.executeQuery( "Select * from [Unreachable URLs$]" );
int statusCode=1;
rs.next();
while (rs.next()) {
String url = rs.getString(2);
String sql = "Insert into [Unreachable URLs$] (Status code)";
sql=sql +" values('" + statusCode +" ')";
st.executeUpdate(sql);
statusCode++;
}

where Unreachable URLs is the name of the spreadsheet

this spreadsheet has coloumn names as SrNo, URL,Description,Status code.
i want to insert data into Status code coloumn which has URL same as url which is read here.and the exception that I got is
Exception: [Microsoft][ODBC Excel Driver] Syntax error in UPDATE statement.

I also tried with
String sql = "Insert into [Unreachable URLs$] (Status code)where URL=url";
but still i am getting same exception.
 
Ulf Dittmer
Rancher
Posts: 42968
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
With SQL it's not a good idea to have spaces in attribute names, like "Status code". Try quoting it, like "('Status code')".
 
Jesper de Jong
Java Cowboy
Saloon Keeper
Pie
Posts: 15438
41
Android IntelliJ IDE Java Scala Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This does not sound like it has anything to do with the SCJP exam. I'm moving this to a more appropriate forum.
 
Amit Suresh Pawar
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi.........

This exception is because of opening that file in read only mode.
So, do one of the below:

1. Start-> Settings->Control Panel->Administrative Tools->Data Sources (ODBC) Then select your driver & click on configure, Then click on Options & uncheck the Read-Only option
2. connection = DriverManager.getConnection( "jdbcdbcriver={Microsoft Excel Driver (*.xls)};DBQ=C://Java_Excel//tip_of_the_day.xls; readOnly= false");

It will work.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic