如何在Google BigQuery加载.csv文件并去除首尾空格及设置多分隔符

一、去除字段首尾空格 #

BigQuery原生CSV加载没有直接的自动trim字段选项，推荐两种高效解决方式：

1. 加载后用SQL批量清洗 #

先按分号分隔正常加载CSV到临时表，再通过TRIM()函数创建清洗后的正式表：

CREATE OR REPLACE TABLE your_dataset.cleaned_transport AS
SELECT
  TRIM(jenis) AS jenis,
  TRIM(nama_transport) AS nama_transport,
  TRIM(kode_rute) AS kode_rute,
  -- 其他字段依次添加TRIM处理
FROM your_dataset.raw_transport;

2. 上传前预处理CSV（同时解决空格问题） #

用简单脚本批量去除所有字段的首尾空格，再上传到BigQuery：

import csv

# 读取原文件并清洗
with open('data_transportasi_kajarta.csv', 'r', encoding='utf-8') as in_file, \
     open('cleaned_data.csv', 'w', encoding='utf-8', newline='') as out_file:
    reader = csv.reader(in_file, delimiter=';')
    writer = csv.writer(out_file, delimiter=';')
    # 处理表头和每一行数据
    for row in reader:
        cleaned_row = [field.strip() for field in row]
        writer.writerow(cleaned_row)

二、配置多自定义分隔符（;、; 、 ;） #

BigQuery的CSV加载工具不支持直接配置多个字段分隔符，但可以通过以下两种方式实现等效效果：

1. 预处理CSV统一分隔符 #

用脚本将所有变体分隔符（; 、 ;）替换为标准分号;，同时trim字段（和上面的预处理脚本合并即可）：

import re
import csv

with open('data_transportasi_kajarta.csv', 'r', encoding='utf-8') as in_file, \
     open('standardized_data.csv', 'w', encoding='utf-8', newline='') as out_file:
    for line in in_file:
        # 用正则匹配所有带空格的分号，替换为标准分号
        standardized_line = re.sub(r'\s*;\s*', ';', line.strip())
        # 拆分字段并trim（可选，确保彻底去空格）
        fields = [field.strip() for field in standardized_line.split(';')]
        out_file.write(';'.join(fields) + '\n')

2. 加载为文本后用正则拆分 #

先将CSV以纯文本格式加载到BigQuery的单字段表（比如字段名为raw_line），再用正则表达式匹配所有变体分隔符并拆分字段：

CREATE OR REPLACE TABLE your_dataset.final_transport AS
SELECT
  -- 按索引提取各字段，同时trim
  TRIM(SPLIT(raw_line, r'\s*;\s*')[OFFSET(0)]) AS col1,
  TRIM(SPLIT(raw_line, r'\s*;\s*')[OFFSET(1)]) AS jenis,
  TRIM(SPLIT(raw_line, r'\s*;\s*')[OFFSET(2)]) AS nama_transport,
  -- 其他字段按对应索引提取
FROM your_dataset.raw_text_table
WHERE raw_line != '' -- 跳过空行

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