Import data into MySQL from CSV via PHP script

Hello everyone. My name is Ray. I would like to share how to import data from CSV file into MySQL database via PHP script
Firstly, take a look on the screenshot of CSV file below:

Take a look on the PHP script below. Please put them in the same repository.


// Connect to the database
$dbc = mysqli_connect('Your location', 'Your MySQL user_name',
'Your MySQL password', 'Your Database Name');

// Set the charset to utf8

// Read the data
$handle = fopen("The file name.csv", "r");

// Set $i = 0 for further usage

// Use fgetcsv function along with while loop to get all of the rows in the file
while (($data = fgetcsv($handle, 1000, ',')))

// Since the first line in the file is column name,
// so we are going to skip the first line.
// When $i = 0, it should be in the first line,
// it gets into the if function,
// and the continue skip all the codes afterwards
// and get back to the top of the loop,
// and in this round the $i = 1. So it will not get into if function,
// only skip the first line that we don't want.

if($i == 0)

// As shown on the image of csv, there is a string "NaN" on the rainfall column.
// If we want to set the data type of this column as float or decimal,
// we should take care of this string before inserting into the database.
// So we use condition sentence to replace 'NaN' with 0,
// and then it will not cause any problem when inserting data into this
// column with data type as either float or decimal.
if($data[2] == 'NaN')
$data[2] = 0;

// Finally, we insert the data into our database.
$query = 'INSERT INTO rainfall (district, date, rainfall)
VALUES ("'.$data[0] . '", "' . $data[1] . '", "' . $data[2].'")';

echo $query;
$result = mysqli_query($dbc, $query);

if ($result == false)
echo 'Error description <br/>' . mysqli_error($dbc);

Finally, execute the script in terminal, php -f scriptName, and here you go!

Import Chinese into Database without garbles


Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now