逗号在地址时将CSV文件导入MySQL的问题

问题描述：

I'm using a script that I found online to import CSV file into MySQL: http://www.johnboy.com/blog/tutorial-import-a-csv-file-using-php-and-mysql but when the script detects a comma in an address like this My address, CO 80113 in a cell, it also splits there as well.

I've seen a solution where you can save from excel to a Tab Delimited txt file. Then you go into notepad and replace the tabs with semicolons.

Is this the best practice for "fixing the comma in the address" issue?

My end goal is to take a CSV file from Highrise full of hundreds of clients into our MySQL database, then make updates every couple of months so this seems to be a decent script but am I going at this the wrong way?

EDIT : It appears that the part of the PHP code that splits up the cells is this

while ($data = fgetcsv($handle,1000,",","'"));

我正在使用我在网上找到的脚本将CSV文件导入MySQL： http://www.johnboy.com/blog/tutorial-import- a-csv-file-using-php-and-mysql 但当脚本检测到地址中的逗号时，如单元格中的我的地址，CO 80113 code>，它也会在那里分裂为好吧。 p>

我见过一个解决方案，你可以从excel保存到制表符分隔的txt文件。然后你进入记事本并用分号替换标签。 p>


这是“修复地址中的逗号”问题的最佳做法吗？ p> 
 
 
我的最终目标是从Highrise获取完整的CSV文件 数百个客户端进入我们的MySQL数据库，然后每隔几个月进行一次更新，所以这似乎是一个不错的脚本，但我是否采取了错误的方式？ p> 
 
 
 编辑： 似乎PHP代码中拆分单元格的部分是 b>  p> 
 
 
  while（$ data = fgetcsv（$ handle，1000，“，”，  “'”））;  
  code>  pre> 
  div>

答

If you use comma as column separator, then you should quote all string field values, e.g. -

1,'My address, CO 80113'
2,'His address, CO 80114'
4,'Her address, CO 80115'
and so on

Try to use LOAD DATA INFILE statement with FIELDS ENCLOSED BY '\'' option.

答

comma (',') is the hart of CSV file so when you generate the csv file it divide cell when comma found.

So I think that you should replace the comma with the space or any other delimeter. you can use PHP 's default function str_replace() when your final code is generate for CSV.

逗号在地址时将CSV文件导入MySQL的问题

相关推荐