Reading from excel file and transffering to database

[dakkarin]

Hi i want to desing a program that will read rows and columns in a excel file and then take the data and insert it into database is something like that possible with borland
i already knew the database part but i dont know how can i take datas from excel file

Liars Do Not Fear The Truth If There Are Enough Liars

 

[TonyGroves]

Since the Excel file format is kept secret by Microsoft, extracting data from an Excel file is not straightforward. It has been reverse-engineered by the OpenOffice.org team among others, so you could in theory use some of their source code (I think it's allowed by their LGPL licence) to analyse an Excel file, but I can't see it being easy.

Maybe instead you should encourage your users to use a more open file format, like that used by OpenOffice.org, which is simple compressed XML.

 

[dakkarin]

how can i take it from opeen office anyway?

Liars Do Not Fear The Truth If There Are Enough Liars

 

[TonyGroves]

You would download the source code, extract the bit that parses an Excel file, and use it to write your own routine. Again, you would want to check that that use is allowed by the licence.

 

[2ffat]

My BCB6 Developer version has some components for MS Office. I've never used them, though. Have you tried ADO to get into Excel?


James P. Cottingham
-----------------------------------------
[sup]I'm number 1,229!
I'm number 1,229![/sup]

 

[dakkarin]

yeah my bcb5 has some office components too but i dont know how to use it
is there any tutorial about them

Liars Do Not Fear The Truth If There Are Enough Liars

 

[Ginka]

I know a way to do that, but... well check it and decide by yourself

Save the excel worksheet as a MS-DOS (*.csv) comma separated document, then you have a clear way to read the data.

Example
if you have this
Mike 12 5.45
Jean 23 6.32

when you save the file you will have this

"Mike", 12, 5.45
"Jean", 23, 6.32

 

[dakkarin]

thanks its a very brilliant idea i will use it
but now i have a problem about inserting that datas into database i pulled filmname and cdnumber variables from excel files
but when i try to insert them into my database like this

Query1->SQL->Add("INSERT into divx SET Filmadi='"+filmname+"',Cdno='"+cdnumber+"'");

it gives the error
"Invalid use of Keyword Token=SET"

what is wrong with that it was working with mysql why it doesnt work with borlands own db enviroment
Thanks

Liars Do Not Fear The Truth If There Are Enough Liars

 

[jmmmx]

I Think SET is for UPDATE, you need:
Query1->SQL->Add("INSERT into divx (Filmadi,Cdno) VALUES ('"+filmname+"', '"+cdnumber+"')");

 

[dakkarin]

Thanks so much

Liars Do Not Fear The Truth If There Are Enough Liars