如何将解析 csv 文件导入 Maria DB?

2022-01-15 00:00:00 csv sql database mariadb php

我有一个解析 csv 文件的 php 代码，我想将解析数据导入 maria db.我该怎么做?

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?

我的代码:

<?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>
";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo $data[$c] . "<br />
";
        }
    }
    fclose($handle);
}

?>

输出:

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 />

此外，由于某种原因，即使我的 csv 文件中只有 12 行，它也会继续打印额外的行.但是如何将解析数据存储到 Maria DB?另外，我希望它在将数据插入 MYSQL 之前将姓名和姓氏大写并小写电子邮件.我是新手，所以我不确定，感谢任何帮助.

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.

推荐答案

您的空行可能是文件中尾随空格的结果.您可以改为使用 file 和 array_map，而 file 使用适当的标志来跳过空行:

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: 
    ];
}

在这里，我们使用 strtolower 和 ucwords，详情请看手册.这将产生一个如下所示的数组:

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'],
    // ....
];

更新:有关上述内容的实时示例，请参阅 https://3v4l.org/SFYjd

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

然后，您显然需要一个与这些匹配的 MySQL 数据库表.为简单起见，将 id INT 字段作为唯一的自动增量索引，其余作为常规 VARCHAR 列.

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.

创建表后，剩下的就是INSERT数据.有关这方面的非常基本的参考，参见此处.如果您的数据不受信任，您需要使用 prepared statements(另请参阅 PHP 手册) 代替，是否使用 MySQLi 或 PDO.考虑 PDO(手册).

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).

然而，一般的 MySQL 数据库表设计和数据插入超出了这个答案；它们是家庭作业，因此请阅读并进行实验，如果/当您遇到数据库插入的特定问题时，请发布一个新问题.

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.

附:如果您的数据已经准备好，您还可以使用 LOAD DATA INFILE 将 CSV 直接导入 MySQL.参见例如，SO:如何导入 CSV 文件导入 MySQL 表?，还 将 CSV 文件导入 MySQL 表 (带有在过程中转换数据的示例)，MySQL 手册.(如果您想使用 MySQL 函数而不是 PHP 来处理数据清理，请参阅输入预处理"部分；只需将 CSV 原样从 PHP 扔到 MySQL.)

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.)

相关文章