Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save jakelosh/54a3e615e27a3ed8b250991e5e08f96f to your computer and use it in GitHub Desktop.
Save jakelosh/54a3e615e27a3ed8b250991e5e08f96f to your computer and use it in GitHub Desktop.
This is a simple script that copies a spreadsheet from a directory, makes a call to a database, loads the results into a data frame and then populates a spreadsheet with values. I used a very similar set of scripts to automate a series of monthly admin tasks.
# import relevant libraries
import pyodbc # needed for database call
import pandas # needed for data frame functionality
import shutil # needed for file copying
import datetime # needed to find dates for file paths
import xlwings # needed to populate the spreadsheet
# COPY FILE STEP
# determine destination directory to save a copy of the file
# file folders are nested according to month and year, so we do a bit of work
# to get the prior month-end date as this process is run after month-end
today = datetime.date.today()
# find the first of the current month
first = today.replace(day=1)
# find prior month-end date
prior_me_date = first - datetime.timedelta(days=1)
# set up origin and destination directories; here we assume dest_dir is a sub-directory of origin dir
origin_dir = r'C:/<YOUR DIR>'
dest_dir = origin_dir + r'%d/%02d.' % (prior_me_date.year, prior_me_date.month) + datetime.datetime.strftime(prior_me_date, '%B') + '/'
file_name1 = 'file1.xlsx'
file_name2 = 'file1_%d%02d.xlsx' % (prior_me_date.year, prior_me_date.month) # append date to end of file name
# copy file1.xlsx from dest_dir to origin_dir as file2.xlsx; we use copy2 to preserve metadata
shutil.copy2(origin_dir+file_name1, dest_dir+file_name2)
# DATABASE CALL STEP
# establish connection string; we found ours here: https://www.connectionstrings.com/
connection = pyodbc.connect('<YOUR STRING>')
# execute query and load results into data frame
query = "SELECT * FROM tables WHERE foo = 'bar'"
data = pandas.read_sql(query, connection)
# populate Sheet1 of workbook with data frame contents
app = xlwings.App(visible=False) # prevents Excel window from appearing
wb = xlwings.Book(fullname=dest_dir+file_name2)
ws = wb.sheets['Sheet1']
ws.range((1,1)).options(index=False, header=False).value = data
wb.save()
xlwings.app.quit()
# message the user of successful termination
print('Done!')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment