Python-сообщество

 #работа с файлом excel
import pandas as pd
from pandas import DataFrame
read_table = pd.read_excel(r'D:\ОБУЧЕНИЕ\py\gtt.xlsx')
df = DataFrame(read_table)
 
#работа с БД access
import pyodbc
connStr = pyodbc.connect(
    r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:\ОБУЧЕНИЕ\py\svod.mdb;')
 
#вставляем данные
cursor = connStr.cursor()
for index,\
    row in df.iterrows():
    cursor.execute("INSERT INTO [D:\ОБУЧЕНИЕ\py\svod.mdb].set ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11]) values (?,?,?,?,?,?,?,?,?,?,?)"
                   , row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11])
    connStr.commit()
cursor.close()
connStr.close()

 cursor.execute("INSERT INTO [D:\ОБУЧЕНИЕ\py\svod.mdb].set ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11]) values (?,?,?,?,?,?,?,?,?,?,?)"
                   , row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11])

[code python]cursor.execute("INSERT INTO [D:\ОБУЧЕНИЕ\py\svod.mdb].set ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11]) values (?,?,?,?,?,?,?,?,?,?,?)"
                   , *row)[/code]

 stolbci = ''
for ii in range(1,len(row) + 1):
   stolbci += '[{}],'.format(ii)
stolbci = stolbci[:-1]
# print(stolbci)
voprosi = ('?,'*len(row))[:-1]
# print(voprosi)
db = 'D:\ОБУЧЕНИЕ\py\svod.mdb'
zapros = "INSERT INTO [{}].set ({}) values ({})".format(db, stolbci, voprosi)
cursor.execute(zapros, *row)

 row = row[1:]

 cursor = connStr.cursor()
for index,\
    row in df.iterrows():
    stolbci = ''
for ii in range(1, len(row) + 1):
    stolbci += '[{}],'.format(ii)
stolbci = stolbci[:-1]
print(stolbci)
voprosi = ('?,' * len(row))[:-1]
print(voprosi)
db = 'D:\ОБУЧЕНИЕ\py\svod.mdb'
zapros = "INSERT INTO [{}].set ({}) values ({})".format(db, stolbci, voprosi)
cursor.execute(zapros, *row)

 cursor.execute(zapros, row)

 pyodbc.ProgrammingError: ('The SQL contains 41 parameter markers, but 1 parameters were supplied', 'HY000')

 row = (1,2,4,'a')
func(*row)
# эквивалентна 
func(1,2,4,'a')
# То же самое, что и
func(row[0], row[1], row[2], row[3])

     cursor.execute(zapros, *row)
pyodbc.DataError: ('22008', '[22008] [Microsoft][Драйвер ODBC Microsoft Access]Переполнение поля даты и времени  (36) (SQLParamData)')

 cell_value = 'от 12.01.2017'
print(cell_value.split(' ')[-1].replace('.', '-'))

 import xlrd
import csv
import os
import pyodbc
with xlrd.open_workbook('D:\ОБУЧЕНИЕ\py\gtt.xls') as wb:
    sh = wb.sheet_by_index(0)
    with open('gtt.csv', 'w', newline="") as f:
        c = csv.writer(f)
        for r in range(sh.nrows):
            c.writerow(sh.row_values(r))
with open('D:\ОБУЧЕНИЕ\py\gtt.csv', 'r') as reader, open('D:\ОБУЧЕНИЕ\py\gtt_clean.csv', 'w') as writer:
    read_csv = csv.reader(reader); write_csv = csv.writer(writer, lineterminator='\n')
    for line in read_csv:
        if len(line[1]) > 0:
            write_csv.writerow(line)
access_path = "D:\ОБУЧЕНИЕ\py\\svod.mdb"
con = pyodbc.connect("DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};DBQ={};" \
                     .format(access_path))
strSQL = "SELECT * INTO [set_s] FROM [text;HDR=Yes;FMT=Delimited(,);" + \
         "Database=D:\ОБУЧЕНИЕ\py].gtt_clean.csv;"
cur = con.cursor()
cur.execute(strSQL)
con.commit()

 from glob import glob
g = glob('*.xls')
for fname in glob