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

Рет қаралды 57,896

Күн бұрын

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:
/ data360yp
Data Analytics Course Link:
ipidata.teachable.com/
GitHub Files:
github.com/Pitsillides91/SQL-...
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:
a. • Data Analytics Excel T...
b. • Aggregate Functions in...
c. • How to Create Pivot Ta...
Excel Dashboard creation:
• • How to Build Interacti...
•
SQL Tutorial Links:
d. • SQL Tutorial for Begin...
e. • SQL Tutorial for Data ...
f. • How to Join Tables in ...
SQL most common queries:
- • SQL Examples For Pract...
How to Automate Excel Reports Using SQL [CSV to SQL to Excel Automation]
Yiannis Pitsillides on Social Media:
/ pitsillides91
ypexists?h...
www.pinterest.co.uk/pitsillid...
/ 1500092413449073
Tags:
how to link sql to excel

Пікірлер: 55

@Data360YP 4 жыл бұрын

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

@jerrymadu2977 4 жыл бұрын

WOW!! very interesting, Thanks a lot.

@joseph354 3 жыл бұрын

Newbie question: if I update the database in the SQL from USA and my friend run the automatic update to the excel dashboard it in Thailand, can it works?

@nathanaelmarcel5455 2 жыл бұрын

I know im randomly asking but does someone know of a tool to get back into an Instagram account?? I stupidly forgot my account password. I love any help you can offer me!

@nyali2 Жыл бұрын

I am in finance and was trying to create a linked server between excel and sql, but couldn't get anywhere, due to an error 'ace.oledb12 not recognised'. This has basically solved my problem... amazing I will have a look at your patreon for sure... Massive thanks!!!

@MuhammadUsman-pv4ds Жыл бұрын

Very nice work done. Can you please also keep the final neat and clean SQL code? In the last part of the video, it is a bit fast and ambiguous to see changes in the command lines. Rest is all great.

@arjunwarrierkrishnan4133 3 жыл бұрын

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

@mariesandrine9170 3 жыл бұрын

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

@SophieGu-qh6me 2 жыл бұрын

Thanks for sharing! Great instructions.

@ganeshsm6814 3 жыл бұрын

Lovely. Well explained in detail

@bouseuxlatache4140 Жыл бұрын

this is really a precious video. thank you.

@findthetruth3021 4 жыл бұрын

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

@Data360YP 4 жыл бұрын

Thanks mate! will do!

@AliBuKarrar 2 жыл бұрын

Very useful. Thank you

@mustafakurt3546 2 жыл бұрын

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

@kkwesterlund 3 жыл бұрын

Thanks mate, great video!

@Data360YP 3 жыл бұрын

Glad you liked it!

@Mezaadi 4 жыл бұрын

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

@Data360YP 4 жыл бұрын

Glad you liked it!

@muatasimkamal3542 2 жыл бұрын

@@Data360YP I was trying to download DEMO_DS_29112019163028002.csv from Kaggle. But the file is not available anymore. If you still have the file; Can you share it ?

@Data360YP 2 жыл бұрын

@@muatasimkamal3542 It's available, check the link again. Thanks

@muatasimkamal3542 2 жыл бұрын

@@Data360YP Thanks

@navjeetchhabra6822 3 жыл бұрын

Top job mate..!

@Data360YP 3 жыл бұрын

Glad you like it mate!

@mousaalmasri4641 4 жыл бұрын

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

@Data360YP 4 жыл бұрын

Hey, yes it is. Both for Excel and Power BI

@meghaarora1853 3 жыл бұрын

Awesome!

@Data360YP 3 жыл бұрын

Thank you! Cheers!

@themeverse7000 3 жыл бұрын

Hey Is it possible to do the same automation in Metabase as well?

@nicolemwanaidi1488 4 жыл бұрын

Amazing

@Data360YP 4 жыл бұрын

Thank you! Cheers!

@sreejx 3 жыл бұрын

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

@yoshihirokawabataify 4 жыл бұрын

Thanks movie Please check feature for import data from CSV

@JMW376 Жыл бұрын

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

@swankyshivy 3 жыл бұрын

do you havr this for ms access db rather than sql server?

@Daulet2009 Жыл бұрын

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.

@solomong.gebrhana1204 Жыл бұрын

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.

@sebastianrubio2882 3 жыл бұрын

I get syntax error near format BULK INSERT Raw_Data_GDP FROM 'C:\Users\...\...\Desktop\gdp_raw_data.csv' with (Format = 'CSV'); I dont see anything wrong :(

@mustafakurt3546 2 жыл бұрын

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Users\*******\Desktop\CA.XLSX', '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?

@personmcpersonface2063 3 жыл бұрын

Hello, I am repeating code from this video and get an error: Msg 102, Level 15, State 1, Line 11 Incorrect syntax near 'FORMAT'.

@manikantabalusa441 3 жыл бұрын

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

@Data360YP 3 жыл бұрын

Will do! Thanks!

@mustafakurt3546 2 жыл бұрын

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 12.0;Database=C:\Users\*******\Desktop\CA.XLSX', 'SELECT * FROM [view$]')

@mohamed.montaser 4 жыл бұрын

how did you calculate GDP_PER_CAPITA without saying Group by Clause? and can all this be replicated in PostgreSQL?

@Data360YP 4 жыл бұрын

GDP_PER_CAPITA is not a calculation - I just filter it out and left join it on table a. Not sure if you can do this is PostSQL, I have never used it

@mustafakurt3546 2 жыл бұрын

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 12.0;Database=C:\Users\*******\Desktop\CA.XLSX;HDR=YES', '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.

@mustafakurt3546 2 жыл бұрын

How can we do this for the .xlsx file? Is csv required?

@jithingeorge6298 Жыл бұрын

Hi did you find how to do it with xlsx ?

@mustafakurt3546 Жыл бұрын

@@jithingeorge6298 Unfortunately no :(

@KJOB 2 жыл бұрын

Broooo youre a messiah

@mustafakurt3546 2 жыл бұрын

Step 2 for those working with XLSX file : Insert into 'created table name' Select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Users\*******\Desktop\CA.XLSX;HDR=YES', 'SELECT * FROM [view$]')

@rakeshcs8684 3 жыл бұрын

You could have made the video in zoom ..it's difficult to watch where and what change you made.. content is fine

@bruce2357 5 ай бұрын

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.

@solomong.gebrhana1204 Жыл бұрын

USE [DB_name] GO --Use for Wallets-- BULK INSERT [dbo].[Table_name] From 'file_location' With( firstrow =1, rowterminator = '0x0a' ); This is the code that I use to import that file every day. I have to change the file_location, DB_name, and Table_name every day to import and I'd like to know if there is a way to automate it. Just so you know, I am importing from a txt file that is generating every day, and there is a "quotation mark that I have to find and replace before importing in the txt file.