March 16, 2012
CSV into MySQL using PHP
Question by user1183307
I’m trying to import data from my students.csv file into mysql using php. The entries in the csv file is in such a way that column (student_number, fname, lname, level) will be inserted into biodata table..
I’m also uploading the student.csv file from my computer.
When I run the page I dont get anything out on the screen.
session_start();
require('includes/dbconnect.php');
require 'includes/header.inc.php';
//check for file upload
if (isset($_FILES['csv_file']) && is_uploaded_file($_FILES['csv_file']['tmp_name'])) {
//upload directory
$upload_dir = "C:UsersDOTMANDocumentsstudents.csv";
//create file name
$file_path = $upload_dir . $_FILES['csv_file']['name'];
//move uploaded file to upload dir
if (!move_uploaded_file($_FILES['csv_file']['tmp_name'], $file_path)) {
//error moving upload file
echo "Error moving file upload";
}
//open the csv file for reading
$handle = fopen($file_path, 'r');
//turn off autocommit and deletethe bio data
mysql_query("SET AUTOCOMMIT=0");
mysql_query("BEGIN");
mysql_query("TRUNCATE TABLE biodata") or die(mysql_error());
while (($data = fgetcsv($handle, 1000, ',')) !== FALSE) {
//Access field data in $data array ex.
$student_number = $data[0];
$fname = $data[1];
$lname = $data[2];
$level = $data[3];
//Use data to insert into db
$query = "INSERT INTO biodata (student_number, fname, lname, level)
VALUES ('$student_number', '$fname', '$lname', '$level')";
mysql_query($query) or die (mysql_error());
}
}
Answer by Starx
Solution using PHP
$file = 'path/to.csv';
$lines = file($file);
$firstLine = $lines[0];
foreach ($lines as $line_num => $line) {
if($line_num==0) { continue; } //escape the header column
$arr = explode(",",$line);
$column1= $arr[0];
$column2= $arr[1];
echo $column1.$column2."<br />";
//put the mysql insert statement here
}
