How to Automate Excel Reports Using SQL [CSV to SQL to Excel Automation]
Vložit
- čas přidán 24. 07. 2024
- 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 - Jak na to + styl
![How to connect POWER BI with SQL and build an AUTOMATED Dashboard [SQL to Power BI]](http://i.ytimg.com/vi/WgE8pVLgWGs/mqdefault.jpg)
Hi everyone! What do you think about this process of automation? You think it can save you some time?
WOW!! very interesting, Thanks a lot.
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?
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!
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!!!
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.
Great going! I love the pace at which you take up the steps. No nonsense straight talk. Keep them coming!!
Thank you very much..Your videos are awesome and simple to understand..Great Job!
Thanks for sharing! Great instructions.
Lovely. Well explained in detail
this is really a precious video. thank you.
Bro....You are a genius...thank you so much.
Very useful. Thank you
You have done a very good job and mentioned every granular details which really helps.. keep it up bro....
Glad you liked it!
@@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 ?
@@muatasimkamal3542 It's available, check the link again. Thanks
@@Data360YP Thanks
Thanks mate, great video!
Glad you liked it!
I really love your videos bro. Keep it up :)
Thanks mate! will do!
Top job mate..!
Glad you like it mate!
Hi, is this video a part of your Data Analytics course? I like it, it’s easy to follow up . Thank you .
Hey, yes it is. Both for Excel and Power BI
Thanks movie
Please check feature for import data from CSV
Hey Is it possible to do the same automation in Metabase as well?
Does this work in Oracle SQL and is there a way to automatically refresh an Oracle database on a daily basis?
Hi YP i am confused ,shouldn't be View inside stored procedure. How does executing store procedure gives an updated View?
Awesome!
Thank you! Cheers!
do you havr this for ms access db rather than sql server?
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.
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.
Amazing
Thank you! Cheers!
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?
videos are excellent and student friendy......
Small concern: Please zoom the excel or jupyter notebook, so that we can be able to see clearly
Will do! Thanks!
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 :(
Hello, I am repeating code from this video and get an error: Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'FORMAT'.
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$]')
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.
how did you calculate GDP_PER_CAPITA without saying Group by Clause?
and can all this be replicated in PostgreSQL?
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
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$]')
How can we do this for the .xlsx file? Is csv required?
Hi did you find how to do it with xlsx ?
@@jithingeorge6298 Unfortunately no :(
Broooo youre a messiah
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.
You could have made the video in zoom ..it's difficult to watch where and what change you made.. content is fine
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.