加载数据文件，用逗号处理字段 [英] load data infile, dealing with fields with comma

问题描述

在使用加载数据infile时，如何处理带有逗号的字段?我有这个查询:

How do we deal with field with comma when using load data infile? i have this query:

$sql = "LOAD DATA LOCAL INFILE '{$file}' INTO TABLE sales_per_pgs 
        FIELDS TERMINATED BY ','
        LINES TERMINATED BY '\n'
        IGNORE 1 LINES
        (@user_id, @account_code, @pg_code, @sales_value)
        SET
        user_id = @user_id, 
        account_code = @account_code,
        product_group_code = @pg_code,
        sales_value = REPLACE(@sales_value, ',', ''),
        company_id = {$company_id},
        year = {$year},
        month = {$month}";

，csv中的一行如下所示:

and a line from the csv looks like this:

139, pg89898, op89890, 1,000,000.00

其中1,000,000.00是销售价值.

当前，插入到我的数据库中的只是"1.

Currently, what is inserted in my database is only "1.

编辑

用户下载具有以下列的表单:

The user downloads a form with columns like:

user id，account id，pg id，sales value

其中填充了前三列user id，account id，pg id，而sales value列为空白，因为用户必须手动填充它...用户使用MS excel来做到这一点...

where the first three columns user id, account id, pg id, were populated and the sales value column is blank because the user has to fill it up manually... the user uses MS excel to do that...

表格填写完毕后，他现在将其上载，我正在其中使用load data infile命令...

after the form is completed, he will now upload it, in which i am using the load data infile command...

推荐答案

您的内容应该看起来像这样:

Your content should really look like:

"139", "pg89898", "op89890", "1,000,000.00"

然后，您可以在命令中添加以下内容:

Then you could add the following to the command:

ENCLOSED BY '"' ESCAPED BY "\\"

您将不会有问题.

此外，如果您没有任何带有,的段落或字符串，可以尝试以下方法:

Also, somethign you could try if you don't have any paragraphs or strings with , in them:

FIELDS TERMINATED BY ', '

这篇关于加载数据文件，用逗号处理字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！