How to import parse csv file to Maria DB?

Question

I have a php code where I am parsing the csv file and I want to import the parse data to maria db. How can I do that?

My code:

<?php 

$row = 1;
if (($handle = fopen("users.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        echo "<p> $num fields in line $row: <br /></p>\n";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo $data[$c] . "<br />\n";
        }
    }
    fclose($handle);
}

?>

Output:

Task1 % php user_upload.php
<p> 3 fields in line 1: <br /></p>
name<br />
surname<br />
email   <br />
<p> 3 fields in line 2: <br /></p>
John<br />
smith<br />
jsmith@gmail.com<br />
<p> 3 fields in line 3: <br /></p>
HaMish<br />
JONES<br />
ham@seek.com<br />
<p> 3 fields in line 4: <br /></p>
Phil <br />
CARRY   <br />
phil@open.edu.au  <br />
<p> 3 fields in line 5: <br /></p>
Johnny<br />
O'Hare<br />
john@yahoo.com.au<br />
<p> 3 fields in line 6: <br /></p>
Mike<br />
O'Connor<br />
mo'connor@cat.net.nz<br />
<p> 3 fields in line 7: <br /></p>
WILLIAM<br />
SMYthe<br />
happy@ent.com.au<br />
<p> 3 fields in line 8: <br /></p>
HAMISH<br />
jones   <br />
ham@seek.com<br />
<p> 3 fields in line 9: <br /></p>
Sam!!  <br />
WalTERS<br />
sam!@walters.org    <br />
<p> 3 fields in line 10: <br /></p>
Daley<br />
thompson<br />
daley@yahoo.co.nz<br />
<p> 3 fields in line 11: <br /></p>
kevin<br />
Ruley<br />
kevin.ruley@gmail.com<br />
<p> 3 fields in line 12: <br /></p>
Edward <br />
JIKES<br />
edward@jikes@com.au<br />
<p> 1 fields in line 13: <br /></p>
<br />
<p> 1 fields in line 14: <br /></p>
<br />
<p> 1 fields in line 15: <br /></p>
<br />
<p> 1 fields in line 16: <br /></p>
<br />
<p> 1 fields in line 17: <br /></p>
<br />
<p> 1 fields in line 18: <br /></p>
<br />
<p> 1 fields in line 19: <br /></p>
<br />
<p> 1 fields in line 20: <br /></p>
<br />
<p> 1 fields in line 21: <br /></p>
<br />
<p> 1 fields in line 22: <br /></p>
<br />

Also, for some reason it keep printing the extra lines even though I only have just 12 lines in my csv file. But how can I store the parse data to Maria DB? Also, I want it to capitalize the name and surname and lower case the email before inserting data to MYSQL. I am new to it so I am not sure, any help is appreciated.

Answer 1

Your empty lines are probably the result of trailing space in the file. You can instead use file and array_map, with file using the appropriate flags to skip empty lines:

$lines = file('users.csv', FILE_IGNORE_NEW_LINES|FILE_SKIP_EMPTY_LINES)
$csv = array_map('str_getcsv', $lines);

Then, get the column names (snipped from the first row of the array):

$col_names = array_shift($csv);

Then, tidy up your data and build an associative array while you're at it:

$users = [];

foreach($csv as $row) {
    $users[] = [
        $col_names[0] => ucwords(strtolower($row[0])), // name: 
        $col_names[1] => ucwords(strtolower($row[1])), // surname: 
        $col_names[2] => strtolower($row[2]), // email: 
    ];
}

Here we normalize the names and e-mails with basic functions like strtolower and ucwords, please see the manual for further details. This will result in an array like the following:

$users = [
    ['name' => 'John', 'surname' => 'Smith', 'email' => 'jsmith@gmail.com'],
    ['name' => 'Hamish', 'surname' => 'Jones', 'email' => 'ham@seek.com'],
    // ....
];

Update: For a live example of the above, see https://3v4l.org/SFYjd

Then, you will obviously need a MySQL database table that matches these. To keep things simple, have an id INT field as a unique auto-increment index, and the rest as regular VARCHAR columns.

Once you've created the table, all that remains is INSERTing the data. For very basic reference on that, see here. If your data is untrusted, you'll want to use prepared statements (also see PHP manual) instead, whether using MySQLi or PDO. Consider PDO (manual).

General MySQL database table design and data inserting are however beyond this answer; they are the homework, so please read up and experiment, and post a new question if/when you run into specific problems with your database inserts.

P.S. If your data is already prepped, you can also import CSV directly to MySQL using LOAD DATA INFILE. See for example, SO: How do I import CSV file into a MySQL table?, also Import CSV File Into MySQL Table (with examples on transforming data in the process), MySQL Manual. (See the section on "Input Preprocessing", in case you want to handle the data clean-up with MySQL functions instead of with PHP; and just toss your CSV as-is from PHP to MySQL.)

Answer 2

I would first parse the csv into an associative array, then generate a bulk insert query and send it to the database.

Consider if you need to validate each value so the database doesn't throw an error or handle the possible error in some way.

If you're not sure how to run the query on MariaDB (which would be the same as MySQL for a task like this) I suggest you search github for: "MySQL PHP class"

Check this link for a function to parse the csv. Basically, it uses fgetcsv() function in a while() loop.

Then, once you have the array with the data, you can do a foreach() loop to generate an insert query. For example:

INSERT into users (field_1,field_2,field_3,field_4,field_5) values 
(value_1,value_2,value_3,value_4,value_5),
(value_1,value_2,value_3,value_4,value_5);

You might also find this post to be useful.

Last but not least, search the internet. It's full of examples to achieve this task :)

Answer 3

insert into table (column1, columne2) values
(
  json_value(concat('{"data": [', csvstringarow, ']}'), '$.data[0]'),
  json_value(concat('{"data": [', csvstringarow, ']}'), '$.data[1]')
)