Excel is a powerful and widely used tool for data analysis. In this video, we will look at how to automate excel reports with Python. How to query a database and import data into a dataframe. Slice and dice this data in a dataframe. How to use OS module to create a directory structure and finally save the files in the directory.
SQL Server ODBC driver 11 for SQL Server for Windows
https://www.microsoft.com/en-us/download/details.aspx?id=36434
Command to install pyodbc package: pip install pyodbc
https://pypi.org/project/pyodbc/
GitHub repo link for excel file, Notebook and SQL code
https://github.com/hnawaz007/pythondataanalysis/tree/main/AutomateExcelReports
GitHub link for AdventureWorks2012 database file
https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2012.bak
How to install Pycharm IDE on windows:
https://www.youtube.com/watch?v=SZUNUB6nz3g
Getting started with Jupyter Notebook:
https://www.youtube.com/watch?v=B0G-44dqHRM&t
Video Outline!
0:00 - Intro: business scenario
0:48 - Data Overview
1:50 - Import required libraries
2:40 - Create Database connection
3:40 - Importing the data used in this video
5:07 - Split dataframe per requirement
5:37 - Create Folder structure
7:29 - Execute code
YouTube: https://www.youtube.com/c/HaqNawaz
Github: https://github.com/hnawaz007
Instagram: https://www.instagram.com/bi_insights_inc
LinkedIn: www.linkedin.com/in/haq-nawaz
