Solution: Parsing a CSV file in MySQL database using PHP (mysqli). How to set the encoding and condition of the selection of elements before entering into the database?

Development | Databases
5.0 (1 ratings)
Input data.So, every day from 1C: Alpha-Auto(special edition 1C: Enterprise 8.3 for SRT and other auto-organizations) a CSV file is downloaded to a specific folder on the hosting(regular shared, not VDS): <

"Parameter 1","Parameter 2","Parameter 3","Parameter 4","Parameter 5"
"Parameter N","Parameter N","Parameter N","Parameter N","Parameter N"

This is a base of spare parts/parts in stock(q.&Gt;0).To parse a file and convert its values ​​into an array(for later use), I use the following code:

$file=file_get_contents('/home/user/site .ru/public_html/upload/catalog.csv');
$lines=explode(PHP_EOL, $file);
foreach($lines as $line) {

This is where the first plug appears.According to the good old tradition, 1C is all encoded in CP1251(or KOI8-R), and, instead of Cyrillic, there are only some crackers in the $array array.Also, in subsequent attempts to make INSERT these values ​​in the database, mysqli does not understand them and gives errors.

Is it possible to somehow save this CSV file or convert it on the fly to UTF-8 format(without BOM, for example) before parsing? Yes, there is Notepad ++, but doing it every day by hand is simply not physically human-resourceing, and I don’t want it anyway, though.

Good.Moving on!

To add values ​​to the database, I write like this:

//Init MySQLi.
//Connect to DB.
if(! $mysqli->real_connect('localhost', $login, $password, $table)) die('Connection error('.mysqli_connect_errno().')'.mysqli_connect_error());
//Loop INSERT.
foreach($array as $row) {
  if(! $mysqli->query("INSERT INTO catalog(id, name, brand, stock, price) VALUES('$row[0]','$row[1]','$row[2]','$row[3]','$row[4]')")) echo" Error(".$mysqli->errno.")".$mysqli->error;
//Close MySQLi connection.

(Table ID cell has UNIQUE and PRIMARY values)

On successful days, this file weighs about 500 Kb(~ 4000 items of the nomenclature).This is the second plug-in, because to load each time anew such quantity, even if it is late at night by Cron, in MySQL it is not good(IMHO).Therefore, the question is: how best to compare each line(array element) from a CSV file with existing lines in the database and enter only those values ​​that have been changed(the main, logical CSV file)?

At the same time, you need to somehow specify the options:
1.If the ID is in the database, but it is not in the CSV file, then do the DELETE element;
2.If there is no such ID in the database, but it is in the CSV file, then do the INSERT, otherwise, skip the element;
3.If such an ID exists both in the database and in the CSV file, then do UPDATE, otherwise, skip the element.

I hope for your help.Thank you in advance!
No attachments
By coding

500 Kb

to load each time again such a number, even if it is late at night by Cron, in MySQL is not good

This is a meager amount, why not good? Especially if only once a day.

And yes, use at least PDO

on April 29th, 2020 (10:21 pm)
All coments
on May 14th, 2020 (11:39 pm)
You must sign in to comment!! LOGIN
We use cookies to give you the best possible experience on our site. By continuing to use the site you agree to our use of cookies. Find out more Accept