这是CSV文件的最佳格式导入到MySQL数据库 [英] Is this the best format of CSV file for importing into a MySQL database

问题描述

我有以下格式的数据（样本数据，有许多行）：

 录制平仓日期，数字1，2号，数据量（字节），设备制造商，设备模型，产品说明
2015年10月6日，0427，70060，137765，韩国三星，三星SM-G900I，$ 39个选项
2015年10月6日，7592，55620，0，苹果公司，苹果iPhone 6（A1586），一些文本＃16
...
 

我想知道的是，什么是导入到mysql的这个最佳格式/做法？

一些具体的问题是：

1. 如果日期是2015年10月6日


2. 应该列2，3和4是在用例如双引号串格式0427


3. 对于列标题，我应该删除所有的空格和括号


4. 还有什么

也许我的数据会更好，看起来像这样将它导入到我的数据库之前：

• 用下划线替换所有的空格


• 删除括号

通过去除双引号
• 转列2，3，4成值

这将是这样的：

<$p$p><$c$c>\"Rec_Open_Date\",\"number_1\",\"number_2\",\"Data_Volume_Bytes\",\"Device_Manufacturer\",\"Device_Model\",\"Product_Description\"
2015年10月6日，0427,70060,137765，韩国三星，三星SM-G900I，$ 39个选项
2015年10月6日，7592,55620,0，苹果公司，苹果iPhone 6（A1586），一些文本＃16
...

同样只是为了寻找最佳实践那里。

接下来的问题将有一个分析器，能做到这一切，也许在bash或其他等效？

解决方案

1.One方式做，这是通过导入CSV文件到mysql，你可以使用phpMyAdmin等工具。

2.You可以试试这个code。它的PHP脚本文件转换到MySQL。

 ＆LT; PHP
$ databasehost =localhost的;
$数据库名称=测试;
$ databasetable =样本;
$ databaseusername =测试;
$ databasepassword =;
$ fieldseparator =，;
$ lineseparator =\\ n;
$ csvfile =filename.csv;
如果（！file_exists（$ csvfile））{
死（找不到文件确保您指定正确的路径。）;
}
尝试{
$ PDO =新PDO（MySQL的：主机= $ databasehost; DBNAME = $数据库名称，
    $ databaseusername，$ databasepassword，
    阵列（
        PDO :: MYSQL_ATTR_LOCAL_INFILE =＆GT;真正，
        PDO :: ATTR_ERRMODE =＆GT; PDO :: ERRMODE_EXCEPTION
    ）
）;
}赶上（PDOException $ E）{
死（数据库连接失败：$ E-＆GT;的getMessage（））;
}$ affectedRows = $ pdo-＆GT;执行exec（'
LOAD DATA LOCAL INFILE$ pdo-方式＆gt;报价（$ csvfile）。 INTO TABLE`$ databasetable`
  TERMINATED田野$ pdo-方式＆gt;引号（fieldseparator $）。
  LINES TERMINATED BY$ pdo-方式＆gt;。引号（$ lineseparator）） '）;
从这个CSV文件加载共计$ affectedRows记录\\ n呼应;
？＆GT;
 

3.You可以使用任何框架或工具来这样做。

I have data in the following format (sample data, there are many rows):

"Rec Open Date","number 1","number 2","Data Volume (Bytes)","Device Manufacturer","Device Model","Product Description"
"2015-10-06","0427","70060","137765","Samsung Korea","Samsung SM-G900I","$39 option"
"2015-10-06","7592","55620","0","Apple Inc","Apple iPhone 6 (A1586)","some text  #16"
...

what I want to know is, what is the best format/practice for importing this into mysql?

Some specific questions are:

1. Should the date be "2015-10-06"
2. Should columns 2, 3, and 4 be in string format with double quotes e.g. "0427"
3. For the column headers, should I remove all the spaces and the brackets
4. Anything else

maybe my data would be better looking like this before importing it into my database:

• Replace all spaces with underscore
• remove brackets
• turn columns 2, 3, and 4 into values by removing the double quotes

which would look like this:

"Rec_Open_Date","number_1","number_2","Data_Volume_Bytes","Device_Manufacturer","Device_Model","Product_Description"
"2015-10-06",0427,70060,137765,"Samsung Korea","Samsung SM-G900I","$39 option"
"2015-10-06",7592,55620,0,"Apple Inc","Apple iPhone 6 (A1586)","some text  #16"
...

Again just looking for best practice out there.

The next question will be is there a parser that can do all this, maybe in bash or other equivalent?

解决方案

1.One way to do this is by importing the csv file into mysql, you can use tools like phpMyAdmin.

2.You can try this code. Its a php script to convert the file to mysql.

<?php    
$databasehost = "localhost";
$databasename = "test";
$databasetable = "sample"; 
$databaseusername="test"; 
$databasepassword = "";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = "filename.csv";
if(!file_exists($csvfile)) {
die("File not found. Make sure you specified the correct path.");
}
try {
$pdo = new PDO("mysql:host=$databasehost;dbname=$databasename", 
    $databaseusername, $databasepassword,
    array(
        PDO::MYSQL_ATTR_LOCAL_INFILE => true,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    )
);
} catch (PDOException $e) {
die("database connection failed: ".$e->getMessage());
}

$affectedRows = $pdo->exec('
LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." INTO TABLE `$databasetable`
  FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)."
  LINES TERMINATED BY ".$pdo->quote($lineseparator))." ');
echo "Loaded a total of $affectedRows records from this csv file.\n";
?>

3.You can use any framework or tool to do so.

这篇关于这是CSV文件的最佳格式导入到MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！