Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Jan 2007
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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

  • #2
    Senior Coder chump2877's Avatar
    Join Date
    Dec 2004
    Location
    the U.S. of freakin' A.
    Posts
    2,803
    Thanks
    19
    Thanked 157 Times in 148 Posts
    Convert the Excel file to .csv format and upload the text file directly into phpMyAdmin....it's really that easy
    Regards, R.J.

    ---------------------------------------------------------

    Help spread the word! Like my YouTube-to-Mp3 Conversion Script on Facebook !! :)
    [Related videos and tutorials are also available at my YouTube channel and on Dailymotion]
    Get free updates about new software version releases, features, and bug fixes!

  • #3
    New Coder
    Join Date
    Jan 2007
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #4
    Regular Coder meth's Avatar
    Join Date
    Jan 2003
    Posts
    262
    Thanks
    0
    Thanked 9 Times in 9 Posts
    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!
    I do Web Design, Brisbane based.
    More time spent in PHP/MySQL Web Development.
    And Search Engine Optimisation takes up the rest of it.

  • #5
    Senior Coder chump2877's Avatar
    Join Date
    Dec 2004
    Location
    the U.S. of freakin' A.
    Posts
    2,803
    Thanks
    19
    Thanked 157 Times in 148 Posts
    Quote Originally Posted by Halli View Post
    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
    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...
    Regards, R.J.

    ---------------------------------------------------------

    Help spread the word! Like my YouTube-to-Mp3 Conversion Script on Facebook !! :)
    [Related videos and tutorials are also available at my YouTube channel and on Dailymotion]
    Get free updates about new software version releases, features, and bug fixes!

  • #6
    New Coder
    Join Date
    Jan 2007
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Last edited by Halli; 02-28-2007 at 12:59 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •