一、需求:
1、根据某种固定格式的excel转换拼接成sql建表语句;
2、遍历每一个sheet;
二、目标excel格式如下(无表头,每个表之间空一行):
中文表名1 | 表名1 | 中文字段名1 | 字段名1 | 字段类型 |
---|---|---|---|---|
表1 | table1 | 名字 | name | string |
性别 | sex | string | ||
年龄 | age | bigint | ||
表2 | table2 | 名字 | name | string |
性别 | sex | string | ||
年龄 | age | bigint | ||
… |
三、上代码:
import pandas as pd#excel处理库
import numpy as np#将datefream转为list
data_path=r'C:\Users\excel\项目执行.xlsx'#数据文件目录
sql_text=r'C:\Users\ddl\项目执行.txt'#结果文件
def read_sheet():
data_xls=pd.read_excel(data_path,sheet_name=None,header = None)#打开文件
for key in data_xls:#循环读取每个sheet
read_table(data_xls[key].replace(np.nan, '', regex=True))
def read_table(df):#将数据转为list
data_array = np.array(df)
data_list =data_array.tolist()
del_table(data_list)
def del_table(data_list):#处理列表
t=1
data_dic=[]
for i in data_list:
key=0
for k in i:#这个for是为了找到空行
if k=='':
key+=1
if key!=5:
if t==1:#每块第一行数据
data_dic=[i[0],i[1],[i[2]],[i[3]],[i[4]]]
else:#其他行
data_dic[2].append(i[2])
data_dic[3].append(i[3])
data_dic[4].append(i[4])
t=0
else:
write_sql(data_dic)#遇到空行处理数据
t=1
write_sql(data_dic)#最后无空行将每个sheet最后一块进入处理
def write_sql(data_dic):#将数据构造为sql并写入文件
sql="create table if not exists " +data_dic[1]+"(\n"
for i in range(len(data_dic[2])):#字段内容类似用for循环
if i == len(data_dic[2])-1:
sql+= data_dic[3][i]+' '+data_dic[4][i]+" comment '"+data_dic[2][i]+"'\n)"
else:
sql+= data_dic[3][i]+' '+data_dic[4][i]+" comment '"+data_dic[2][i]+"'\n,"
sql+="comment '{}'\npartitioned by(ds string)\nlifecycle 365;".format(data_dic[0])
with open(sql_text,'a') as f: #设置文件对象 a追加模式
f.write(sql+'\n\n')
read_sheet()
四、结果呈现:
create table if not exists table1 (
name string comment ‘名字’
,sex string comment ‘性别’
,age bigint comment ‘年龄’
) comment ‘表1’
partitioned by (ds string)
lifecycle 365;create table if not exists table2 (
name string comment ‘名字’
,sex string comment ‘性别’
,age bigint comment ‘年龄’
) comment ‘表2’
partitioned by (ds string)
lifecycle 365;