Does anyone have a recommendation for building a new database (multiple tables with primary keys, indexes, etc) from one or more Excel worksheet pages?
I have a complex spreadsheet which a non-profit has been using for 10+ years to track audio lectures by the main teacher (Yoga school in India) as well as visiting lecturers.
The same spreadsheet, different worksheet, tracks which CDs, DVDs, etc. they've produced, and under different sets, etc.
I did a quick search, but didn't see anything related to building databases from spreadsheets here.
I'm doing the work for free, and "basically" unemployed, so I'd prefer a free solution.
Thanks in advance,
Stephan Hodges
Best tool to build database from spreadsheet?
Re: Best tool to build database from spreadsheet?
Hi Stephen,
because I'm a moderator on this forum I really can't undersell AppGini. However, there are a couple of freebie programs out there, but they're very, very difficult to use, So much so, I've completely dumped them and stuck with AppGini.
When looking for anything free on the Internet, always include the words "open source". free often means "free to download, money to use." Open Source means the code is free and usually the program is also.
AppGini has a decent trial period and it's pretty cheap to purchase. Consider asking the end user to pay the few dollars it costs and offer ongoing service as an incentive. Even a Club will buy that one.
To answer your question, AppGini does what you ask and it does it extremely well. You just need to be patient and practice before you leap.
Firstly, create a project using field names in the same sequence as your spreadsheet. If you have a spreadsheet field called Cost of Goods Sold, use "cogs" in Appgini. You can't use multiple words unless you use an undersocre: cost_of_goods_sold or costofgoodssold (messy!)
Just remember that fields a,b,c,d in your spreadsheet must appear as fields a,b,c,d in AppGini otherwise the wrong data will end up in the wrong field. You can change the order in your AppGini script later.
Save your spreadsheet data as delimited data. I prefer ',' as a delimiter. You should find this option in the Save As section of your spreadsheet.
When you have created your AppGini database and ran it for the first time, one of the options is to import data. Click that option and choose the same delimiter. AppGinni will import the data and propagate the database. Of course you have to tell AppGini which will be the key fields during development.
Also with AppGini you have lookups etc. If your spreadsheet has a list of users and a list of DVD's, Create the DVD table first and get it running. Then the Users table and get it running also. Then you can begin building lookups. As far as I know, AppGini can't import data from multiple tables in one hit. Do each one separately then add the bells and whistles..
As I said in another post, I'm moving house but I think all this is covered in the help files.
Let us know how you go.
John
because I'm a moderator on this forum I really can't undersell AppGini. However, there are a couple of freebie programs out there, but they're very, very difficult to use, So much so, I've completely dumped them and stuck with AppGini.
When looking for anything free on the Internet, always include the words "open source". free often means "free to download, money to use." Open Source means the code is free and usually the program is also.
AppGini has a decent trial period and it's pretty cheap to purchase. Consider asking the end user to pay the few dollars it costs and offer ongoing service as an incentive. Even a Club will buy that one.
To answer your question, AppGini does what you ask and it does it extremely well. You just need to be patient and practice before you leap.
Firstly, create a project using field names in the same sequence as your spreadsheet. If you have a spreadsheet field called Cost of Goods Sold, use "cogs" in Appgini. You can't use multiple words unless you use an undersocre: cost_of_goods_sold or costofgoodssold (messy!)
Just remember that fields a,b,c,d in your spreadsheet must appear as fields a,b,c,d in AppGini otherwise the wrong data will end up in the wrong field. You can change the order in your AppGini script later.
Save your spreadsheet data as delimited data. I prefer ',' as a delimiter. You should find this option in the Save As section of your spreadsheet.
When you have created your AppGini database and ran it for the first time, one of the options is to import data. Click that option and choose the same delimiter. AppGinni will import the data and propagate the database. Of course you have to tell AppGini which will be the key fields during development.
Also with AppGini you have lookups etc. If your spreadsheet has a list of users and a list of DVD's, Create the DVD table first and get it running. Then the Users table and get it running also. Then you can begin building lookups. As far as I know, AppGini can't import data from multiple tables in one hit. Do each one separately then add the bells and whistles..
As I said in another post, I'm moving house but I think all this is covered in the help files.
Let us know how you go.
John
Re: Best tool to build database from spreadsheet?
Thanks for your reply, and I apologize for leaving the wrong impression.
I've owned AppGini Pro since version 3 (if I recall). I prefer designing the database tables and relationships BEFORE importing and putting together something in AppGini. That's why I was asking how to build the database, not the app.
But, I'm currently unemployed and don't have access to Erwin, etc.
I was hoping to find something that could jump start the process from an Excel spreadsheet.
I've lots of experience in searching for freeware - haha. I know what you mean about how everyone puts the word "free" into every possible description, even if it's just a "free download"
I've owned AppGini Pro since version 3 (if I recall). I prefer designing the database tables and relationships BEFORE importing and putting together something in AppGini. That's why I was asking how to build the database, not the app.
But, I'm currently unemployed and don't have access to Erwin, etc.
I was hoping to find something that could jump start the process from an Excel spreadsheet.
I've lots of experience in searching for freeware - haha. I know what you mean about how everyone puts the word "free" into every possible description, even if it's just a "free download"
Re: Best tool to build database from spreadsheet?
I guess I should clarify a bit.
I was looking for something to help with creating MULTIPLE tables from a single worksheet. There's lots of duplicate data, for example, in some of the columns.
I was looking for something to help with creating MULTIPLE tables from a single worksheet. There's lots of duplicate data, for example, in some of the columns.
Re: Best tool to build database from spreadsheet?
Regarding a modeller, have you seen these?
http://argouml-db.tigris.org/
http://fabforce.net/dbdesigner4/
If you decide to try them, we'd love to hear your comments.
John
http://argouml-db.tigris.org/
http://fabforce.net/dbdesigner4/
If you decide to try them, we'd love to hear your comments.
John
Re: Best tool to build database from spreadsheet?
Thanks, I had lost the dbdesigner4 link. I used to use it maybe 6-8 years ago(?)
Then, it was bought out and went away for a while, and then came back...
I'll check them both out.
Currently, I'm trying out Oracle's MySql Workbench tool.
Then, it was bought out and went away for a while, and then came back...
I'll check them both out.
Currently, I'm trying out Oracle's MySql Workbench tool.
Re: Best tool to build database from spreadsheet?
I have checked out them all.. AppGini is as close to free as your going to get for a program that will do it right!
Tina O'Connell
Web Dev & Appgini FAN
Web Dev & Appgini FAN