How to Use PHP Script to Import CSV Data Into MySQL

Written by lisa nelson
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Use PHP Script to Import CSV Data Into MySQL
Importing CSV files into MySQL is a common problem. (Stockbyte/Stockbyte/Getty Images)

Importing CSV data into the MySQL open source database can be challenging. People often prefer organising the information in spreadsheets while they are writing it, but once they are done they must import it into MySQL, a cumbersome task to do by hand. A straightforward way to bypass this issue is to create a PHP script that automatically imports all the data.

Skill level:


  1. 1

    Open the CSV file that you want to open in a text editor such as Notepad and examine the file's format. Specifically, note what character is used as a separator between fields. For example, if the CSV file contains the text "John;Smith;6;58" note that the separator is the semicolon (";").

  2. 2

    Check whether the first line of the file contains the names of fields that will also exist in the database. For example, if each line contains a person's first name, last name, height and age, make sure the first row contains the appropriate column heading, such as "firstname;lastname;height;age". If it does not exist, or it does not match the fields in the database, you should correct it.

  3. 3

    Create the MySQL table that the CSV file will be imported to, if it does not already exist. If you are not sure how to do this, consult the resources section of this article. Make sure that the names of the fields in the MySQL table match those in the CSV file.

  4. 4

    Copy the PHP script below into a text editor such as Notepad, and enter your database information into the second line. Change the capitalised words to reflect your information. For example, remove "USERNAME" and enter your own username. Save the file as a .php file. In Notepad this is done by pressing "File" and then "Save As" on the top menu bar, choosing "All Files" in the drop-down list marked "Save as type:" and pressing "Save".

    Here is the code for you to copy:


    $dbInfo = array('host Name' => 'HOSTNAME', 'dbName' => 'DB_NAME', 'username' => 'USERNAME', 'password' => 'PASSWORD', 'table Name' => 'TABLE_NAME');

    fwrite(STDOUT, "Please enter full path to CSV file: \n");

    $fullpath = fgets(STDIN);

    fwrite(STDOUT, "Please enter the field separator: \n");

    $sep = fgets(STDIN);

    $first = true;

    if (($handle = fopen($fullpath, "r")) === FALSE) die("Unable to open the CSV file you entered");

    for($i = 0; ($data = fgetcsv($handle, 0, $sep)) !== FALSE; $i++) {

    $num = count($data);

    if($first) {

    if($num == 0 || ($num == 1 && $data[0] == null)) die("CSV file must contain at least one field!");

    $totalRows = $num;

    $fieldNames = $data;

    $first = false;

    } else {

    if($num != $totalRows) die("Row does not contain the required number of fields.");

    $values[$i] = $data;



    $con = mysql_connect($dbInfo['hostName'],$dbInfo['username'],$dbInfo['password']) or die('Could not connect: ' . mysql_error());

    mysql_select_db($dbInfo['dbName'], $con);

    foreach($values as $v) {

    foreach($v as &$i) {

    $i = mysql_real_escape_string($i);

    mysql_query("INSERT INTO " . $dbInfo['tableName'] ." (" . implode(',', $fieldNames) . ") VALUES ('" . implode("','", $v) . "')");




  5. 5

    Open the command line. In Windows, this is done by pressing the Start button, choosing "All Programs", and then "Accessories" and pressing on "Command Prompt". Enter the path to your PHP executable and the path to the PHP script to run it; for example, 'C:\PHP5\php.exe "C:\PHP Scripts\script.php"'. Enter the full path to the CSV file when prompted by the script and wait for the script to run. This may take some time. If any errors occur, make sure you followed all the steps properly.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.