Converting Excel data into MySQL database with PHP

Hi,

i was chasing a bit of help with a script. I have a spreadsheet on excel where i track basketball tables and have about 10 tables on the one sheet (eg. Home table, away table, overall, etc etc). What i want to be able to do is upload these tables to a MySQL database on my server for a site im creating.

To give you an idea, my spreadsheet is called NBL.xls but for this purpose i would probably need to save it as .csv or .xml file.

To give you an example of the table i want the script to insert into mysql, take a look at the following:

Overall Ladder
# P W L Avg F Avg A Pts% WR%
1 Bullets 33 28 5 115 101 114.4 84.8%
2 Tigers 33 25 8 105 98 107.0 75.8%
3 Wildcats 33 23 10 101 94 107.0 69.7%
4 Kings 33 20 13 98 95 103.8 60.6%
5 Crocodiles 33 19 14 110 107 103.1 57.6%
6 Taipans 33 17 16 100 100 100.3 51.5%
7 Dragons 33 15 18 104 106 97.3 45.5%
8 Slingers 33 13 20 100 104 95.9 39.4%
9 Breakers 33 11 22 102 107 95.6 33.3%
10 Hawks 33 11 22 98 103 95.4 33.3%
11 36ers 33 11 22 101 108 93.6 33.3%
12 Razorbacks 33 5 28 98 109 89.4 15.2%

Hopefully what im asking is clear and understandable. Any help with getting started with this would be greatly apreciated.

Thanks :)

Convert the Excel file to .csv format and upload the text file directly into phpMyAdmin....it's really that easy

Cheers mate, sounds easy.

I do have one quesiton though, as there are about 10 different tables on the one sheet, is it possible to upload the 10 sepperate tables into the database? And if so, there would have to be a script which can do this automatically for me rather than having to manually do it

Thank you :thumbsup:

The time taken to track down and customise a script to do this would be much longer than to just edit the main xls into 10 separate csv's. What's the big deal? Using phpmyadmin, this would take 5 minutes!

Cheers mate, sounds easy.

I do have one quesiton though, as there are about 10 different tables on the one sheet, is it possible to upload the 10 sepperate tables into the database? And if so, there would have to be a script which can do this automatically for me rather than having to manually do it

Thank you :thumbsup:

If you are able to get all 10 "tables" into one .csv file (and i don;t know if you can), so that you have your .csv file looking like this:

ColumnName1,ColumnName2,ColumnName3
sdf,sdf,sdf
sdf,sdf,sdf
sdf,sdf,sdf
sdf,sdf,sdf
sdf,sdf,sdf
sdf,sdf,sdf

ColumnName1,ColumnName2,ColumnName3
sdf,sdf,sdf
sdf,sdf,sdf
sdf,sdf,sdf
sdf,sdf,sdf
sdf,sdf,sdf
sdf,sdf,sdf

etc...

You could use some PHP to parse the text file and create a new DB table with the ensuing data every time PHP sees a line break in your text file. You could store DB table names in an array or use some generic table naming convention...

If you plan to do this kind of thing frequently, a script like this could be useful...if not, it might be easier to just upload 10 separate .csv files directly into phpMyAdmin...

Thanks so much guys, cant believe how easy that was to be honest. Thought id have to work out a stack of code but its a simple as just importing a table.

Thanks for the advice, much apreciated :)