有效的CSV文件导入失败，数据在双引号（"）和字段分隔符：字段开头 [英] Valid CSV filed import fails with Data between close double quote (") and field separator: field starts with

问题描述

我使用的cmd是：

< BLOCKQUOTE>

$ BQ负载--field_delimiter = ^ --quote = '' --allow_quoted_newlines
--allow_jagged_rows --ignore_unknown_values wr_dev.drupal_user_profile GS：//fls_csv_files/user_profileA.csv
uid：string，first_name：string，last_name：string，category_id：string，logo_type：string，country_id：string，phone：string，phone_2：string，address：string，address_2：string，city：string，state：string ，zip：string，company_name：string，created：string，updated：string，subscription：string

报告的错误是

 文件：0 /行：1409 /字段：14，紧密双引号（）
和字段分隔符之间的数据：字段从以下开始：< Moreno L> 
  

样本数据为：

  $ sed -n'1409,1409p'user_profileA.csv 
 $ 1893 ^Moreno^Jackson^ 17 ^ 0 ^ 1 ^517-977-1133^517 -303-3717^^^^^^Moreno L Jackson \\激动人心的！\\^ 0 ^ 1282240785 ^ 1 
  
 
 
  
 
 
 > SELECT * INTO TERMINATED BY '^' 
的任选ENCLOSED BY '' 结尾BY '\\\
' $ b $ 
 LINES OUTFILE '/opt/mysql_exports/user_profileA.csv' 
的各场b FROM p; 
  
为什么我在BQ中得到错误信息？如何从MySQL CSV文件中正确导出（ CR 和 LF 混合，因为它是来自Windows或Mac的用户输入）
 
 
'p>作业ID的夫妇：结果，
作业ID：需氧锻-504：bqjob_r75d28c332a179207_0000014710c6969d_1结果，
作业ID：需氧锻-504：bqjob_r732cb544f96e3d8d_0000014710f8ffe1_1 
 
 
 
 更新 
 
 
 
显然这更重要。我使用了 5.5.34-MariaDB-wsrep-log  INTO OUTFILE，它们都是错误或错误，但是我得到了无效的CSV导出。我不得不使用其他工具来导出正确的CSV。 （工具：SQLYog）
 
 
它有双引号的问题，例如Field 14在这里有错误：
 
 
  3819 ^ Ron ^ Wolbert ^ 6 ^ 0 ^ 1 ^ 6123103169 ^^^^^^^LilRon's ^ 0 ^ 1282689026 ^ 1 
  
 
 
所以不是：
 
 
 Moreno L Jackson \ MOTIVATOR！\... 
  
有： 
 
 
 Moreno L JacksonTHE MOTIVATOR！... 
  
 
I am trying to import a CSV file into BQ from GS.

The cmd I use is:

  
$ bq load --field_delimiter=^ --quote='"' --allow_quoted_newlines
  --allow_jagged_rows --ignore_unknown_values wr_dev.drupal_user_profile gs://fls_csv_files/user_profileA.csv
  uid:string,first_name:string,last_name:string,category_id:string,logo_type:string,country_id:string,phone:string,phone_2:string,address:string,address_2:string,city:string,state:string,zip:string,company_name:string,created:string,updated:string,subscription:string
the reported error is
File: 0 / Line:1409 / Field:14, Data between close double quote (")
and field separator: field starts with: <Moreno L>
sample data is:
$ sed -n '1409,1409p' user_profileA.csv
$ 1893^"Moreno"^"Jackson"^17^0^1^"517-977-1133"^"517-303-3717"^""^""^""^""^""^"Moreno L Jackson \"THE MOTIVATOR!\" "^0^1282240785^1
which was generated from MySQL with:
SELECT * INTO OUTFILE '/opt/mysql_exports/user_profileA.csv' 
FIELDS TERMINATED BY '^' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
FROM p;
Why I get the error message in BQ? How to properly export from MySQL CSV files that have newlines (CR and LF mixed, as it was user input from Windows or Mac)

Couple of job IDs:

Job ID: aerobic-forge-504:bqjob_r75d28c332a179207_0000014710c6969d_1

Job ID: aerobic-forge-504:bqjob_r732cb544f96e3d8d_0000014710f8ffe1_1

Update

Apparently it's more to this. I used 5.5.34-MariaDB-wsrep-log INTO OUTFILE, and either is a bug or something wrong, but I get invalid CSV exports. I had to use other tool to export proper CSV. (tool: SQLYog)

it has problems with double quotes, for example Field 14 here has error:
3819^Ron ^Wolbert^6^0^1^6123103169^^^^^^^""Lil"" Ron's^0^1282689026^1

解决方案
The proper way to encode a double quote in CSV is to put another double quote in front of it.

So instead of:
"Moreno L Jackson \"THE MOTIVATOR!\"...
Have:
"Moreno L Jackson ""THE MOTIVATOR!""...


                        
这篇关于有效的CSV文件导入失败，数据在双引号（&quot;）和字段分隔符：字段开头的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！