Python批量处理Excel数据后,导入SQL Server

1、前言

紧接昨天的文章Windows下载安装配置SQL Server、SSMS,使用Python连接读写数据,我们已经安装和配置好了sqlserver,也成功测试了如何利用Python连接、读写数据到数据库。

今天我们正式开始怼需求:有很多Excel,需要批量处理,然后存入不同的数据表中。

2、开始动手动脑

2.1 拆解+明确需求

1) excel数据有哪些需要修改?

  • 有一列数据DocketDate是excel短时间数值,需要转变成正常的年月日格式;
    eg. 44567 --> 2022/1/6

  • 部分数据需要按SOID进行去重复处理,根据DocketDate保留最近的数据;

  • 有一列数据需要进行日期格式转换。
    eg. 06/Jan/2022 12:27 --> 2022-1-6

主要涉及:日期格式处理、数据去重处理

2) 每一个Excel都对应一个不同数据表吗?表名和Excel附件名称是否一致?

  • 有些Excel对应的是同一个表,有些是单独的

  • 表名和Excel附件名称不一致,不过是有对应关系的
    eg. 附件test1 和 test2 对应表 testa,附件test3 对应 testb

主要涉及:数据合并处理

2.2 安装第三方包

pip3 install sqlalchemy pymssql pandas xlrd xlwt   
  • sqlalchemy:可以将关系数据库的表结构映射到对象上,然后通过处理对象来处理数据库内容;

  • pymssql:python连接sqlserver数据库的驱动程序,也可以直接使用其连接数据库后进行读写操作;

  • pandas:处理各种数据,内置很多数据处理方法,非常方便;

  • xlrd xlwt:读写excel文件,pandas读写excel会调用他们。

导入包:

import pandas as pd   from datetime import date, timedelta, datetime   import time   import os   from sqlalchemy import create_engine   import pymssql   

2.3 读取excel数据

读取数据比较简单,直接调用pandas的read_excel函数即可,如果文件有什么特殊格式,比如编码,也可以自定义设置。

# 读取excel数据   def get_excel_data(filepath):       data = pd.read_excel(filepath)              return data   

2.4 特殊数据数据处理

1)日期天数转短日期

这个有一定难度,excel里直接转很简单,直接选中需要转的数据,然后在开始-数据格式栏选择短日期即可。

当时第一眼不知道其中的转换规律,搜索了很久,也没发现有类似问题或说明,首先肯定不是时间戳,感觉总有点关系,最后发现是天数,计算出天数计算起始日期就可以解决其他数据转变问题啦。

首先我们要判断空值,然后设置日期天数计算起始时间,利用datetime模块的timedelta函数将时间天数转变成时间差,然后直接与起始日期进行运算即可得出其代表的日期。

# 日期天数转短日期   def days_to_date(days):       # 处理nan值       if pd.isna(days):           return        #  44567  2022/1/6       # 推算出 excel 天数转短日期 是从1899.12.30开始计算       start = date(1899,12,30)        # 将days转换成 timedelta 类型,可以直接与日期进行计算       delta = timedelta(days)       # 开始日期+时间差 得到对应短日期       offset = start + delta       return offset   

这里比较难想的就是天数计算起始日期,不过想明白后,其实也好算,从excel中我们可以直接将日期天数转成短日期,等式已经有了,只有一个未知数x,我们只需列一个一元一次方程即可解出未知数x。

from datetime import date, timedelta      date_days = 44567   # 将天数转成日期类型时间间隔   delta = timedelta(date_days)   # 结果日期   result = date(2022,1,6)   # 计算未知的起始日期   x = result - delta   print(x)      '''   输出:1899-12-30   '''   

2)将日期中的英文转成数字

最开始我想的是使用正则匹配,将年月日都在取出来,然后将英文月份转变成数字,后来发现日期里可以直接识别英文的月份。

代码如下,首先将字符串按格式转变成日期类型数据,原数据为06/Jan/2022 12:27(数字日/英文月/数字年 数字小时:数字分钟),按日期格式化符号解释表中对应关系替换即可。

# 官方日期格式转换成常见格式   def date_to_common(time):       # 处理nan值       if pd.isna(time):           return        # 06/Jan/2022 12:27  2022-1-6       # 测试 print(time,':', type(time))       # 将字符串转成日期       time_format = datetime.strptime(time,'%d/%b/%Y %H:%M')        # 转换成指定日期格式       common_date = datetime.strftime(time_format, '%Y-%m-%d')        return common_date   

日期格式化符号解释表
@CSDN-划船的使者

3)按订单编号SOID去重

这里去重复除了按指定列去重外,还需要按日期保留最新数据。

我的想法是,首先调用pandas的sort_values函数将所有数据根据日期列进行升序排序,然后,调用drop_duplicates函数指定按SOID列进行去重,并指定keep值为last,表示重复数据中保留最后一行数据。

代码如下:

# 去除重复值 SOID重复 按日期去除最早的数据   def delete_repeat(data):       # 先按日期列 Docket Rec.Date & Time 排序 默认降序  保证留下的日期是最近的       data.sort_values(by=['Docket Rec.Date & Time'], inplace=True)       # 按 SOID 删除重复行       data.drop_duplicates(subset=['SOID #'], keep='last', inplace=True)              return data   

2.5 其他需求

多个Excel数据对应一张数据库的表

可以写一个字典,来存储数据库表和对应Excel数据名称,然后一个个存储到对应的数据库表中即可(或者提前处理好数据后,再合并)。

  • 合并同类型Excel表
# 相同表合并数据 传入合并excel列表   def merge_excel(elist, files_path):       data_list = [get_excel_data(files_path+i) for i in elist]       data = pd.concat(data_list)       return data   

这里传入同一类型Excel文件名列表(elist)和数据存储文件夹绝对/相对路径(files_path)即可,通过文件绝对/相对路径+Excel文件名即可得到Excel数据表文件的绝对/相对路径,再调用get_excel_data函数即可读取出数据。

遍历读取Excel表数据利用了列表推导式,最后利用pandas的concat函数即可将对应数据进行合并。

  • 数据存储到sqlserver
# 初始化数据库连接引擎   # create_engine("数据库类型+数据库驱动://数据库用户名:数据库密码@IP地址:端口/数据库",其他参数)   engine = create_engine("mssql+pymssql://sa:123456@localhost/study?charset=GBK")      # 存储数据   def data_to_sql(data, table_naem, columns):       # 再对数据进行一点处理,选取指定列存入数据库       data1 = data[columns]              # 第一个参数:表名       # 第二个参数:数据库连接引擎       # 第三个参数:是否存储索引       # 第四个参数:如果表存在 就追加数据       t1 = time.time()  # 时间戳 单位秒       print('数据插入开始时间:{0}'.format(t1))       data1.to_sql(table_naem, engine, index=False, if_exists='append')       t2 = time.time()  # 时间戳 单位秒       print('数据插入结束时间:{0}'.format(t2))       print('成功插入数据%d条,'%len(data1), '耗费时间:%.5f秒。'%(t2-t1))   

sqlalchemy+pymssql连接sqlserver的时候注意坑:要指定数据库编码,slqserver创建的数据库默认是GBK编码,关于sqlserver安装使用可以查看文章Windows下载安装配置SQL Server、SSMS,使用Python连接读写数据

2.6 完整调用代码

'''   批量处理所有excel数据   '''   # 数据文件都存储在某个指定目录下,如:   files_path = './data/'   bf_path = './process/'      # 获取当前目录下所有文件名称   # files = os.listdir(files_path)   # files      # 表名:附件excel名   data_dict = {       'testa': ['test1.xls', 'test2.xls'],        'testb': ['test3.xls'],        'testc': ['test4.xls']   }      # 选取附件中的指定列,只存入指定列数据   columns_a = ['S/No', 'SOID #', 'Current MileStone', 'Store In Date Time']   columns_b = ['Received Part Serial No', 'Received Product Category', 'Received Part Desc']   columns_c = ['From Loc', 'Orig Dispoition Code']      columns = [columns_a, columns_b, columns_c]   flag = 0  # 列选择标记      # 遍历字典 合并相关excel 然后处理数据后,存入sql   for k,v in data_dict.items():       table_name = k       data = merge_excel(v, files_path)       # 1、处理数据       if 'SOID #' not in data.columns:           # 不包含要处理的列,则直接简单去重后、存入数据库           data.drop_duplicates(inplace=True)       else:           # 特别处理数据           data = process_data(data)       # 2、存储数据       # 保险起见 本地也存一份       data.to_excel(bf_path+table_name+'.xls')       # 存储到数据库       data_to_sql(data, table_name, columns[flag])       flag+=1   

本文点赞过20,最近开源本文所有相关数据和代码。

以上就是“Python批量处理Excel数据后,导入SQL Server”的全部内容,希望对你有所帮助。

关于Python技术储备

学好 Python 不论是就业还是做副业赚钱都不错,但要学会 Python 还是要有一个学习规划。最后大家分享一份全套的 Python 学习资料,给那些想学习 Python 的小伙伴们一点帮助!

一、Python所有方向的学习路线

Python所有方向的技术点做的整理,形成各个领域的知识点汇总,它的用处就在于,你可以按照上面的知识点去找对应的学习资源,保证自己学得较为全面。

在这里插入图片描述

二、Python必备开发工具

img

三、Python视频合集

观看零基础学习视频,看视频学习是最快捷也是最有效果的方式,跟着视频中老师的思路,从基础到深入,还是很容易入门的。

img

四、实战案例

光学理论是没用的,要学会跟着一起敲,要动手实操,才能将自己的所学运用到实际当中去,这时候可以搞点实战案例来学习。

img

五、Python练习题

检查学习结果。

img

六、面试资料

我们学习Python必然是为了找到高薪的工作,下面这些面试题是来自阿里、腾讯、字节等一线互联网大厂最新的面试资料,并且有阿里大佬给出了权威的解答,刷完这一套面试资料相信大家都能找到满意的工作。

img

最后祝大家天天进步!!

上面这份完整版的Python全套学习资料已经上传至CSDN官方,朋友如果需要可以直接微信扫描下方CSDN官方认证二维码免费领取【保证100%免费】。

  • 21
    点赞
  • 23
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
一个将excel文件导入SQLServer表中的程序 一 双击Input.exe运行程序,将弹出一个窗口,这时请你在"数据库名"后面的 输入栏中输入数据库名(如果是千方百剂就是输入帐套名).你如果没对数 据库的登录进行特殊修改的话,那"用户名和密码"就没必要修改了. 二 填好以上输入框后,真接单击"连接数据库",如果连接成功,将弹出"数据库 连接成功,你现在可以导入数据"的对话框,你按"OK"后将弹出新的一个数据 导入的窗口. 三 在这个窗口上单击"打开EXCEL文件"按钮,然后选择你要导入Excel文件, 按打开(这时如果你数据比较多的话你可能要多等一会儿时间),之后就弹 出一个让你选择Excel工作区的窗口,你可以在下拉框中选择你数据所在的 Excel工作区了.选完以后按确定,你可以看到你Excel里的数据已经在"Excel 数据信息"里面了.而且还可以看到多了一列"不导入"的选项了.你如果哪一行 的数据导入的话你可以打勾,这一行将不被导入. 四 做完以上三步后,请在"表名"后面的下拉框中选择你所要导入的表的名称. 选完后,你得到"数据转换信息如下"这一栏配置数据转换的对应关系. 五 双击Excel字段处从下拉框中选择excel的列,双击表字段处从下拉框中选 择SQL表的列,然后看这列是否是"关键字",是的话打勾,不是不打勾.选择 完第一行后,就按方向键的向下键,继续第二行的选择,直到配置完Excel列 和表字段的对应关系为止. 六 按"导入数据"按钮系统会自动将页面转到"转换信息"这一页面.你将可以看 到第几行导入成功,或第几行导入失改的信息.

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值