Populating a database from a spreadsheet

[gerkintrigg]

Hi everyone. I'm trying to work out how to populate my database from an Excel spreadsheet.

Does anyone have any ideas how I'd go about doing that?

I've seen the tutorials on exporting the sql into excel, but can't begin to figure it out the other way.

Are there any free resourses or tutorials that you know of?

Thanks in advance.

[freakus_maximus]

[!--quoteo(post=367934:date=Apr 24 2006, 08:04 AM:name=gerkintrigg)--][div class=\'quotetop\']QUOTE(gerkintrigg @ Apr 24 2006, 08:04 AM) [snapback]367934[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Hi everyone. I'm trying to work out how to populate my database from an Excel spreadsheet.

Does anyone have any ideas how I'd go about doing that?

I've seen the tutorials on exporting the sql into excel, but can't begin to figure it out the other way.

Are there any free resourses or tutorials that you know of?

Thanks in advance.
[/quote]


I do this through phpMyAdmin. Check with your host to see if this available to you or if you are doing this locally go to [a href=\"http://www.phpmyadmin.net\" target=\"_blank\"]www.phpmyadmin.net[/a] and download the latest release.

It's pretty easy, you just specify what you are using to seperate the fields (with Excel this would be commas, since you need to save your spreadsheet as a .CSV file). You can browse to your .csv file and import it from there.

Keep in mind, if you have a field in your table you must have a column in your spreadsheet for that field. Such as, if you have an "id" field in your table you may not have that in your spreadsheet at the moment and need to add it. Essentially all the fields in your table need to present as columns (in the same order) in your spreadsheet.

Hope that helps!

[gerkintrigg]

Ideally I'd like for my users to upload data to the database from an excel file rather than comma seperated values. I know how to do it, but the poor little tykes might not. I'd also like to avoid phpMyAdmin too. It's too powerful to let my boss play with it ;o)

Thanks for the info about the columns & fields. Does this also cuase problems if the id field is left out because it auto-increments?

[freakus_maximus]

[!--quoteo(post=367982:date=Apr 24 2006, 10:22 AM:name=gerkintrigg)--][div class=\'quotetop\']QUOTE(gerkintrigg @ Apr 24 2006, 10:22 AM) [snapback]367982[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Ideally I'd like for my users to upload data to the database from an excel file rather than comma seperated values. I know how to do it, but the poor little tykes might not. I'd also like to avoid phpMyAdmin too. It's too powerful to let my boss play with it ;o)

Thanks for the info about the columns & fields. Does this also cuase problems if the id field is left out because it auto-increments?
[/quote]


Well you got me on that one...have not tried to do this any other way. As far as the id field goes, if you leave it out then your data gets flooded to the wrong columns (that would go for any missing field). I do not know if this has a bearing with auto-increments.

I have not been successful in having an imported csv add the auto-incremented id's. So, if I have to add to existing data I generally export it first and combine it with the new data. In excel, I can drag n' fill the id numbers until all rows have an id. Then I can import (although when you do, you need to empty your table or replace existing data). That has always worked fine for my needs.

Others may know if different methods for importing a csv the way you need.

[michaellunsford]

for simplicity sake, you will certainly need to export as a delimited file. I like | (pipe) delimited, which is shift-backslash on my keyboard... it kind of looks like a lowercase L.

once you've exported excel to some kind of delimited file, you can then explode it to an array, then inject it to your database.

also regarding the auto-increment field. I generally just leave it out of the INSERT, and it works fine.

[litebearer]

I love a challenge.

I spent a few minutes (LOL) searching the web for different scripts/snippets/etc with which to cobble together a solution. I am NO guru, so you may need to do some tweaking.

In the below zip file there are 4 files (excel2csv.php, reader.php, oleread.inc and sample.xls).

The only file you need to edit is the excel2csv.php file --- there is a section where it displays the contents of the newly created csv file; simply replace the echo with a bit of code that (1) connects to your database (2) creates the appropriate sql query, and (3) emails you a message that your 'tykes' have done something.

Note: all of the files go in the same folder, and be sure to make the folder reads writeable

[a href=\"http://nstoia.com/excel2csv/excel2mysql.zip\" target=\"_blank\"]http://nstoia.com/excel2csv/excel2mysql.zip[/a]

Hope this helps

Lite...

[gerkintrigg]

Thanks Lite, I'll give it a try and let you know how it goes.