[SOLVED] Ability To Allow Users to Import .csv File Using .php Site

[dlebowski]

I want to give my clients the ability to import a .csv file from the website into their mysql database.  I want them to be able to browse to the .csv file on their machine, select the fields they want to import and match them up with the fields in the MySql DB.  Then submit the import.

 

Is anyone aware of any good tutorials or threads here in the forum that show me how to do this?  I may have overlooked them in my search over the last couple of days.  Thanks in advance.

[trq]

You need to be alot clearer on what is residing on the client and what is on the server.

 

Either way, its all going to need to be uploaded to the server if php is to have any chance of doing anything with it.

[dlebowski]

The client will have a .csv file that looks like this:

 

ID, TITLE, DESCRIPTION, DATE

3, Pontiac, 4 Door, 2007-08-15

 

The database will look something like this:

 

ID, TITLE, DESCRIPTION, DATE

 

 

Once they are logged into the website, I want them to be able to go a page on the site where they can click on a "Browse" button to find the .csv file on their machine.  Once they find it, I want them to be able to match up the columns in their .csv file with the corresponding columns in the database.  Once they are matched up, click submit and the script inserts or updates the records in the database accordingly.

[roopurt18]

The first thing you need to do is learn how to upload a file.  Try and find a tutorial on creating a file upload form and then read this:

http://www.php.net/features.file-upload

 

Once the file is uploaded and you've done whatever validation you need to perform, you need to open the file and count the number of fields within the file.  Then you can present the user with another form that lists the DB columns and the numbered columns in their table and allows them to match them up.

 

Once that column-matching form is submitted, you can open the file and import the values into the corresponding fields, assuming that all required fields have been matched.

 

The part that is probably hanging you up most is that you're trying to complete a process on the server that requires intermediate user intervention, which means multiple forms and when one is submitted moving the user along to the next form.

 

You can allow the user to do this all in one form if you wish.  The form would list the possible table columns and the user would assign an integer value to each one; this integer would correspond to a column in the CSV file.  The last element on the form is a file browse field.  The post page should open the uploaded file, match the columns up, and insert the data.

[dlebowski]

Thanks for the detailed response.  I think I can handle all of those things mentioned in your post with the exception of:

 

you need to open the file and count the number of fields within the file

 

What kind of query would I run to count the fields in a file?  Thanks again.

[roopurt18]

Assuming columns are separated by commas and rows are separated by newlines:

 

<?php
  $file = file_get_contents($uploaded_file); // you have to point $uploaded_file at the correct file
  if(!strlen($file)){
    // Empty file, do some error handling here
  }

  // Newline is different in each major operating system:
  // Unix - \n
  // Windows - \r\n
  // Mac - \r

  // str_replace below will change newlines to the following:
  // Unix - \n
  // Windows - \n\n
  // Mac - \n
  // You now have two options:
  //   use str_replace to replace "\n\n" with "\n"
  //   ~OR~ while looping over the rows, discard any that are zero length
  // NOTE:  You might be tempted to use preg_replace to pattern match "\r\n" and "\r"
  // and replace them with "\n" all in one go, BUT you are going to run into problems with
  // large files.  At least, whenever I use regexps on LARGE files on my system it crashes;
  // you'll have to get a feel for how big of files you can search with a preg_* function
  $file = str_replace("\r", "\n", $file);

  // Convert to rows
  $rows = explode("\n", $file);
  foreach($rows as $row){
    $row = trim($row);
    if(!strlen($row)){
      continue;  // Empty line, skip
    }
    $cols = explode(",", $row);
    // Here we do one of two things
    if( CALCULATE_NUM_COLUMNS ){
      $number_of_columns = count($cols);
      break; // Get out of the loop, we're only counting number of columns so we only need
      // the number in the first actual row
    }else if( PARSE_EACH_ROW ){
      // Sanitize each element in $cols for DB entry first!!!
      // Build SQL statements
      $sql = "INSERT INTO table (col1, col2, ..., colN) VALUES ("
             . implode(",", $cols) . ")";
      $q = mysql_query($sql);
      // Perform error checking
    }
  }
?>

[dlebowski]

Thanks man.  I'll give this a shot!