Azure Data Factory复制活动报错：CSV列数超出预期求助

错误信息 #

{
    "errorCode": "2200",
    "message": "ErrorCode=DelimitedTextMoreColumnsThanDefined,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error found when processing 'Csv/Tsv Format Text' source 'organizations.csv' with row number 6696: found more columns than expected column count 41.,Source=Microsoft.DataTransfer.Common,'",
    "failureType": "UserError",
    "target": "Copy data1",
    "details": []
}

复制活动配置 #

源配置截图 #

接收器配置截图 #

源数据预览截图 #

问题场景 #

执行Azure Data Factory中CSV到SQL表的复制活动时触发上述错误，报错指向第6696行数据，该行内容如下（带引号的字段内包含逗号）：

3b1a2e5f-d08b-166b-4b91-eb53009b2377    Compassites Software Solutions  organization    compassites-software    https://www.crunchbase.com/organization/compassites-software    318375  17/07/2008 10:46    05/12/2022 12:17        company compassitesinc.com  http://www.compassitesinc.com   IND     Karnataka   Bangalore   "Pradeep Court", #163/B, 6th Main 3rd Cross, JP Nagar 3rd phase 560078  operating   Custom software solution experts    Big Data,Cloud Computing,Information Technology,Mobile,Software Data and Analytics,Information Technology,Internet Services,Mobile,Software                 01/11/2005          51-100  info@compassitesinc.com 080-42032572    http://www.facebook.com/compassites http://www.linkedin.com/company/compassites-software-solutions  http://twitter.com/compassites  https://res.cloudinary.com/crunchbase-production/image/upload/v1397190270/c3e5acbde40f36eaf4f8c6f6eda3f803.png              company

原因与解决方案 #

• 核心原因：第6696行的地址字段用双引号包裹，但字段内包含逗号，而当前CSV源配置未正确设置引号解析规则，导致ADF将引号内的逗号识别为列分隔符，解析出的列数超过预期的41列。
• 解决步骤：
  1. 进入CSV源的格式配置页面，将引号字符设置为"（双引号），确保ADF能识别引号包裹的字段，忽略内部的逗号分隔符。
  2. 确认列分隔符设置与实际文件一致（从预览截图判断应为制表符\t，需核对配置）。
  3. 重新运行复制活动，或通过数据预览功能验证第6696行的地址字段已被正确识别为单个列。

内容的提问来源于stack exchange，提问作者Patterson