sourcecode

Importing CSV data using PHP/MySQL

copyscript 2023. 9. 21. 21:12
반응형

Importing CSV data using PHP/MySQL

I'm having a bit of a problem trying to import data from a CSV and have a couple of questions on it that I haven't managed to solve myself yet.

First off here's my code to help put things in perspective (tidied it up a bit, removing CSS and DB connection):

<body>
<div id="container">
<div id="form">

<?php
$deleterecords = "TRUNCATE TABLE tablename"; //empty the table of its current records
mysql_query($deleterecords);

//Upload File
if (isset($_POST['submit'])) {
    
    if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
        echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded 
 successfully." . "</h1>";
        echo "<h2>Displaying contents:</h2>";
        readfile($_FILES['filename']['tmp_name']);
    }

    //Import uploaded file to Database
    $handle = fopen($_FILES['filename']['tmp_name'], "r");

    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $import="INSERT into importing(text,number)values('$data[0]','$data[1]')";
        
        mysql_query($import) or die(mysql_error());
    }

    fclose($handle);

    print "Import done";

//view upload form
} else {

    print "Upload new csv by browsing to file and clicking on Upload<br />\n";

    print "<form enctype='multipart/form-data' action='upload.php' method='post'>";

    print "File name to import:<br />\n";

    print "<input size='50' type='file' name='filename'><br />\n";

    print "<input type='submit' name='submit' value='Upload'></form>";

}

?>

</div>
</div>
</body>

It's basically an adaptation of an example I have found after many many attempts at various methods.

My CSV has two columns of data, the first one being text and the second is integers The table in the database also has two columns, the first called "text" and the second "number"

So the questions I have are:

  1. the text being uploaded is just being displayed as 0 in every field and i'm not sure why
  2. I keep reading about data ending up enclosed in "", if that happens how would I sort it?
  3. how can I ignore the first X lines of the CSV for headers etc?
  4. is the data format changed throughout this process or is it ready for me to use in a graph? e.g. would a decimal stay a decimal once placed in the database?

EDIT:

Just done a test of 10,000 record uploading and got the error:

"Fatal error: Maximum execution time of 30 seconds exceeded"

any thoughts?

I answered a virtually identical question just the other day: Save CSV files into mysql database

MySQL에는 기능이 있습니다.LOAD DATA INFILE, 이를 통해 PHP 프로그램을 통해 루프에서 처리할 필요 없이 CSV 파일을 단일 SQL 쿼리로 직접 가져올 수 있습니다.

Simple example:

<?php
$query = <<<eof
    LOAD DATA INFILE '$fileName'
     INTO TABLE tableName
     FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
     LINES TERMINATED BY '\n'
    (field1,field2,field3,etc)
eof;

$db->query($query);
?>

It's as simple as that.

No loops, no fuss. And much much quicker than parsing it in PHP.

MySQL manual page here: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Hope that helps

$i=0;
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
if($i>0){
    $import="INSERT into importing(text,number)values('".$data[0]."','".$data[1]."')";
    mysql_query($import) or die(mysql_error());
}
$i=1;
}

letsay $infile = a.csv //file needs to be imported.

class blah
{
 static public function readJobsFromFile($file)
{            
    if (($handle = fopen($file, "r")) === FALSE) 
    {
        echo "readJobsFromFile: Failed to open file [$file]\n";
        die;
    }

    $header=true;
    $index=0;
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) 
    {
        // ignore header
        if ($header == true)
        {
            $header = false;
            continue;
        }

        if ($data[0] == '' && $data[1] == '' ) //u have oly 2 fields
        {
            echo "readJobsFromFile: No more input entries\n";
            break;                        
        }            


        $a      = trim($data[0]);
        $b   = trim($data[1]);                 





        if (check_if_exists("SELECT count(*) FROM Db_table WHERE a='$a' AND b='$b'") === true)
        {

                $index++;
            continue;    
        }            

        $sql = "INSERT INTO DB_table SET a='$a' , b='$b' ";
        @mysql_query($sql) or die("readJobsFromFile: " . mysql_error());            
        $index++;
    }

    fclose($handle);        
    return $index; //no. of fields in database.
} 
function
check_if_exists($sql)
{
$result = mysql_query($sql) or die("$sql --" . mysql_error());
if (!$result) {
    $message  = 'check_if_exists::Invalid query: ' . mysql_error() . "\n";
    $message .= 'Query: ' . $sql;
    die($message);
}

$row = mysql_fetch_assoc ($result);
$count = $row['count(*)'];
if ($count > 0)
    return true;
return false;
}

$infile=a.csv; 
blah::readJobsFromFile($infile);
}

hope this helps.

set_time_limit(10000);

$con = mysql_connect('127.0.0.1','root','password');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("db", $con);

$fp = fopen("file.csv", "r");

while( !feof($fp) ) {
  if( !$line = fgetcsv($fp, 1000, ';', '"')) {
     continue;
  }

    $importSQL = "INSERT INTO table_name VALUES('".$line[0]."','".$line[1]."','".$line[2]."')";

    mysql_query($importSQL) or die(mysql_error());  

}

fclose($fp);
mysql_close($con);

i think the main things to remember about parsing csv is that it follows some simple rules:

a)it's a text file so easily opened b) each row is determined by a line end \n so split the string into lines first c) each row/line has columns determined by a comma so split each line by that to get an array of columns

have a read of this post to see what i am talking about

일단 감만 잡히면 아주 쉽게 할 수 있고 아주 유용해집니다.

데이터베이스 연결:

 try {
        $conn = mysqli_connect($servername, $username, $password, $db);
        //echo "Connected successfully"; 
    } catch (exception $e) {
        echo "Connection failed: " . $e->getMessage();
    }

CSV 파일을 읽고 데이터베이스의 테이블에 업로드하기 위한 코드입니다.

   $file = fopen($filename, "r");
            while (($getData = fgetcsv($file, 10000, ",")) !== FALSE) {
                $sql = "INSERT into db_table 
                   values ('','" . $getData[1] . "','" . $getData[2] . "','" . $getData[3] . "','" . $getData[4] . "','" . $getData[5] . "','" . $getData[6] . "')";
                $result = mysqli_query($conn, $sql);
                if (!isset($result)) {
                    echo "<script type=\"text/javascript\">
                            alert(\"Invalid File:Please Upload CSV File. 
                            window.location = \"home.do\"
                          </script>";
                } else {
                    echo "<script type=\"text/javascript\">
                        alert(\"CSV File has been successfully Imported.\");
                        window.location = \"home.do\"
                    </script>";
                }
            }
            fclose($file);

언급URL : https://stackoverflow.com/questions/11448307/importing-csv-data-using-php-mysql

반응형

'sourcecode' 카테고리의 다른 글

OSX Swift 기본 브라우저에서 URL 열기  (0) 2023.09.26
피카소 v/s 이미지로더 v/s 프레스코 vs 글라이드 vs 코일  (0) 2023.09.21
자바 앱과 통합하기 위한 블로그 엔진.어떤 블로그 엔진?  (0) 2023.09.21
Express에서 HTML을 뷰 엔진으로 사용하려면 어떻게 해야 합니까?  (0) 2023.09.21
console.log javascript [Function]  (0) 2023.09.21

'sourcecode'의 다른글

  • 현재글Importing CSV data using PHP/MySQL

관련글

댓글

티스토리툴바