SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    googlicious graymatter bvarvel's Avatar
    Join Date
    Sep 2002
    Location
    Katy, TX
    Posts
    956
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Importing CSV data to MySQL

    Anyone have any snippets laying around to help me build a page to import CSV data into a mysql database. I need something that I can present to several users so I don't want to have to use the phpmyadmin route. anyone have any tips?

  2. #2
    Sell crazy someplace else markl999's Avatar
    Join Date
    Aug 2003
    Location
    Manchester, UK
    Posts
    4,007
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can use fgetcsv to run through the csv file and build up your INSERT query.

  3. #3
    googlicious graymatter bvarvel's Avatar
    Join Date
    Sep 2002
    Location
    Katy, TX
    Posts
    956
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok.. thanks. only problem i'm having now is when i'm running through the csv, i get errors when trying to insert a row and one of the fields is blank. any ideas on how to prevent those errors from showing up?

    i don't want to turn off error reporting, just ignore the insert errors regarding fields that are blank.

  4. #4
    googlicious graymatter bvarvel's Avatar
    Join Date
    Sep 2002
    Location
    Katy, TX
    Posts
    956
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's what I've managed to piece together:

    PHP Code:
      <?php
      
    include_once 'BrianVarvel/config/config.php';
      
    dbConnect("bvarvel_dbmain");
      
    $CSVFile "test.csv";
      
      if (
    file_exists($CSVFile)) {
          
    $row 1;
          
    $handle fopen ("$CSVFile","r");
          
    fgetcsv ($handle1000",");
      
          while ((
    $data fgetcsv($handle1000",")) !== FALSE) {
              
    $num count($data);
               
    $row++;
               
               if (!isset(
    $data[0])) { $data[0] = " "; }
               if (!isset(
    $data[1])) { $data[1] = " "; }
               if (!isset(
    $data[2])) { $data[2] = " "; }
               if (!isset(
    $data[3])) { $data[3] = " "; }
               if (!isset(
    $data[4])) { $data[4] = " "; }
               if (!isset(
    $data[5])) { $data[5] = " "; }
               if (!isset(
    $data[6])) { $data[6] = " "; }
               if (!isset(
    $data[7])) { $data[7] = " "; }
               if (!isset(
    $data[8])) { $data[8] = " "; }
               if (!isset(
    $data[9])) { $data[9] = " "; }
               if (!isset(
    $data[10])) { $data[10] = " "; }
               if (!isset(
    $data[11])) { $data[11] = " "; }
          
               
             
    $userExists = (@mysql_result(@mysql_query("SELECT count(sid) FROM tbl_subscribers WHERE semail = '{$data[10]}'"), 00) > true false);
              
              if(
    $userExists == false) {
              
              
    mysql_query("INSERT INTO tbl_subscribers SET 
                  sfirst = '
    $data[0]',
                  slast = '
    $data[1]',
                  saddress = '
    $data[2]',
                  scity = '
    $data[3]',
                  sstate = '
    $data[4]',
                  szip = '
    $data[5]',
                  shome = '
    $data[6]',
                  smobile = '
    $data[7]',
                  sbusiness = '
    $data[8]',
                  sfax = '
    $data[9]',
                  semail = '
    $data[10]',
                  swebsite = '
    $data[11]'");
          
                 echo 
    $data[0] . " " $data[1] . " was imported successfully.<br />";    
          
          
               
               } else {
          
                  echo 
    $data[0] . " " $data[1] . " already exists and was not imported based on email address.<br />";
          
              }
      }
      
      echo 
    "A total of " . ($row-1) . " records were imported.";
      
      
    fclose ($handle);
      
      } else {
      
          echo 
    "No file in folder!!";
      
      }
      
    ?>
    There are twelve fields in each row (name, address, etc.). The problem is: some of them get uploaded, some don't, and there doesn't seem to be any rhyme or reason to it.

    CAN ANYONE SEE THE BUG IN THIS????

  5. #5
    googlicious graymatter bvarvel's Avatar
    Join Date
    Sep 2002
    Location
    Katy, TX
    Posts
    956
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    anyone got any more ideas?

  6. #6
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can you post an example csv file

  7. #7
    googlicious graymatter bvarvel's Avatar
    Join Date
    Sep 2002
    Location
    Katy, TX
    Posts
    956
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here ya go:
    Attached Files Attached Files

  8. #8
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers - will look at it as soon as it is approved.

  9. #9
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Assuming the following test data in a file called test1.csv

    Code:
       "","",""
       "2first","",""
       "","3se'co'nd",""
       "","","4thi$r'd"
       "5first","second",""
       "6first","","third"
       "","7second","third"
       "8first","second","third"
    There are 8 lines, each with 3 fields per line.

    The PHP code
    PHP Code:
       // Function used as callback from array_walk
       // adds single quotes around each value
      // and escapes all harmfull data
       
    function AddSingleQuote(& $ArrayValue$ArrayKey)
       {
         
    $ArrayValue '\'' mysql_real_escape_string($ArrayValue) . '\'';
       }
       
       
    // Function used to determine whether a user exists
       // returns FALSE if user does not exits
       // returns TRUE is a user does exist
       
    function UserExists($EmailName)
       {
         if (
    '' == $EmailName)
         {
           
    $ret false;
         }
         else
         {
           
    $ret true;
         }
         return (
    $ret);
       }
       
     
    // connect to database here
      
       // csv filename
       
    $filename 'test1.csv';
       
    $fh fopen($filename'r');
       if (
    false == $fh)
       {
         echo 
    'failed to open ' $filename '<br />';
       }
       else
       {
         
    // temporary array to hold data for SQL statement
         
    $values_array = array();
         
    // read each line from the CSV file
         
    while (false != ($csv_line fgetcsv($fh100)))
         {
           
    // if the username from the CSV file does not esits
           
    if (false == UserExists($csv_line[0]))
           {
             
    // make sure all the data i properly escaped
             
    array_walk($csv_line'AddsingleQuote');
             
    // make line for VALUE statement from SQL INSERT INTO string
             
    $values_array[] = '(' implode(', '$csv_line) . ')';
           }
         }
       
         
    // build SQL insert string
         
    $sql 'INSERT'
              
    ' INTO tbl_subscribers'
              
    ' VALUES'
              
    ' ' implode(', '$values_array)
              ;
         echo 
    $sql;
         
    $result mysql_query($sql) or dir ('Failed to execute ' $sql ' due to ' mysql_error());
         
    mysql_free_result($result);
       } 
    This code reads the test file and creates an SQL INSERT INTO statement for those lines where the first field in empty (which represent a non-existant user in your scenario).

    I have left the coding of checking for an existing username as an excercise for you (just change the function USerExists as required)

    Note that there is only 1 SQL INSERT INTO statement. This single statement will insert all the data in a single query.

    when the attachment gets approved, I'll download it and run it through this code to see if it works.

    Have fun

  10. #10
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There is a really cool script called CSV Importer. Works like a charm.

    Um, note the review toward the bottom of the page by yours truly.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web


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
  •