Python运用-通用格式CSV文件转换为XLSX

相信工作时间较长的“表哥们”一定都遇到过需要对存在固定格式的一些比较特殊的CSV文件进行统计分析的场景，比如从Oracle数据库里导出的用逗号或者空格分隔字段的CSV格式文本，此时如果CSV文件中再出现个像身份证号码这类超过15个字符的数据时，如采用Excel直接打开保存将会导致数据被转为科学计数法，这对数据的正确性就是巨大的灾难。 所以平时对这类格式都比较头疼，我这里想到一个办法，尝试用Python结合SQLite对CSV进行下处理：

脚本说明：

1、本脚本适合字段之间用空格分隔或者用逗号分隔的CSV文本

2、本脚本支持带有标题和不带标题的CSV文件，是否带有标题需要完成以下设置，当isheader设置为True时，自动跳过CSV文本首行。

# 首行是否标题true是false否(默认为否)，当为true时自动跳过首行
isheader = False

3、只需将CSV文件路径（含文件名和扩展名）设置到file_path_name变量即可

# 待转换的CSV文件【必填】
#file_path_name = f'{os.path.dirname(__file__)}/待转换的CSV文件.csv'

完整代码如下：

``

import sqlite3
import re
import time
import pandas as pd
from pathlib import Path
 
# 公共变量
# 待转换的CSV文件【必填】
file_path_name = f'{os.path.dirname(__file__)}/待转换的CSV文件.csv'
# 首行是否标题true是false否(默认为否)，当为true时自动跳过首行
isheader = False
# 设置字段之间的分割符号，默认空格
delimiter = " "
 
# 获取CSV文件所在目录
file_dirname = os.path.dirname(file_path_name)
# 获取CSV文件名(不带扩展名)
file_name = Path(file_path_name).stem
 
# 删除重建数据库
dbfile_path_name = f'{file_dirname}/{file_name}.db'
if os.path.exists(dbfile_path_name):
    os.remove(dbfile_path_name)
    # 连接到 SQLite 数据库（如果数据库文件不存在，会自动创建一个新的数据库文件）
    conn = sqlite3.connect(dbfile_path_name)
else:
    conn = sqlite3.connect(dbfile_path_name)
 
cursor = conn.cursor()
 
# 创建数据表
def CreateTB(header_name = "", cursor = conn.cursor()):
    sql = f"select name from sqlite_master where name='{file_name}';"
    cursor.execute(sql)
    # fetchone逐行获取查询结果，如果没有结果返回则判断表未创建
    if not bool(cursor.fetchone()):
        with open(file_path_name,'r',encoding='utf-8') as file:
            # 获取字段个数
            line = file.readline()
            if delimiter == " ":
                line = re.sub(r'\s+',',',line)
 
            if re.findall('^,',line):
                line = re.sub('^,','',line)
 
            if re.findall(',$',line):
                line = re.sub(',$','',line)
 
            # 将整理好的文本分割成字段，并获得字段数
            line_split = line.split(',')
            sql = ""
            if isheader:
                for i in range(len(line_split)):
                    sql += f",{line_split[i]} TEXT NOT NULL"
                    #index_name = line_split[index_num]
                    header_name += f"{line_split[i]},"
 
                    if i == len(line_split) - 1:
                        header_name = re.sub(',$','',header_name)
            else:
                for i in range(len(line_split)):
                    sql += f",字段名{i} TEXT NOT NULL"
                    #index_name = f"字段名{index_num}"
                    header_name += f"字段名{i},"
 
                    if i == len(line_split) - 1:
                        header_name = re.sub(',$','',header_name)
 
            sql = f"CREATE TABLE IF NOT EXISTS {file_name}(id INTEGER PRIMARY KEY{sql});"
 
            cursor.execute(sql)
            #sql = f"CREATE UNIQUE INDEX IF NOT EXISTS tableindex ON {file_name} ({index_name});"
            #cursor.execute(sql)
            # 提交更改
            conn.commit()
            return header_name
 
# 调用创建数据表函数
header = CreateTB()
 
with open(file_path_name,'r',encoding='utf-8') as file:
    lines = file.readlines()
    rownum = len(lines)
 
    i=0; x=0; y=0
    cursor.execute('BEGIN TRANSACTION')
    start_time = time.time()
    for line in lines:
        if i == 0:
            if isheader:
                i = i + 1
                continue
 
        # 这里用到正则表达式对数据中存在的多个空格以及数据行前后逗号进行处理
        if delimiter == " ":
            line = re.sub(r'\s+',',',line)
 
        if re.findall('^,',line):
            line = re.sub('^,','',line)
 
        if re.findall(',$',line):
            line = re.sub(',$','',line)
 
        # 对整理完的文本，用逗号进行分割
        line_split = line.split(',')
 
        # 组装insert插入记录
        fieldsValue = ""
        for j in range(len(line_split)):
            fieldsValue += f"'{line_split[j]}',"
            if j == len(line_split) - 1:
                fieldsValue = re.sub(',$','',fieldsValue)
 
        try:
            sql = f"INSERT OR ignore INTO {file_name}({header}) VALUES ({fieldsValue});"
            cursor.execute(sql)
            x = x + 1
        except Exception as e:
            y = y + 1
            #conn.rollback()
            print(e)
 
        # 对操作进行计数
        i=i+1
        if i == rownum:
            conn.commit()
 
            # 在这里将导入的待转换CSV文件数据经过SQLite数据库转化为Excel表导出
            df = pd.read_sql_query(f'select * from {file_name}',conn)
            df.to_excel(f'{file_dirname}/{file_name}.xlsx',index=False)
 
            # 输出执行结果
            end_time = time.time()
            print(f'共转换{x+y}行，其中成功{x}行，失败{y}行，操作完毕，执行时间：{end_time-start_time}秒')