Problem Formulation and Solution Overview
ℹ️ Info: A batch or
.bat file is a file that contains commands to be executed in the order specified. This file type can organize and automate tasks that need to be run regularly without requiring user input. These files can be created using a text editor, such as Notepad.
To make it more interesting, we have the following running scenario:
sales.csv file to follow along with our article.
Install Batch Runner Extension
To run/execute a
.bat file, an extension will need to be installed in the IDE.
To install this extension, navigate to the IDE,
Extensions area. In the VSC IDE, this can be found on the far left panel bar shown below.
In the Search textbox, enter Batch Runner. While entering this text, the IDE automatically searches for extensions that match the criteria entered.
Once the desired extension is found, click the Install button to the left of the Batch Runner extension to start the installation process.
Once the installation has been completed, the Install button converts to a Settings icon. The extension is now ready to use!
💡 Note: Feel free to install the Batch Extension of your choosing.
Create Python Script
This section creates a Python file that reads in a CSV, sorts, filters and saves the output to an Excel file.
You can replace this with any Python file you want to run. For this example, we’ll need two libraries:
pandaslibrary will need to be installed for this example, as the code reads in and filters a CSV file.
library will need to be installed for this example, as the code exports the filtered DataFrame to an Excel file. To install this library, navigate to the IDE terminal command prompt. Enter and run the code snippet shown below.
To install those libraries, navigate to the IDE terminal command prompt. Enter and run the two commands to install both libraries:
pip install pandas pip install openpyxl
In the current working directory, create a Python file called
sales.py. Copy and paste the code snippet below into this file and save it.
import pandas as pd from datetime import datetime import openpyxl today = datetime.now() cols = ['OrderDate', 'Region', 'Item', 'Units'] df = pd.read_csv('sales.csv', usecols=cols) df["OrderDate"] = pd.to_datetime(df["OrderDate"]) df = df.sort_values(by=['OrderDate']) df_monthly = df[df['OrderDate'].dt.month == today.month] df_monthly.to_excel('monthly_rpt.xlsx', columns=cols, index=False, header=True)
The first three (3) lines in the above code snippet import references to the libraries necessary to run this code error-free.
The following line retrieves the current date using
datetime.now() from the
datetime library. The results save to the variable
today. If the contents were output to the terminal, the following displays:
The next line declares a List containing the DataFrame Columns to retrieve from the CSV file and export to the Excel file. The results save to
sales.csv file is opened, and columns specified in
cols are retrieved. The results save to the DataFrame
df was output to the terminal, the following snippet displays:
Top Five (5) Records of
The next line converts the
OrderDate into a proper Date format.
As you can see, the DataFrame,
df, is not in any kind of sort order. The next line takes care of this by sorting on the
OrderDate field in ascending order. The results save back to the DataFrame
This script’s final two (2) lines filter the DataFrame,
df, based on the current month. The results save to
df_monthly. These results are then exported to Excel and placed into the current working directory.
If you run this code, you will see that the Excel file saved the appropriate filtered results into the
monthly_rpt.xlsx file and placed this file into the current working directory.
Great! Now let’s create a
(Batch) file to run this script!
Create Batch File
In this section, a
.bat file is created to run the Python file
In the current working directory, create a
sales.bat. Copy and paste the code snippet below into this file and save it.
@echo off "C:\Python\python.exe" "C:\PYTHON_CODE\sales.py"
The first line of the code snippet turns off any output to the terminal.
The following line specifies the following:
- The location of the
python.exefile on your computer.
- The location of the python script to execute.
Let’s see if this works!
💡Note: It is best practice to ensure that the full paths to the python.exe and sales.py files are added.
This section executes the
.bat file, which calls and runs the code inside the
To run the
.bat file, navigate to the IDE, and click to select the
F5 key on the keyboard to execute.
If successful, the
monthly_rpt.xlsx file will appear in the current working directory.
This article has shown you how to create and run a
.bat file that executes a Python script. This file can execute a simple Python script as well as an intricate one.
Good Luck & Happy Coding!
Programming Humor – Python
At university, I found my love of writing and coding. Both of which I was able to use in my career.
During the past 15 years, I have held a number of positions such as:
In-house Corporate Technical Writer for various software programs such as Navision and Microsoft CRM
Corporate Trainer (staff of 30+)
Implementation Specialist for Navision and Microsoft CRM
Senior PHP Coder