08
2019
07

excel转sql工具

#!/usr/bin/python  
# coding=gbk  

import os
import time
import datetime
import xlrd
import sys

s_version = "1.0.8"
readme = '''
.xls file convert .sql file
    water
    2019-07-08
    version: %s
说明:
    xls2sql [-r rows] [-v/--version] [-h/--help] file.xls
        -r rows         指定记录的开始位置
        -v/--version    版本
        -h/--help       帮助
    1. 工作表名称为表名;
    2. 第1行为字段名;
    3. 第2行为字段类型;
    4. 第2~(rows-1)行为附加数据,需要使用参数-r rows(rows不能小于3);
    5. 第rows行开始为记录;
''' % s_version



def getdir(file):
    now = datetime.datetime.now()
    path = 'output/[' + file.lower().replace('.xls', '') + '].[' + now.strftime('%Y-%m-%d') + ']'
    if os.path.exists(path):
        for i in range(98, 122):
            new_path = path + "_" + chr(i)

            if not os.path.exists(new_path):
                path = new_path
                break
            else:
                print(new_path + '已存在')

    os.makedirs(path)  # 创建新文件夹
    return path


def getCellStr(cell):
    if cell.ctype == xlrd.book.XL_CELL_NUMBER:
        # return str('%d' % cell.value)
        v = str(cell.value)
        if v.endswith('.0'):
            v = v[:-2]
        return v
    else:
        return str(cell.value)


# drop table script
def getdrop(tabname):
    return "DROP TABLE [" + tabname + "];"


# create table script
def getcreate(tab1, tabname):
    ncols = tab1.ncols

    sql = "CREATE TABLE [" + tabname + "] ( \n"
    col = ""
    for i in range(ncols):
        if i != 0: col += ",\n"
        col += "[" + getCellStr(tab1.cell(0, i)) + "] " + getCellStr(tab1.cell(1, i)) + " "
        if i == 0:
            col += "NOT NULL"
        else:
            col += "NULL"
    col += ");"
    sql += col
    return sql


# select script
def getselect(tabname):
    return "SELECT TOP 1000 * FROM [" + tabname + "];"


# delete script
def getdelete(tabname):
    return "DELETE FROM [" + tabname + "];"


# insert script
def getinsert(tab1, tablename, d_index=3):
    nrows = tab1.nrows
    ncols = tab1.ncols
    sql = ""
    col = ""
    for i in range(ncols):
        if i != 0: col += ","
        col += "[" + getCellStr(tab1.cell(0, i)) + "]"

    for i in range(d_index - 1, nrows):
        sql += "INSERT INTO [" + tablename + "] (" + col + ") VALUES ("
        field = ""
        for j in range(ncols):
            if j != 0: field += ","
            t = getCellStr(tab1.cell(1, j))
            if t == "INT" or t == "DATETIME":
                field += getCellStr(tab1.cell(i, j))
            else:
                f_temp = getCellStr(tab1.cell(i, j))
                f_temp = f_temp.replace("'", "''")
                field += "'" + f_temp + "'"
        sql += field + "); \n"
    return sql


def xls2sql(xlsfile, path="sql/", d_index=3):
    # path = getdir(xlsfile)
    if d_index < 3: d_index = 3
    xls1 = xlrd.open_workbook(xlsfile, formatting_info=True)
    tab_count = len(xls1.sheets())
    for k in range(tab_count):
        tab1 = xls1.sheets()[k]  # sheet
        tabname = tab1.name
        print("table name: [" + tabname + "]")
        nrows = tab1.nrows
        ncols = tab1.ncols

        sql = "-- " + getdrop(tabname) + "\n\n"
        sql += "/*\n" + getcreate(tab1, tabname) + "\n*/\n\n"
        sql += "-- " + getselect(tabname) + "\n\n"
        sql += "-- " + getdelete(tabname) + "\n\n"
        sql += getinsert(tab1, tabname, d_index)

        if not os.path.exists(path):
            os.makedirs(path)
        fn_sql = path + "/[" + tabname + "].[CRUD].sql"
        file1 = open(fn_sql, "w")
        file1.write(sql + "\n")
        file1.close
        print("output: %s" % fn_sql)
    return path


if __name__ == '__main__':

    i_len = len(sys.argv)
    if i_len == 2:
        cmd = sys.argv[1]
        if cmd == '-h' or cmd == '--help':
            print(readme)
        elif cmd == '-v' or cmd == '--version':
            print(s_version)
        else:
            xls2sql(cmd)
    elif i_len == 4:
        if sys.argv[1] == "-r":
            i_r = int(sys.argv[2])
            fn = sys.argv[3]
            xls2sql(fn, d_index=i_r)
    else:
        print(readme)

示例(demo.xls):

转换命令:python xls2sql.py demo.xls

生成结果:

« 上一篇 下一篇 »

评论列表:

1.动画制作  2019-09-11 14:11:03 回复该评论
谢谢分享

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。