How to Automate Excel Reports Using SQL [CSV to SQL to Excel Automation]

Learn how to fully automate your Reports in Excel using SQL in order to minimize any manual work. First, we go through how to automate your data import from CSV to SQL. Then, we explain how to create an SQL view selecting the data you need. Afterwards, we explain how to create an SQL store procedure with all the steps needed. Then we go through how to schedule SQL jobs in order to run themselves. Finally, we explain how to connect SQL with Excel and build an automated Excel Dashboard. Hope you enjoy this video!

Support the channel on Patreon:

Data Analytics Course Link:

GitHub Files:

Overview:
• How to fully automate Excel reports using SQL
• How to automate SQL imports
• How to create a view in SQL
• How to create a store procedure in SQL
• How to schedule jobs in SQL
• How to connect Excel with SQL – via a view
• How to create an automated Excel Dashboard

Excel Tutorial Links:

Excel Dashboard creation:
•

SQL Tutorial Links:

SQL most common queries:

How to Automate Excel Reports Using SQL [CSV to SQL to Excel Automation]

Yiannis Pitsillides on Social Media:

Tags:
how to link sql to excel

[YiannisPi]

Hi everyone! What do you think about this process of automation? You think it can save you some time?

[arjunwarrierkrishnan]

Great going! I love the pace at which you take up the steps. No nonsense straight talk. Keep them coming!!

[SyedVibe]

You have done a very good job and mentioned every granular details which really helps.. keep it up bro....

[mariesandrine]

Thank you very much..Your videos are awesome and simple to understand..Great Job!

[mustafakurt]

Bro....You are a genius...thank you so much.

[bouseuxlatache]

this is really a precious video. thank you.

[SophieGu-qhme]

Thanks for sharing! Great instructions.

[findthetruth]

I really love your videos bro. Keep it up :)

[yoshihirokawabataify]

Thanks movie
Please check feature for import data from CSV

[manikantabalusa]

videos are excellent and student
Small concern: Please zoom the excel or jupyter notebook, so that we can be able to see clearly

[mousaalmasri]

Hi, is this video a part of your Data Analytics course? I like it, it’s easy to follow up . Thank you .

[JMW]

Does this work in Oracle SQL and is there a way to automatically refresh an Oracle database on a daily basis?

[bruce]

Your videos would be better if your coding screen was set to dark mode.
Some people like me have vision issues and the white screen just makes it impossible for me to see your code.

[mustafakurt]

Step 2 for those working with XLSX file :

Insert into 'created table name' Select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel
'SELECT * FROM [view$]')

[mustafakurt]

I adjusted the second step according to xlsx. no problem...
Step 2 for those working with XLSX file :

Insert into 'created table name' Select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel
'SELECT * FROM [view$]')

Dude can you make a prefix for xlsx files? I made an adaptation, but I am getting an error in the cretae view part, which is the 3rd step that I designed in accordance with my own needs.

[solomong.gebrhana]

This is a great tutorial buddy. But I have a small problem. I am trying to automate importing from a text file and I want to ignore this "quotation mark that is in the text file because I don't want that in my table. The other problem is that the name of my files keeps changing every day for example today's file name was 20221109_registrations.txt and tomorrow it will change the date. So how do I do that for a txt file? and How do I get rid of the "quotation mark? Every day I import I have to change the location of the file as well as find and replace the "quotation mark with a blank space.

[mustafakurt]

For I am working with xlsx file, I changed the query part as follows. following query is working, it is displayed. How can I transfer this temporary query to the header table?
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel
'SELECT * FROM [view$]')

[Daulet]

Hi my question is about excel file size. Does the file contains all data on the file and the it's big? Or all data stored in SQL and file size are small?
It's important for me because i want to use SQL automation in my daily work with dashboards, because the size of dashboards are too high - i have to keep all raw data inside the file.

[mustafakurt]

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel
'SELECT * FROM [view$]')
csv files can only hold one table. If we change the lines of code with the file extension for multiple tables like this, can xlsx be used for files?

[sreejx]

Hi YP i am confused, shouldn't be View inside stored procedure. How does executing store procedure gives an updated View?