SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    967
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    best practices for uploading a csv to MYSQL

    I wrote a script that uploads a csv to a database. When I ran a csv through it one of the lines got jumbled and some of the data was reported missing.

    Are there best practices for validation, etc.. to ensure that the columns and rows won't get misinterpreted?

    Thanks

  2. #2
    Web Professional
    Join Date
    Oct 2008
    Location
    London
    Posts
    862
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Best practice is to not reinvent the wheel: LOAD DATA INFILE

  3. #3
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try something like this.

    PHP Code:
    <?php

    if ((isset($HTTP_POST_VARS["MM_update"])) && ($HTTP_POST_VARS["MM_update"] == "form1")) {

    $link mysql_connect("**""****""****") or die("Could not connect: ".mysql_error());

    $db mysql_select_db("****") or die(mysql_error());


    // yourfile.csv must be in the same directory as this php file
    $handle fopen ("yourfile.csv","r");
       
    while (
    $data fgetcsv ($handle1000",")) {
                
                
    $prod_id $data[0];
                
    $no_of_qt $data[1];
                
    $notes $data[2];
                
    $b_id $data[3];
                

    $query "INSERT INTO p_b (prod_id,no_of_qt,notes,b_id) values ('".$prod_id."','".$no_of_qt."','".$notes."','".$b_id."')";
       
    $result mysql_query($query) or die('Query failed: ' mysql_error());
       }
       
    // Printing results in HTML
      /* if(mysql_affected_rows() > 0){
       
       echo "this is result". mysql_affected_rows();
       
       }else{
       
              echo "this is no result";
        }*/
    echo "product is inseted";
    //mysql_close($link);
    fclose ($handle);

    }
    ?>
    change your variable with my variables. any help let us know

  4. #4
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, i forgot about the validation part. try this

    PHP Code:
    if(($notes==""))
                  {
           
                    
    $notesErr              =" Dont keep this empty";
                    
    $validate              0;
                   }  
                 if(
    validate==0)
                 {
    while (
    $data fgetcsv ($handle1000",")) {
                
                
    $prod_id $data[0];
                
    $no_of_qt $data[1];
                
    $notes $data[2];
                
    $b_id $data[3];
    //insertion query

    }

    html code
    HTML Code:
    <form action="<?=$PHP_SELF;?>" method="POST" name="">
    <table>
    
    
    <tr><td>Notes:</td><td><input type="hidden" name="notes" value="" /><label class="errorText"><?=$$NotesErr;?></label></td></tr>
    
    <tr><td colspan=4><input type="submit" name="submit"  value="submit"/>
    </table> <br><br>
    </form>
    </td>
    </tr>
    
    </head>
    </body>

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    my vote is for decowski's solution -- load the data using the provided utility, and save yourself the bother of writing the application code

    (by the way, doing one INSERT statement for every record in the csv is very inefficient compared to a single command to load the entire file)

    if you need to do further validation, you can then use SQL to edit and filter rows and columns, before copying the data into the final target table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    load the data using the provided utility, and save yourself the bother of writing the application code
    you are absolutely right r937, but sometimes we need , query should run atleast,
    this query never works only
    PHP Code:
     $query "LOAD DATA INFILE 'D:\***\***\***\***\Book2.csv' INTO TABLE p_b FIELDS TERMINATED BY ',' ENCLOSED BY '&quot;' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'"
    that why i was using that method. may be i have to work on this again
    but my method is also not bad hah because its work for me.

  7. #7
    Web Professional
    Join Date
    Oct 2008
    Location
    London
    Posts
    862
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by deepson2 View Post
    but my method is also not bad hah
    It actualy is bad for the reason Rudy has mentioned: it queries the database for every single row inserted ó performance-wise itís as bad as it can get.

    Quote Originally Posted by deepson2 View Post
    because its work for me.
    Ah, the most famous non-argument

  8. #8
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by decowski View Post
    It actualy is bad for the reason Rudy has mentioned: it queries the database for every single row inserted ó performance-wise itís as bad as it can get.
    hmm. may be i should give another try for your method then.

    Ah, the most famous non-argument

  9. #9
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    967
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I need to validate some of the fields for accuracy to ensure the person creating the csv isn't inserting bad data. So I think Load Data Infile won't do the job.


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
  •