csv导入mysql格式转换,这是CSV文件的最佳格式导入到MySQL数据库

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:

Should the date be "2015-10-06"

Should columns 2, 3, and 4 be in string format with double quotes e.g. "0427"

For the column headers, should I remove all the spaces and the brackets

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.

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