Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Jul 2003
    Location
    New Zealand
    Posts
    435
    Thanks
    1
    Thanked 0 Times in 0 Posts

    php & mysql: splitting a csv and inserting into database

    i have a script, here's the code:

    PHP Code:
    <?php

    include "config.php";

     
    /*

    $all = mysql_query("SELECT * FROM Wineries");

    while($row = mysql_fetch_assoc($all)){

            $password = generatePassword();

            if(!mysql_query("UPDATE Wineries SET Password = '".md5($password)."' WHERE id = '".$row["id"]."' LIMIT 1")) echo mysql_error();

            mail("pr.testemail@mindmediagroup.com", $row["Name1"] . " ".$row["Surname"]."'s New Password", $password, "FROM: WhatWine <whatwine@mindmediagroup.com>");

    }

    */

    $fp file("wine.csv");

    foreach(
    $fp as $key => $current){

            if(
    $key 0){

                    
    $sql "INSERT INTO Wineries VALUES('',";

                    
    $pieces explode(','$current);

                    foreach(
    $pieces as $p => $piece){

                            
    /*if(strstr($piece, "@")){

                                    list($user, $domain) = explode("@", $piece);

                                    $piece = $user . "@mindinternet.co.nz";

                            }*/

                            #$piece = str_replace("\r\n", "", $piece);
                            
    $piece trim($piece);
                            
    $piece str_replace('"'''$piece);
                            
    $piece str_replace("\n"''$piece);
                            
    $piece str_replace("\r"''$piece);

                            
    $sql .= "\n\t'".mysql_real_escape_string($piece)."'";

                            
    $column_count++;

                            if(
    $p count($pieces)) $sql .= ",";

                    }

                    
    #$sql = substr($sql, 0, strlen($sql)-1);

                    
    $sql .= ");\n\n";

            }

            if(
    $sql){

            if(!
    mysql_query($sql)){ echo '<pre style="color: red">' $sql '</pre><br /><br />'.$column_count.'<br /><br />' mysql_error(); } else { if(!$ca){ echo '<pre style="color: green">'.$sql.'</pre><br /><br />'.$column_count$ca true; } }
            unset(
    $sql);

            }

            unset(
    $column_count);

    }


    echo 
    '<br /><br /><span style="color: green">done</span>';

    ?>
    this script takes each line (except the first, which has titles in it) and splits it by commas, then creates the sql and executes it, problem is, the sql query is real screwed, some queries go through fine, others are missing the last set of '' in the insert area which triggers a bad column count error, others come out like this:

    Code:
    INSERT INTO Wineries VALUES('',
    	'');
    and

    Code:
    INSERT INTO Wineries VALUES('',
    	'Ktest Estate was established and is owned by Wallace Hilder and Charles Forkworth.  Our sales office is run by Jonathan Holder our vines are managed by Cathryn Maccar and our wines are made by Dean Shed.');
    originally i thought it was because the csv i exported from excel had some commas inside some of the cells, so i removed them all, but it still gives errors.

    help greatly appreciated, this is relatively urgent!

  • #2
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    so.. what is the actual error message you are getting?

  • #3
    Super Moderator
    Join Date
    May 2002
    Location
    Perth Australia
    Posts
    4,106
    Thanks
    11
    Thanked 101 Times in 99 Posts
    I have not looked at your code but from experience I found fgetcsv very useful
    resistance is...

    MVC is the current buzz in web application architectures. It comes from event-driven desktop application design and doesn't fit into web application design very well. But luckily nobody really knows what MVC means, so we can call our presentation layer separation mechanism MVC and move on. (Rasmus Lerdorf)

  • #4
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Not sure what you have to work with, but I find it easier to use phpMyAdmin to import a CSV file.


  •  

    Posting Permissions

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