Created
May 5, 2018 06:03
-
-
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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