SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2002
    Location
    USA
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Uploading CSV into MySQL via php

    If you wish to load only some of a table's columns, specify a field list:


    mysql> LOAD DATA INFILE 'persondata.txt'
    -> INTO TABLE persondata (col1,col2,...);


    You must also specify a field list if the order of the fields in the input file differs from the order of the columns in the table. Otherwise, MySQL cannot tell how to match up input fields with table columns.
    I have a membership database already set up in MySQL, but the data is currently managed in MSAccess. I want to take the CSV dump of the data and be able to load it up into the MySQL table via a CMS page using php.

    The member table has columns named:
    - member_fname, member_lname, member_number, member_phone etc.

    The CVS file has in the first row the column names:
    - MEM NUM, Lst NAME, Fst NAME, HOME PHONE

    so I want to be able to write a file upload script that will
    1) upload CSV file to directory on web (done)
    2) read the first row of the CSV and gain the field lists of the CSV
    3) load the data into the table by matching the field names:
    member_fname=Fst NAME etc.

    It looks like I should be able to list the CSV column names in the proper order, but what if the ordering of columns were to change in the CSV? I want to be able to pull the correct columns in if the ordering changes.

    Anyone have a few minutes to dabble with this?

    **also I have thought about providing a screen (after uploading the CSV) that will list the table columns and then a pulldown selector of the CSV fields and then "map" the file into the database. I may be dealing with a project where I get CSVs uploaded that have more info than the table needs and the field names in the CSV might vary, so they would need to map in their data to the table.

  2. #2
    SitePoint Evangelist
    Join Date
    Jul 2001
    Location
    Michigan, USA
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did something similar with a Perl script I wrote. Even if the column ordering is different, it's still possible. The way I did it was have to user setup their template, which was basically the ordering of the columns. After that, I had them upload the script, and then the script ordered properly. I'd show you the code, but the way I did it was so sloppy that I don't even know what it is really doing.

    I would have found a better method.

    Might I suggest shoving them in into arrays of the proper column name, and then adding them all by the row.

    Sample (psuedo code):

    PHP Code:
    //some setup stuff
    $columns['username'] = array();
    //...and the rest
     
    //get the line of data
    $data_line explode(','$line);
    array_push($columns['username'],$data_line[$username_column];
    //...get the rest of hte vars
     
    //then just loop through the length of the array in $columns['username'] 
    Hopefully, this makes sense to you.

  3. #3
    SitePoint Wizard Sillysoft's Avatar
    Join Date
    May 2002
    Location
    United States :)
    Posts
    1,691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dunno if this will help you but it might:

    http://us3.php.net/manual/en/function.fgetcsv.php

    Silly

  4. #4
    SitePoint Zealot
    Join Date
    Jul 2002
    Location
    USA
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, that gives me the first row of the CSV and the cloumn names...now how can I take this array and plug it into the LOAD DATA INFILE command to specify my field list (ie col1,col2,col3) .. looks like I need to write the SQL statement by looping through the array?

    but wait, I stil need to know how the files match up. So I have the array of column names..now I need the user to matchup the field names, and then click submit which will then write the column ordering?

    so I have a table with two columns..the first column lists the field names in the Member database and the second column is a pulldown list of the field names in the Array..and then when the form submits I can pick up my ordering by the http_post_vars.

    So I could write my SQL statement by saying member_fname=http_post_var['fname']

    does this make sense to anyone? Ideally I'd like to make this a function or class that I use on many websites and the clients are going to use all different names, ordering but the mapping into the database will remain consistent.

  5. #5
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why not just use some existing script ?
    Here is one: http://www.hotscripts.com/Detailed/18098.html

    -The easiest way is often the best...
    - website

  6. #6
    SitePoint Zealot
    Join Date
    Jul 2002
    Location
    USA
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well that is going down the exact path I am discussing..thanks!

    Now it gives me an error when I tried using it however..
    Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/virtual/site3/fst/var/www/html/cms/csv/includes/php/stages/select_db.php on line 60
    So that's as far as I got!


Bookmarks

Posting Permissions

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