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

[@YiannisPi]

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

[@jerrymadu2977]

WOW!! very interesting, Thanks a lot.

[@joseph354]

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]

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]

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....

[@YiannisPi]

Glad you liked it!

[@muatasimkamal3542]

@@YiannisPi 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 ?

[@YiannisPi]

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

[@muatasimkamal3542]

@@YiannisPi Thanks

[@mariesandrine9170]

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

[@bouseuxlatache4140]

this is really a precious video. thank you.

[@mustafakurt3546]

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

[@SophieGu-qh6me]

Thanks for sharing! Great instructions.

[@findthetruth3021]

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

[@YiannisPi]

Thanks mate! will do!

[@ganeshsm6814]

Lovely. Well explained in detail

[@JMW376]

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

[@kkwesterlund]

Thanks mate, great video!

[@YiannisPi]

Glad you liked it!

[@mousaalmasri4641]

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

[@YiannisPi]

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

[@yoshihirokawabataify]

Thanks movie Please check feature for import data from CSV

[@navjeetchhabra6822]

Top job mate..!

[@YiannisPi]

Glad you like it mate!

[@AliBuKarrar]

Very useful. Thank you

[@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.

[@manikantabalusa441]

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

[@YiannisPi]

Will do! Thanks!

[@mustafakurt3546]

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?

[@sreejx]

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

[@swankyshivy]

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

[@mustafakurt3546]

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$]')

[@sebastianrubio2882]

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 :(

[@themeverse7000]

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

[@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.

[@mustafakurt3546]

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$]')

[@personmcpersonface2063]

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

[@mustafakurt3546]

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.

[@meghaarora1853]

Awesome!

[@YiannisPi]

Thank you! Cheers!

[@mohamed.montaser]

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

[@YiannisPi]

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]

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 :(

[@nicolemwanaidi1488]

Amazing

[@YiannisPi]

Thank you! Cheers!

[@KJOB]

Broooo youre a messiah

[@bruce2357]

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.

[@rakeshcs8684]

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

[@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.