如何在Ruby on Rails中用正则表达式清理格式混乱的CSV文件？

问题背景 #

每天需处理数千个包含数千行、数百列的CSV文件，这些文件大多遵循规范格式——所有字段用双引号包裹、以逗号分隔，但字段内部的内容常出现格式问题，导致无法正确导入到系统中。

示例数据 #

"option1","option2","product","description","reference","price","currency","foo","bar","address1","address2","city","state","postal","country","option3","option4","option5"  
"","","apples","multi-color ""macintosh"" apples","PO#1-3503", 24.00"" x 12.00"" x 12.00"","12.50","USD","","","1234 Main Street """","","Genericsberg","VA","10324","USA","","",""  

具体问题 #

• 字段首尾出现三个双引号（如示例中的address1字段）
• 字段内部出现双引号对（如description、reference字段）
• 字段内部出现单独双引号后接逗号（如reference字段，采购订单编号与产品尺寸属于同一字段，不能被逗号拆分）

明确需求 #

1. 非空字段内部的双引号对（""）直接移除
2. 字段内部出现的"，（双引号加逗号），移除双引号，保留逗号
3. 字段首尾的三个双引号（"""）替换为单个双引号（"）
4. 非空字段首尾的双引号对（""）替换为单个双引号（"）

技术栈与已尝试方案 #

• 技术栈：Ruby on Rails，使用SmarterCSV从S3存储桶导入CSV文件
• 已尝试方案：
  • text.gsub(/(?<!^|,)"(?!,|$)/, '')：曾解决部分格式问题，但对当前案例效果不佳
  • text.gsub('"""', '"')：能较好满足需求3，但无法同时处理其他问题

解决方案 #

通过分步正则替换，可同时满足所有需求，Ruby代码如下：

# 处理需求2：移除字段内部双引号，保留逗号
text = text.gsub('",', ',')

# 处理需求1：移除字段内部的双引号对
text = text.gsub(/(?<!^|,)""(?!,|$)/, '')

# 处理需求3、4：将字段首尾的多组双引号替换为单个引号包裹内容
text = text.gsub(/(?<=^|,)"{2,}(.*?)"{2,}(?=,|$)/, '"\\1"')

# 确保空字段保持原格式（""）
text = text.gsub(/(?<=^|,)""(?=,|$)/, '""')

代码说明 #

1. 第一步：直接替换所有",为,, 移除字段内部导致拆分的双引号，保留逗号，确保同一字段不被错误拆分。
2. 第二步：使用负向断言正则，匹配不在字段开头（^或,之后）、也不在字段结尾（,或$之前）的双引号对，替换为空，清除字段内部的冗余双引号。
3. 第三步：匹配字段首尾的2个及以上双引号，将其替换为单个引号包裹字段内容，同时解决首尾多引号的问题。
4. 第四步：确保空字段仍保留""格式，避免被误修改。

处理后示例数据 #

"option1","option2","product","description","reference","price","currency","foo","bar","address1","address2","city","state","postal","country","option3","option4","option5"  
"","","apples","multi-color macintosh apples","PO#1-3503, 24.00 x 12.00 x 12.00","12.50","USD","","","1234 Main Street ","","Genericsberg","VA","10324","USA","","",""  

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