• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

java and Excel

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
How can I execute SQL stements - select, insert, update, delete using MS Excel as database from a java application.
 
Ranch Hand
Posts: 1209
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by tushar jain:
How can I execute SQL stements - select, insert, update, delete using MS Excel as database from a java application.


there's a odbc driver for excel as well?
if u want to manipulate excel files directly , u might want to check out www.linar.com (java-com bridge)
karthik.
 
tushar jain
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Dear Kartik,
The fine point is that my company does not want to use JIntegra as mentioned at suggested.
I am able to execute Select query as follows:
User DSN : UserDsn
Excel File name : ExcelFile.xls
Select statement: Select * from [Sheet1$] where ColName=condition
In this query ColName is the content of A1 cell.But if I employ same for the insert, delete and update queries I get error message " Operation must use Updateable query. "
Tushar
 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If you're using Microsoft's ODBC driver for Excel, I've got some bad news for you... no DELETE or UPDATE queries, and INSERT can only append to table. Read this from MS ... Excel ODBC Driver and Text ODBC Driver Notes. There might be 3rd party drivers out there that's more flexible... good luck though
 
edmond so
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
the link to the article Excel ODBC Driver and Text ODBC Driver Notes is http://support.microsoft.com/support/kb/articles/Q178/7/17.ASP
 
tushar jain
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for all,
My company now ready to use some java-com bridge.
tushar
 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Maybe this has come a bit too late and you must've found out the solution. But, any way, I'll share with you what I know.
When creating the DNS click on "Options>>" and make sure to UNCHECK the "Readonly" attribute of the DNS. Then you will be able write to the Excel sheet as well.
Regards,
Sanjay.
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Does anyone know how to get around this problem?
I can connect to an Excel document, and read data from it.
However, I have a big problem: if a column contains a mixture of numeric and alphanumeric data, some of the cells return a null value.
If the first cell contains a number, even though I've formatted the cell as text, it is interpreted as a number (a double, to be precise.)
Then, when the program hits the first cell containing letters as well as numbers, it returns a null value.
If we can fix this, we will be happy as Happy Jack McHappy, Outright Winner of the All-England Happy Championships.
All ideas welcome
TIA
Jay
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can someone send a simple example?
 
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi,
anyone know how to retrive worksheet names from excel file.
i am able to connect and read the data from excell file by passing the worksheet name. now i want to know all the work sheet names.
bye
glkishore
------------------
glkishore,
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic