如何处理文本转CSV时的断行问题以适配Athena读取

从第三方获取的文本文件会在第28列随机出现断行，转换为CSV后本地可正常读取，但导入Athena时无法识别。尝试用str.split修复失败，当前使用的Pandas代码如下：

import pandas as pd
add_columns = ["col1", "col2", "col3", ..., "col59"]

res = pd.read_csv("file_name.txt", names= add_columns, sep=',\s+', delimiter=',', encoding="utf-8", skipinitialspace=True)
df = pd.DataFrame(res)

df.to_csv('final_name.csv', index = None)

示例文件file_name.txt内容：

99,999,00499013,X701,,,5669,5669,1232,,1,1,,2,,,,0,0,0,,,,,,,,,,,,,,2400,1232,LXA,,
2,5669,,,,68,,,1,,,,,,,,,,,,71,
99,999,00499017,X701,,,5669,5669,1160,,1,1,,2,,,,0,0,0,,,,,,,,,,,,,,2400,1160,LXA,,1,5669,,,,,,,1,,,,,,,,,,,,71,
99,999,00499019,X701,,,5669,5669,1284,,1,1,,2,,,,0,0,0,,,,,,,,,,,,,,2400,1284,LXA,,2,5669,,,,66,,,1,,,,,,,,,,,,71,

步骤1：预处理文本合并断行 #

先读取原始文本，通过逗号数量判断是否为完整行（59列对应58个逗号），将被拆分的行合并为完整行后保存为临时文件，再用Pandas读取该文件。

代码示例：

import pandas as pd

# 定义总列数
total_columns = 59
input_path = "file_name.txt"
temp_path = "fixed_temp.txt"

# 预处理合并断行
with open(input_path, 'r', encoding='utf-8') as infile, open(temp_path, 'w', encoding='utf-8') as outfile:
    current_row = ""
    for line in infile:
        cleaned_line = line.strip()
        if not cleaned_line:
            continue
        current_row += cleaned_line
        # 计算当前行的列数：逗号数+1
        col_count = current_row.count(',') + 1
        if col_count >= total_columns:
            outfile.write(current_row + '\n')
            current_row = ""
    # 处理末尾剩余的不完整行
    if current_row:
        outfile.write(current_row + '\n')

# 读取修复后的文件
add_columns = ["col1", "col2", "col3", ..., "col59"]
df = pd.read_csv(temp_path, names=add_columns, delimiter=',', encoding="utf-8", skipinitialspace=True)

# 导出最终CSV
df.to_csv('final_name.csv', index=None)

步骤2：优化Pandas读取参数 #

原始代码中同时指定sep和delimiter会造成解析冲突，Pandas优先使用delimiter，因此移除sep=',\s+'参数，避免列解析混乱。

验证与导入 #

修复后打开final_name.csv确认每一行列数均为59，导入Athena时指定逗号为列分隔符，即可正常识别。

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