Hi, I have a requirement to import an excel spreadsheet on a regular basis
to a database file.
e.g when product cost prices change for a stock database I need to be able to
pass these new prices changes from the excel file into the database.
I currently have a stock database populated into a TDBAdvGrid
I then use TAdvGridExcelIO function to import the excel data into the TDBAdvGrid.
but the database does not get updated automatically with the new data.
1. Do I need to copy the newly imported grid data to the database again
or is there an automatic way of doing this?
2. Is there a better way to achieve this using tms grid / flexcel ?
Thanks
Hi,
I am not really expert in DBAdvGrid, so maybe someone more informed from TMS answer this one, but anyway I'd like to ask for some clarifications.
Your workflow seems to be:
1) you import some xls file into the grid
2) you commit the changes from the dbgrid to the database, but the database doesn't get updated.
Is this it?
About your second question, it depends if you need the grid. If you need to show the grid to the user so he can change/edit values or preview the changes, then I would go with TDBAdvGrid/ExcelIO as you are going now. TAdvGridExcelIO actually uses a cutdown FlexCel 3 version to import the file into the grid, and it does the same things that you would have to do to import it directly with FlexCel.
If you want to use FlexCel 5 (to support reading xlsx) instead of the builtin FlexCel 3 in AdvGridExcelIO, you could use TAdvExcelExport from http://www.tmssoftware.com/site/advgridfilters.asp which is similar to AdvGridExcelIO, but it uses the installed FlexCel.
Now, if you don't need to show the grid at all and are just using it to import the data into the db, then yes, using FlexCel alone instead of AdvGrid/AdvExcelIO will be better. By using FlexCel alone you skip loading all the file into a grid and then dumping the grid into a database, you just load the data from the xls file directly to the database. You might even import the file at the same time it is loaded to avoid reading it into memory, using virtual mode.
You can look at the "Reading Files" for an example on how to read a file, and "Virtual mode" for an example on how to read the file without loading it into memory. Those examples load the xls file into a grid instead of a db, but changing it to load directly into a TDataset is very easy. If this is what you need (loading the file directly into the db) and you need more help than the mentioned demos let me know and I can send a demo that specifically loads a file into a db.
Hi Adrian
Thanks for the tips.
1. Yes i guess I was looking for an elegant solution to update a database from excel spreadsheets
I assumed that once a grid is linked to a datasource then any changes made to the grid will reflect in the database.
2. Yes I think the demo (file into db ) would be helpful for me if you could kindly send it.
My email: kamranrana@hotmail.com
Kamran
My email: kamranrana@hotmail.com
Kamran
Hi,
Well, I've made an example and uploaded it to http://tmssoftware.net/public/flexcel/samples/demodb.zip
But I am not sure on how helpful it can be. The thing is (and I realized this even more when doing the demo), that the code will depend a lot on the particular db you use, and the tables you want to use, and the way you want to insert the rows.
And the FlexCel part is the easiest, you just use xls.GetCellValue to get the value of a cell. The most complex part is to add this value to the db. In this example I used DBGo with an access db, and every time you click a button, all the records are loaded into the db. This means that this is good for importing into empty tables.
If on the other side you wanted to update records already in the database, you need to define which record is the key, how to handle conflicts, etc. Probably the simplest would be to use a TClientDataset to load the existing table from the db, then read the Excel file using FlexCel and insert or update the records as needed. But as said, the hard work is in the db side, on FlexCel side it is just calling GetCellValue.