#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,672
    Rep Power
    171

    Upload csv, insert value in to the database


    Hi;

    Let's say there is a table with structure like:

    id, name, email, address


    Some members might upload csv files with different structure and want to insert those into the database.

    For example:

    Code:
    id, name, email, address
    CPP,MILTON@ww.ll,2008,456
    JAVA,Gilson@ll.kj,2002,456
    OR

    Code:
    id, name
    CPP,MILTON
    JAVA,Gilson

    Questions:

    1 - What's the best way to insert it into the database? I see different tuotorials loop through csv and insert as it goes, sounds bad.

    Should I create an array of data and then hit it with 1 bulk insert?

    2 - How can I build the sql dynamically based on the user csv format?

    Please guide thanks
    Last edited by English Breakfast Tea; February 9th, 2014 at 07:37 PM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    189
    Rep Power
    0
    One way would be to provide the mechanism that they use to create the file so that you can edit it on input. Of course then you could just directly update the db too.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,998
    Rep Power
    375
    if users are uploading something then it relates to THEIR account.. so if they do something bad then it is their fault - BUT depending on if what they upload, doesn't change (i.e. they always upload same thing name, desc, price whatever) then you can check each column of each row and make sure it is in the right format, if not then "flag" that row and get users to either "edit" or discard silently (keeping track) and tell the user 10 of 200 rows have not been uploaded etc
  6. #4
  7. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,672
    Rep Power
    171
    Is it the right way to use fgetcsv, build an array and bulk insert?
    Or use mysql's LOAD INFILE ?



    Edit;

    Also the format of csv files they upload varies. How can I check the format and build the query?
    Code:
    LOAD DATA INFILE 'path/file.csv'
    INTO TABLE tbl_name 
    FIELDS TERMINATED BY ',' 
    ENCLOSED BY '"' 
    LINES TERMINATED BY '\n' 
    (column1, column2, column3,......);
    Last edited by English Breakfast Tea; February 9th, 2014 at 07:34 PM.

IMN logo majestic logo threadwatch logo seochat tools logo