php:将多个csv文件导入mysql表 [英] php: importing multiple csv files into mysql table

问题描述

<?php

error_reporting(E_ALL);
ini_set('memory_limit', '512M');
ini_set('display_errors', 'ON');

$host = "127.0.0.1"; // Host name
$username = "root"; // Mysql username
$password = ""; // Mysql password
$db_name = "test2"; // Database name
$tbl_name = "tcg_unique";

$con = mysql_connect("$host", "$username", "$password")or die("cannot connect");
$db_con = mysql_select_db($db_name) or die("cannot select DB");
$charset = mysql_set_charset('utf8',$con);

$dir_iterator = new RecursiveDirectoryIterator("/Users/jacksons/Dropbox/MTG/SQL staging");
$iterator = new RecursiveIteratorIterator($dir_iterator, RecursiveIteratorIterator::SELF_FIRST);

foreach ($iterator as $file) {
echo $file, "\n";
if(strpos($file, '.csv') !== false){
mysql_query("LOAD DATA LOCAL INFILE '$file' INTO TABLE $tbl_name") or die (mysql_error());
}
else{
    print "else";
}
}

我想从一个目录加载多个csv文件.我正在打印所有文件路径，但无法将数据加载到表中.我在mysql中尝试了一个，然后导入就没问题了(所有列名都匹配了).任何帮助将不胜感激.

I am tying to load multiple csv files from a directory. I am getting all the file path's to print out, but i am not able to get the data to load into the tables. I tried one in mysql and it imported with no problem (all the column names matched). Any help would be greatly appreciated.

csv看起来像: 卡名供应商条件价格装运数量日期 奥里斯科斯国王游戏城堡附近的造币厂Brimaz 18 0.5 4 5/30/14 毁灭之波FTW游戏Kiora轻玩13.09 NA 1 5/30/14 薄荷附近的Kruphix Chicagoland Games的高尔夫球手11.68 0.75 3 5/30/14

csv looks like: cardname vendor condition price shipping quantity Date Brimaz, King of Oreskos Game Citadel Near Mint 18 0.5 4 5/30/14 Kiora, the Crashing Wave FTW Games Lightly Played 13.09 NA 1 5/30/14 Courser of Kruphix Chicagoland Games Near Mint 11.68 0.75 3 5/30/14

推荐答案

如果文件实际上是具有以逗号分隔的字段的csv文件，则必须指定字段分隔符.

If your file is actually a csv file with fields separated by comma, you must specify the field separator.

LOAD DATA INFILE '$file' INTO TABLE $tbl_name FIELDS TERMINATED BY ','

如果您未声明字段分隔符，则mysql会将字段分隔符视为制表符(\ t).

If you don't state the field separator mysql considers the field separator as tab (\t).

如果文件是用回车符创建的，那么奇怪的是Windows应用程序会这样做，您还需要在语句末尾添加:

In case the file is created with return carriage, monstly windows applications do that, you also need to add in the end of the statement:

LINES TERMINATED BY '\r\n'

这篇关于php:将多个csv文件导入mysql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！