Best tool to build database from spreadsheet?

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
tabletguy
Posts: 6
Joined: 2013-01-09 15:20

Best tool to build database from spreadsheet?

Post by tabletguy » 2013-04-06 06:48

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

Johnk
AppGini Super Hero
AppGini Super Hero
Posts: 68
Joined: 2013-01-09 03:47
Location: Cairns, Australia

Re: Best tool to build database from spreadsheet?

Post by Johnk » 2013-04-07 09:40

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

tabletguy
Posts: 6
Joined: 2013-01-09 15:20

Re: Best tool to build database from spreadsheet?

Post by tabletguy » 2013-04-07 09:45

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"

tabletguy
Posts: 6
Joined: 2013-01-09 15:20

Re: Best tool to build database from spreadsheet?

Post by tabletguy » 2013-04-07 10:09

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.

Johnk
AppGini Super Hero
AppGini Super Hero
Posts: 68
Joined: 2013-01-09 03:47
Location: Cairns, Australia

Re: Best tool to build database from spreadsheet?

Post by Johnk » 2013-04-08 11:13

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

tabletguy
Posts: 6
Joined: 2013-01-09 15:20

Re: Best tool to build database from spreadsheet?

Post by tabletguy » 2013-04-09 12:00

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.

User avatar
toconnell
Veteran Member
Posts: 204
Joined: 2013-04-09 19:29
Location: Oklahoma City, OK
Contact:

Re: Best tool to build database from spreadsheet?

Post by toconnell » 2013-04-25 17:11

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

Post Reply