Hello! I'm writing a python script that will import all the excel files in a folder and write them into a SQL Server table. The script runs if I just point it to a single excel file, but i'm stumped at setting up an iteration loop that will read through all the excel files in a folder. I need to insert some type of loop inbetween the "if file_to_import.endswith('.XLS'):" & the "column_count=10" lines. Any ideas or suggestions would be very much appreciated!
# Import arcpy module
from xlrd import open_workbook ,cellname
import arcpy
import pyodbc as p
import os
# Database Connection Info
server = "Server"
database = "DB"
connStr = ('DRIVER={SQL Server Native Client 10.0};SERVER=' + server + ';DATABASE=' + database + ';' + 'Trusted_Connection=yes')
# Assign path to Excel files
folder_to_import = '\\\\Location\\DATA'
l_files_to_import = os.listdir(folder_to_import)
for file_to_import in l_files_to_import:
if file_to_import.endswith('.XLS'):
column_count=10
# Open entire workbook
book = open_workbook(file_to_import)
# Use first sheet
sheet = book.sheet_by_index(0)
# Open connection to SQL Server Table
conn = p.connect(connStr)
# Get cursor
cursor = conn.cursor()
# Assign the query string without values once, outside the loop
query = "INSERT INTO HED_EMPLOYEE_DATA (Company, Contact, Email, Name, Address, City, CentralCities, EnterpriseZones, NEZ, CDBG) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
# Iterate through each row
for row_index in range(1, sheet.nrows):
row_num = row_index
Company = sheet.cell(row_index,0).value
Contact = sheet.cell(row_index,1).value
Email = sheet.cell(row_index,2).value
Name = sheet.cell(row_index,3).value
Address = sheet.cell(row_index,4).value
City = sheet.cell(row_index,5).value
CentralCities = sheet.cell(row_index,6).value
EnterpriseZones = sheet.cell(row_index,7).value
NEZ = sheet.cell(row_index,8).value
CDBG = sheet.cell(row_index,9).value
values = (Company, Contact, Email, Name, Address, City, CentralCities, EnterpriseZones, NEZ, CDBG)
cursor.execute(query, values)
# Close cursor
cursor.close()
# Commit transaction
conn.commit()
# Close SQL server connection
conn.close()
