如何用Python 3将Unix(LF格式)CSV转为Windows(CR-LF格式)并调整内容

需求说明 #

需要将Unix换行（LF）格式的Unix.csv转换为Windows换行（CR-LF）格式的Windows.csv，同时完成以下内容调整：

• 去除所有字段的引号
• 将日期格式从YYYY-MM-DD或YYYY-MM-DD HH:MM AM/PM转换为MM/DD/YYYY或MM/DD/YYYY HH:MM（去掉AM/PM）
• 补全行尾逗号，确保每行统一为6列（即行尾对应字段用空值填充）

原文件内容（Unix.csv） #

Admissions
"Not Started: 1","Sent: 0","Completed: 0"
,,,,
Division,Community,"Resident Name",Date,"Document Status","Last Update"
,"Test","Name",2023-11-22,"Not Started",
,,,,
,,,,
Readmissions
"Not Started: 1","Sent: 0","Completed: 0"
,,,,
Division,Community,"Resident Name",Date,"Document Status","Last Update"
,"Test","Name","2023-11-22 7:29 AM","Not Started",
,,,,
,,,,
Discharges
,,,,
Division,Community,"Resident Name",Date
,"Test","Name","2023-11-22 1:23 AM"
,"Test","Name","2023-11-22 7:49 AM"
,"Test","Name","2023-11-22 1:12 AM"

目标文件内容（Windows.csv） #

Admissions,,,,,
Not Started: 1,Sent: 0,Completed: 0,,,
,,,,,
Division,Resident Name,Date,Document Status,Last Update
,Test,Name,11/22/2023,Not Started,
,,,,,
,,,,,
Readmissions,,,,,
Not Started: 1,Sent: 0,Completed: 0,,,
,,,,,
Division,Community,Resident Name,Date,Document Status,Last Update
,Test,Name,11/22/2023 7:29,Not Started,
,,,,,
,,,,,
Discharges,,,,,
,,,,,
Division,Community,Resident Name,Date,,
,Test,Name,11/22/2023 1:23,,
,Test,Name,11/22/2023 7:49,,
,Test,Name,11/22/2023 1:12,,

尝试过的无效代码 #

代码1（仅转换换行） #

#!/bin/python3
with open('Unix.csv', 'r') as file:
    toWrite = file.read().replace('\n', '\r\n')
with open('Convert.csv', 'w') as file:
    file.write(toWrite)

代码2（未实现内容调整） #

#!/bin/python3
with open('Test.csv', newline="\r\n") as file: 
    toWrite = file.read()
with open('convert.csv', 'w') as file:
    file.write(toWrite)

正确解决方案 #

使用Python的csv模块解析CSV内容，同时处理日期转换、字段补全和换行格式，代码如下：

#!/bin/python3
import csv
from datetime import datetime

def convert_date(field):
    """转换日期格式，处理两种输入格式"""
    # 定义可能的日期格式
    date_formats = [
        '%Y-%m-%d',
        '%Y-%m-%d %I:%M %p'
    ]
    for fmt in date_formats:
        try:
            dt = datetime.strptime(field.strip(), fmt)
            if fmt == '%Y-%m-%d':
                return dt.strftime('%m/%d/%Y')
            else:
                # 转换为MM/DD/YYYY HH:MM格式，去掉前导零
                return dt.strftime('%m/%d/%Y %I:%M').replace(' 0', ' ')
        except ValueError:
            # 不是日期格式，返回原字段
            return field

# 处理CSV文件
with open('Unix.csv', 'r', newline='') as infile, \
     open('Windows.csv', 'w', newline='\r\n') as outfile:
    
    reader = csv.reader(infile, quotechar='"', delimiter=',')
    writer = csv.writer(outfile, delimiter=',', quoting=csv.QUOTE_NONE, escapechar=None)
    
    for row in reader:
        # 处理每个字段：转换日期，csv.reader已自动去除字段引号
        processed_row = [convert_date(field) for field in row]
        # 补全到6列，不足的用空字符串填充
        while len(processed_row) < 6:
            processed_row.append('')
        # 截断到6列（防止原行超过6列）
        processed_row = processed_row[:6]
        # 写入处理后的行
        writer.writerow(processed_row)

代码说明 #

1. 日期转换：通过convert_date函数识别两种日期格式，转换为目标格式并去掉AM/PM标识。
2. 字段处理：利用csv.reader自动解析带引号的字段，无需手动去除引号。
3. 列数补全：将每行补全为6列，确保行尾逗号数量符合要求。
4. 换行格式：写入文件时指定newline='\r\n'，直接生成Windows格式的换行符。

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