load csv into mysql_Load a csv file into mysql via php

最佳答案

It can be done by using the fgetcsv PHP function.

Afterwards, use the Mysql INSERT function in order to save that data in the Database.

参考答案2

MySQL has a feature LOAD DATA INFILE, which allows it to import a CSV file directly in a single SQL query, without needing it to be processed in a loop via your PHP program at all.

Simple example:

$query = <<

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.

Hope that helps

author

参考答案3

Give this a try. This uses a function to read the lines of the file into an array of strings, then loops through each line and stores the column values to a table. Obviously you will need to insert your db connection settings and proper query.

function loadData()

{

$lines = readInputFromFile("myCSV.csv");

for ($i = 0; $i < count($lines); $i++)

(

$columns = explode(",", $lines[$i]);

$conn = new PDO("mysql:host=YOUR_HOST;port=YOUR_PORT;dbname=YOUR_DBNAME", "UserName", "Password");

$query = "INSERT INTO MYTABLE VALUES ('".$columns[0]."', '".$columns[1]."', '".$columns[2]."')";

$stmt = $conn->prepare($query);

$stmt->execute();

)

}

function readInputFromFile($file)

{

$fh = fopen($file, 'r');

while (!feof($fh))

{

$ln = fgets($fh);

$parts[] = $ln;

}

fclose($fh);

return $parts;

}

参考答案4

If you are looking for the easiest method to import csv file into mysql, then go with following:

Eg:

LOAD DATA LOCAL INFILE 'import.csv' INTO TABLE from_csv FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (si, distro, available);

For more reading:

参考答案5

see the code below

csv2sql("pre_employee","ch_data.csv");

function csv2sql($table_name,$csvname)

{

$fl=fopen($csvname,r);

//if(!file_exists('a.txt'))

//{

$flw=fopen("a.txt",'w');

//}

$sql='';

$i=2;

//echo $csvname.$data=fgetcsv($fl,1000,",");die();

while(($data=fgetcsv($fl,1000,","))!=false)

{

$sql.="insert into ".$table_name." values('".$data[0]."','".$data[2]."','".$data[3]."');".chr(13);

$i++;

}

//echo $sql;

fwrite($flw,$sql);

fclose($flw);

fclose($fl);

}

a.txt is the filename saving your sql. nor import this txt file to your mysql database.