How to Import CSV Files Into MySQL Using PHP
- 1). Open a blank text file in any text editor.
- 2). Type the line
<?php
to start the PHP script. - 3). Type the lines
$hostname = "localhost";
$db_name = "example";
$table_name = "test";
$username ="username";
$passwd = "password";
$f_separator = ",";
$l_separator = "\n";
$filename = "filename.csv";
to create the variables used in the script. Replace the values of the variables with the correct information for your database. - 4). Type the lines
if(!file_exists($filename)) {
echo "File not found. Stopping script.".PHP_EOL;
exit;
}
To check if the file exists. If the file does not exist the line "File not found. Stopping script." will be printed to the screen and the script will end. - 5). Type the lines
$file = fopen($filename,"r") or die ("Cannot open file. Stopping script.".PHP_EOL);
To open the file. If the file cannot be opened, the line "Cannot open file. Stopping script." will be printed to the screen and the script will end. - 6). Type the line
$size = filesize($filename) or die ("File is empty".PHP_EOL);
get the size of the file. If the file is empty, the line "File is empty." will be printed to the screen and the script will end. - 7). Type the lines
$content = fread($file,$size);
fclose($file);
to read the contents of the file into the "$content" array and close the file. - 8). Type the lines
$con = @mysql_connect($hostname,$username,$passwd) or die(mysql_error());
@mysql_select_db($db_name) or die(mysql_error());
to connect to the database and select the database table. If the script cannot connect to the database, the MySQL error will be printed to the screen and the script will end. - 9). Type the following lines
$lines = 0;
$l_array = array();
to create the variables used to split each line of the CSV file into the separate fields. - 10
Type the lines
foreach(split($l_separator,$content) as $line) {
$lines++;
$line = trim($line," \t");
$line = str_replace("\r","",$line);
$line = str_replace("'","\'",$line); //Escape special characters
$linearray = explode($f_separator,$line);
$linemysql = implode("','",$linearray);
to separate each line into the fields that will be added to the database. - 11
Type the lines
$query = "INSERT INTO $table_name VALUES('$linemysql');";
@mysql_query($query);}
to send the data to the database. - 12
Type the lines
mysql_close($con);
?>
to close the MySQL connection and end the PHP script.
Source...