Automate Daily Query To Export Excel

Question

I am using a Windows OS 64 BIT.

I have a query that I would like to run daily.

The output of this query would be one Excel document. Within the Excel document, there would be two tabs 1) the previous day's data and 2) an accumulation of all previous days' data. So for example, say I started to query my data 6/1; the output when running the query today(6/16) would be data from only 6/15 on one tab and data from 6/1-6/15 on the second tab.

The query itself is pretty simple-see below:

sql= """
select PATIENT_FIN, CHECKIN_DATE_TIME, FIRST_MD_SEEN, LAST_ASSIGNED_MD, PT_ACUITY, PT_DISCH_DISPO, REASON_FOR_VISIT, PT_DX1, PT_DX2, PT_DX3
FROM COVID_TAT
Where (pt_acuity like '%1%') or (pt_acuity like '%2%' and (PT_DISCH_DISPO like '%admit%' or PT_DISCH_DISPO like '%IP%'))
order by PATIENT_FIN
"""
TAT = pd.read_sql(sql,conn)

I found this link below but I'm unsure how to adapt this to make my query produce the automated desired output.

Python script to do something at the same time every day

What operating system are you using? There are ways to achieve this within the OS that runs the script at the same time each day — Parakiwi, Jun 16 '20 at 20:18
Okay. I will describe how i do this daily and hopefully it helps — Parakiwi, Jun 16 '20 at 20:27

Parakiwi · Answer 1 · 2020-06-17T20:17:22.037

Here is how i accomplish a similar task:

First off, the data you want to collect can be obtained every day at the same time using the Task Scheduler. This allows you to set up scripts to be executed every X amount of time (i.e. every day in your case). I use these to execute python queries similar to the one you have described above to pull the data and dump it into a .csv file in a given location. In this case you might want to have a todays_data.csv and a previous_data.csv which you can update every day with the data you pull. Pseudocode for this is something like:

sql= """
select PATIENT_FIN, CHECKIN_DATE_TIME, FIRST_MD_SEEN, LAST_ASSIGNED_MD, PT_ACUITY, PT_DISCH_DISPO, REASON_FOR_VISIT, PT_DX1, PT_DX2, PT_DX3
FROM COVID_TAT
Where (pt_acuity like '%1%') or (pt_acuity like '%2%' and (PT_DISCH_DISPO like '%admit%' or PT_DISCH_DISPO like '%IP%'))
order by PATIENT_FIN
"""
TAT = pd.read_sql(sql,conn)

# YOU MAY NEED TO DO SOME FORMATTING HERE

# add current todays_data.csv to previous_data.csv
today_file = open("todays_data.csv", "r")
today_file_data = today_file.readlines()
today_file.close()
previous_file = open("previous_data.csv", "a")
previous_file.writelines(today_file_data)
previous_file.write("\n")
previous_file.close()
# delete todays_data.csv
today_file = open("todays_data.csv", "w+")
# create todays_data.csv fresh with only the data you pulled today
today_file.writelines(TAT)
today_file.close()

Once you've got this working, you can create an excel spreadsheet and then point it at these .csv files as a data source. This is from Data > Get Data > From File > From Text/CSV.

This treats the .csv files as a database of sorts and allows the sheet to be updated every day with the information pulled from the scheduled script. This allows you to also create graphs or statistics that will be auto updated every day within the excel document.

Hope this helps. Happy to answer any more questions you have

I'm sure this makes sense to most but I'm totally confused - new to Python. How do I tell my OS (task scheduler) to run the python code everyday? How do I do the things you commented on in my code? — Raven, Jun 17 '20 at 15:03
@Raven I will edit the answer with some more detail for ya. Open your task scheduler (you can find it through the start menu easy enough) — Parakiwi, Jun 17 '20 at 20:03

Automate Daily Query To Export Excel

1 Answers1