Import of a CSV file into MySQL – database incomplete
I would like to update a database regularly by a CSV file. I have now thought about the following way:
I put the CSV file on the server, establish an SSH connection with Putty and start a php script, which is located in the same folder as the CSV file. Following the code of the php file:
<?php // Connect to the database $dbc = mysqli_connect('XXXXX', 'XXXXXXX', 'XXXXXX', 'XXXXXX'); // Set the charset to utf8 mysqli_set_charset($dbc,"utf8"); // Read the data $handle = fopen("Reimcodes.csv", "r"); // Set $i = 0 for further usage $i=0; // Use fgetcsv function along with while loop to get all of the rows in the file while (($data = fgetcsv($handle, 1000, ';'))) { if($i == 0) { $i++; continue; } if($data[3] == 'NaN') { $data[3] = 0; } // Finally, insert the data into my database. $query = 'INSERT INTO phrase (phrase, codes_type1, codes_type2, lang ) VALUES ("'.$data[0] . '", "' . $data[1] . '", "' . $data[2].'", "' . $data[3].'")'; echo $query; $result = mysqli_query($dbc, $query); if ($result == false) { echo 'Error description <br/>' . mysqli_error($dbc); } } 'DELETE FROM phrase WHERE lang = ""'; ?> Now the script also works with small restrictions. It imports most of the words, but after about 280,000 data I get the message "Terminated" in the command prompt, although there are still about 70,000 data to import.
Furthermore, not all words containing the letters "ä", "ö" or "ü" are imported. Some yes, but some are missing, which I can’t really understand.
I would be happy if someone has an idea how I can get all data imported cleanly.
it might be maximum execution time. use set_time_limt to increase time
<?php set_time_limit(0); // Connect to the database $dbc = mysqli_connect('XXXXX', 'XXXXXXX', 'XXXXXX', 'XXXXXX'); 
