当前位置：首页 > article >正文

Python在实际工作中的运用-CSV转XLSX的几个方法

article 2025/2/25 11:22:01

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

所以平时对这类格式一般我们要么会用Access桌面数据库导入为数据表然后导出为Excel表或者就在Access里进行处理，或者通过其他数据库管理工具导入Mysql/Mssql来进一步处理，但是不论是Access还是Mysql、MSSQL，Excel和Sqlite还是更好用也更方便些，既然如此我们就尝试下用Python结合SQLite对CSV进行下处理，同时也提供几个CSV的数据处理方案给大家自选：

1、将CSV插入SQLite数据库，然后导出为XLSX，在该案例中，我们用到了os、re、pandas和sqlite3库，os库的用法我们在《Python在实际工作中的运用-基础操作》中已经为大家做过详细介绍，pandas库的用法在《Python在实际工作中的运用-Excel数据统计和数据分析》中进行过详细介绍、下面我们将逐一对re和sqlite库进行介绍

re库是Python的内置库，无需安装，用来实现“正则表达式”，re库提供了：“match”、“search”、“findall”三种基本搜索模式，以及“sub”替换模式、“split”分割模式。本段内容摘自《 Python---【re库的使用】》

本代码使用到的是re库的sub替换模式，第一句将数据行里连续的多个空格替换为一个半角逗号，第二句将数据行开头的一个逗号清除，第三句将数据行末尾的一个逗号清除。

        # 这里用到正则表达式对数据中存在的多个空格以及数据行前后逗号进行处理
        line = re.sub(r'\s+',',',line)
        line = re.sub('^,','',line)
        line = re.sub(',$','',line)

首先介绍下SQLite 是一个 C 语言库，它实现了一个小型、快速、全功能、自包含的 SQL 数据库引擎。与其他数据库系统不同，SQLite 是无服务器的，这意味着它不需要一个单独的服务器进程来运行，它直接读写常规的磁盘文件。SQLite 数据库完全存储在一个单独的文件中，具备跨平台兼容性，且体积小，使用简单。

从Python 2.5版本开始，SQLite就已经被集成到了标准库sqlite3模块中，所以大多数情况下不需要额外安装任何东西。不过，如果不确定自己的Python版本或者想要确认是否已经包含该模块，可以通过以下代码来检查：

import sqlite3
print(sqlite3.version)  # 打印SQLite库的版本
print(sqlite3.sqlite_version)  # 打印SQLite的版本

运行这段代码后，如果没有任何错误提示，并且打印出了版本信息，那么恭喜你的Python环境已经为SQLite做好了准备！本段内容摘自《使用Python操作SQLite数据库：创建表及增删改查》

在本代码中，分别用到以下属性和方法：

使用sqlite3.connect()函数连接到一个名为员工信息.db的数据库文件。如果该文件不存在，则会在当前目录下创建一个新的数据库。
创建了一个游标（Cursor）对象，用于执行SQL命令。
执行SQL语句来创建员工信息表，其中包含id（主键）、身份证号码、姓名、所属部门和入职时间等字段，并向表中插入员工信息。
最后提交所有更改。

下面是完整代码，并对操作步骤进行了详细解读：

import os
import sqlite3
import re
import pandas as pd

# 连接到 SQLite 数据库（如果数据库文件不存在，会自动创建一个新的数据库文件）
conn = sqlite3.connect('员工信息.db')

# 创建一个游标对象
cursor = conn.cursor()

# 创建表和索引
# 查询表是否存在
sql = f"select name from sqlite_master where name='员工信息表';"
cursor.execute(sql)

# fetchone逐行获取查询结果，如果没有结果返回则判断表未创建
if not bool(cursor.fetchone()):
    cursor.execute(f"CREATE TABLE IF NOT EXISTS 员工信息表 "
                   "(id INTEGER PRIMARY KEY,身份证号码 TEXT NOT NULL,姓名 TEXT NOT NULL,所属部门 TEXT NOT NULL,入职时间 TEXT NOT NULL);")
    cursor.execute(f"CREATE UNIQUE INDEX 员工信息表_身份证号码 ON 员工信息表 (身份证号码);")
    # 提交更改
    conn.commit()

# 对包含汉字的CSV或者txt文件在读取时建议加上encoding，否则在进行后续处理是可能会出现，这类问题TypeError: a bytes-like object is required, not ‘str‘
filename = f'{os.path.dirname(__file__)}/员工信息数据.csv'
with open(filename,'r',encoding='utf-8') as file:
    lines = file.readlines()
    rownum = len(lines)
    i=0
    for line in lines:
        # 这里用到正则表达式对数据中存在的多个空格以及数据行前后逗号进行处理
        line = re.sub(r'\s+',',',line)
        line = re.sub('^,','',line)
        line = re.sub(',$','',line)

        # 对整理完的文本，用逗号进行分割，从而拿到员工信息字段
        datasplit = line.split(',')
        身份证号码 = datasplit[0]
        姓名 = datasplit[1]
        所属部门 = datasplit[2]
        入职时间 = datasplit[3]

        # 配合员工信息表身份证号码唯一索引,忽略身份证号码重复项,提高操作效率
        sql=f"INSERT OR ignore INTO 员工信息表(身份证号码,姓名,所属部门,入职时间) VALUES ('{身份证号码}','{姓名}','{所属部门}','{入职时间}');"
        cursor.execute(sql)

        # 对操作进行计数
        i=i+1

        if i == rownum:
            # 最后通过提交事务的方法进一步提高操作速度,大幅缩短操作时间
            conn.commit()

            # 在这里将导入的员工信息数据.CSV数据经过SQLite数据库转化为Excel表导出
            df = pd.read_sql_query('select * from 员工信息表',conn)
            df.to_excel(f'{os.path.dirname(filename)}/员工信息表.xlsx',index=False)
            print(f'共转换{i}行，操作完毕')

2、也可以将CSV直接转换为Excel，然后再进行进一步处理，在该案例中，我们用到了pandas库

import os.path
import pandas as pd

filepath = os.path.dirname(__file__)
data = pd.read_csv(f'{filepath}\\员工信息.CSV')
data.to_excel(f'{filepath}\\员工信息表.xlsx',index=False)

但是对于从数据库里导出的CSV还是推荐用第一种方法，插入SQlite数据库后，用SQlite数据库管理工具（比如：SharpPlus SQLite Developer）来进行处理，或者导出为Excel进行进一步处理。

查看全文

http://www.kler.cn/a/560256.html