duandian4501
2013-08-01 16:41
浏览 50
已采纳

CSV到数据库导入脚本

The default phpmyadmin functionality to import a CSV isn't working for me (spins and spins no errors) so I wrote a script to import it. I am not getting any errors from my script but none of the rows are importing! Can you see what I may be doing wrong?

<?php

$db_host        = 'secret';
$db_user        = 'secret';
$db_pass        = 'secret';
$db_database    = 'secret'; 

/* End config */



$link = mysql_connect($db_host,$db_user,$db_pass) or die('Unable to establish a DB connection');
echo "Successfully connected 
";
mysql_select_db($db_database,$link);
mysql_query("SET names UTF8");

//get the csv file
   // $file = $_FILES[csv][dc.csv];
$handle = fopen("dc.csv","r");
$counter = 0;    
$data = array();






    echo "starting import 
";
    while( ($line = fgetcsv($handle)) !== false) {
    $data[] = $line;


        //if ($data[0]) {
           mysql_query("INSERT INTO private_schools (school_name,
                                                      city,
                                                      state,
                                                      county_code,
                                                      zip_code,
                                                      NA2,
                                                      low_grade,
                                                      high_grade,
                                                      NA3,
                                                      NA4,
                                                      NA5,
                                                      grade_range,
                                                      NA6,
                                                      NA7,
                                                      NA8,
                                                      NA9,
                                                      NA10,
                                                      NA11,
                                                      NA12,
                                                      NA13,
                                                      NA14,
                                                      NA15,
                                                      NA16) VALUES
                (
                    '".addslashes($data[$counter][0])."',
                    '".addslashes($data[$counter][1])."',
                    '".addslashes($data[$counter][2])."',
                    '".addslashes($data[$counter][3])."',
                    '".addslashes($data[$counter][4])."',
                    '".addslashes($data[$counter][5])."',
                    '".addslashes($data[$counter][6])."',
                    '".addslashes($data[$counter][7])."',
                    '".addslashes($data[$counter][8])."',
                    '".addslashes($data[$counter][9])."',
                    '".addslashes($data[$counter][10])."',
                    '".addslashes($data[$counter][11])."',
                    '".addslashes($data[$counter][12])."',
                    '".addslashes($data[$counter][13])."',
                    '".addslashes($data[$counter][14])."',
                    '".addslashes($data[$counter][15])."',
                    '".addslashes($data[$counter][16])."',
                    '".addslashes($data[$counter][17])."',
                    '".addslashes($data[$counter][18])."',
                    '".addslashes($data[$counter][19])."',
                    '".addslashes($data[$counter][20])."',
                    '".addslashes($data[$counter][21])."',
                    '".addslashes($data[$counter][22])."'
                )
            " or die("THere was an issue!!: " . mysql_error()));
            //print($data[$counter][0]);
            print "INSERT INTO private_schools (school_name,
                                                      city,
                                                      state,
                                                      county_code,
                                                      zip_code,
                                                      NA2,
                                                      low_grade,
                                                      high_grade,
                                                      NA3,
                                                      NA4,
                                                      NA5,
                                                      grade_range,
                                                      NA6,
                                                      NA7,
                                                      NA8,
                                                      NA9,
                                                      NA10,
                                                      NA11,
                                                      NA12,
                                                      NA13,
                                                      NA14,
                                                      NA15,
                                                      NA16) VALUES
                (
                    '".addslashes($data[$counter][0])."',
                    '".addslashes($data[$counter][1])."',
                    '".addslashes($data[$counter][2])."',
                    '".addslashes($data[$counter][3])."',
                    '".addslashes($data[$counter][4])."',
                    '".addslashes($data[$counter][5])."',
                    '".addslashes($data[$counter][6])."',
                    '".addslashes($data[$counter][7])."',
                    '".addslashes($data[$counter][8])."',
                    '".addslashes($data[$counter][9])."',
                    '".addslashes($data[$counter][10])."',
                    '".addslashes($data[$counter][11])."',
                    '".addslashes($data[$counter][12])."',
                    '".addslashes($data[$counter][13])."',
                    '".addslashes($data[$counter][14])."',
                    '".addslashes($data[$counter][15])."',
                    '".addslashes($data[$counter][16])."',
                    '".addslashes($data[$counter][17])."',
                    '".addslashes($data[$counter][18])."',
                    '".addslashes($data[$counter][19])."',
                    '".addslashes($data[$counter][20])."',
                    '".addslashes($data[$counter][21])."',
                    '".addslashes($data[$counter][22])."'
                )
            " ;
            $counter ++;
            if ($counter === 3){
            exit();
            }
       // }
    }

//var_dump($data);
?>

Printing the sql statement yields

INSERT INTO private_schools (school_name, city, state, county_code, zip_code, NA2, low_grade, high_grade, NA3, NA4, NA5, grade_range, NA6, NA7, NA8, NA9, NA10, NA11, NA12, NA13, NA14, NA15, NA16) VALUES ( 'Evangel Classical Christian School', 'Alabaster', 'AL', '1117', '35007', '21', 'KG', '12', '5', '2', '8', 'K-12', '295', '40.7', '28.8', '10.2', '99', '0.7', '0', '0', '0', '0', '0.3' )

Sample of CSV file:

Abbeville Christian Academy,Abbeville,AL,1067,36310,42,PK,12,6,2,8,K-12,157,52.9,16.6,9.5,95.5,0.6,0,1.9,0,0.6,1.3
Kingwood Christian School,Alabaster,AL,1117,35007,21,KG,12,6,2,4,K-12,413,44.1,33.1,12.5,88.9,6.8,0,1.9,0,0.2,2.2
Evangel Classical Christian School,Alabaster,AL,1117,35007,21,KG,12,5,2,8,K-12,295,40.7,28.8,10.2,99,0.7,0,0,0,0,0.3
Westwood Bapt Weekday Edu Ministry,Alabaster,AL,1117,35007,21,PK,KG,6,2,5,K-6,12,50,1,12,100,0,0,0,0,0,0
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • duanjiao2978 2013-08-01 16:52
    已采纳

    Look into using the mysqli extension as mysql is deprecated.

    Manpage for addslashes says

    To escape database parameters, DBMS specific escape function (e.g. mysqli_real_escape_string() for MySQL or pg_escape_literal(), pg_escape_string() for PostgreSQL) should be used for security reasons.

    For mysql try mysql_real_escape_string, and for mysqli try mysqli_real_escape_string.

    I don't use this format to check/display errors, but I think the way you would write this is:

    mysql_query($yourInsertQueryString) or die("THere was an issue!!: " . mysql_error());
    

    Are all of those column types really strings?

    点赞 评论
  • donglizuo8892 2013-08-01 17:13

    You can try this approach too, quoting the fields too and using the "coupple" assingment to verify the correct assignment (a frequent problem with csv importing)

    <?php
    $db_host        = 'secret';
    $db_user        = 'secret';
    $db_pass        = 'secret';
    $db_database    = 'secret'; 
    
    $_db = mysql_connect($db_host,$db_user,$db_pass);
    mysql_select_db($db_database,$_db);
    
    $fileds = array(
    
    "school_name",
    "city",
    "state",
    "county_code",
    "zip_code",
    "NA2",
    "low_grade",
    "high_grade",
    "NA3",
    "NA4",
    "NA5",
    "grade_range",
    "NA6",
    "NA7",
    "NA8",
    "NA9",
    "NA10",
    "NA11",
    "NA12",
    "NA13",
    "NA14",
    "NA15",   
    "NA16",
    );
    
    $handle = fopen("dc.csv","r");
    $counter = 0;
    $data = array();
    echo "starting import 
    ";
    while( ($line = fgetcsv($handle)) !== false) {
        $data[] = $line;
    }
    foreach($data as $row){
        if(count($fileds) != count($row)){
            continue;
        }
        $couples = array();
        foreach($fileds as $k => $v){
            $couples[] = sprintf("`%s` = '%s",$v ,mysql_real_escape_string($row[$k],$_db));
        }
        $_sql = 'INSERT INTO private_schools '.join(',', $couples).';';
        echo "$_sql
    ";
        mysql_query($_sql);
    
    }
    // and remeber to commit if you use a transactiona storage engine
    mysql_query('commit',$_db); // fixed for "old" client
    
    点赞 评论
  • douzheng1853 2013-08-01 17:18

    You could try using mysqlimport. You run in from a command window.

    Heres a nice easy tutorial

    It does not matter if you are using windows, it is available on that as well, just the same.

    点赞 评论

相关推荐 更多相似问题