将CSV文件导入MySQL数据库

I have records into .CSV file and I want to import them into MySQL database.

我有记录到。csv文件中，我想将它们导入到MySQL数据库中。

Whenever I import the .CSV I get the message Import has been successfully finished... but only 79 out of 114 records are be inserted into the database.

每当我导入.CSV时，我得到的消息导入已经成功完成……但是在114条记录中，只有79条被插入到数据库中。

When I try to import the .CSV file with 411 records, just 282 are be inserted. The CSV file which got 411 records includes two categories of records Active and Sold whereby 114 records are Active.

当我尝试导入包含411条记录的. csv文件时，只插入了282条记录。获得411条记录的CSV文件包括两类活动记录和销售记录，其中114条记录是活动的。

Has someone gotten this type of problem? If so what should be done?

有人遇到过这种问题吗?如果是这样，应该做什么?

3 个解决方案

#1

I wrote my own csv importer with php. I use php command fgetcsv to read the csv file and then I use mysql insert command in a loop.

我用php编写了自己的csv导入程序。我使用php命令fgetcsv读取csv文件，然后在循环中使用mysql insert命令。

$handle = fopen($this->file, "r");
$i=0;
$delimiter = ($this->fieldDelimiter == 'TAB') ? chr(9) : $this->fieldDelimiter;
while (($data = fgetcsv($handle, 10000, $delimiter)) !== FALSE)
{
     $mydata[] = $data;
}
fclose ($handle);
reset ($mydata);
if ($this->CSVhasTitle)
{
      $mydata = array_slice($mydata,1); //delete first row
}

Then I loop through my array and I use mysql insert:

然后循环遍历数组，然后使用mysql insert:

foreach ($mydata as $value) 
{
     INSERT INTO $table (...) VALUES (....)
}

But I add exact columnnames into the array before the loop. I've an array of all columnames.

但是我在循环之前向数组中添加了确切的列名。我有一个列名数组。

#2

I had this problem too. Even though its a bit old and these recommendations go in one direction here is the solution I found. I was creating a large database and import and had the same thing happen, after trial and trials I realized that a key was created somehow assigned (I didn't recognize it because Iwas using the new skin and always use the old skin). Took out the key that somehow got assigned not by me and left the primary only and boom, absorbed the data upload. Also, had issues with view now, and request times out with respect to your question. I pushed up the viewer display a lot to thousands and now stuck, cannot access the config file anywhere with my CP. So as will hang and hosting customer support to lazy to read my concerns and override it on their end-I will have to remove whole table instead of any DROP as can't even run SQL as freezes with overload. So, food for thought would be to keep you table view down, which sucks like in my case b/c I need to look at 17,000 rcords visually quickly to ensure my .csv was correct rather then functions as if issues then can spot them and correct in the control which makes more sense to me anyway.

我也有这个问题。尽管它有点旧，这些建议朝着一个方向发展，这就是我找到的解决方案。我正在创建一个大的数据库并导入，并且发生了同样的事情，经过反复的试验和试验，我意识到一个键被以某种方式分配(我没有识别它，因为我使用的是新的皮肤，而且总是使用旧的皮肤)。拿出不知怎么分配给我的钥匙，只留下了主键，轰了一声，吸收了数据上传。还有，现在有一些问题，关于你的问题，请求超时。我推高了查看器显示很多数以千计,现在卡住了,不能在任何地方访问配置文件与我的CP。将挂起和托管客户支持懒惰阅读我的担心和覆盖它最后我不得不删除整个表,而不是下降甚至无法运行SQL与冻结过载。所以,精神食粮会让你表视图,这糟透了像在我的例子中b / c我需要看17000尽快rcords视觉,以保证我. csv是正确的而不是功能如果问题才能发现它们和正确的控制对我更有意义。

#3

Take a look at your CSV file. It very likely contains something like

看看您的CSV文件。它很可能包含类似的东西

1,2,"some data",1
2,5,"data,with,comma",2

If you don't specify COLUMNS OPTIONALLY ENCLOSED BY '"' (SINGLE_QUOTE DOUBLE_QUOTE SINGLE_QUOTE) then the commas embedded in the string data in the second row, third column will not be imported properly.

如果您不指定“”(SINGLE_QUOTE DOUBLE_QUOTE SINGLE_QUOTE)包含的列，那么在第二行中的字符串数据中嵌入的逗号将不会正确地导入第三列。

Check the CSV to see what enclosure character is being used and specify that in the phpmyadmin interface.

检查CSV，查看在phpmyadmin界面中使用了什么外壳字符并指定它。

#1