They are all good at telling you that Excel is not a database, which is true. It is not a database, but they don't give you options, they don't tell you what to do, but don't worry, I'll tell you what to do, we'll see in this video How to pass your Excel files to a sql server database since sql or sql server if it is a database we are going to see how to pass those Excel files and accumulate them in a table of a sql database to later create queries from Excel to that sql database using Power query and making dynamic tables sounds complicated to you, don't worry. It's easier than it seems and you already know that in this channel we explain step by step in detail so that you can replicate the steps you want to learn on your computer I teach you [Music] as I told you at the beginning everyone is good at telling us Excel is not a database And now but they don't give you options they don't give you solutions to move to a database but I do I will give you a solution, we are going to see how to pass Excel files to a sql database, we are going to be accumulating them. For example, I have here this Excel file where I have the date of the branch vendor and sales. We have here a random point between where we are going to simply generate for this case dates of January 2023 then sellers Let's put a space here sellers branches here we use the random point between function to literally invent values So I want to pass this information to a sql database imagine that you have a daily file or a monthly file and what you want is to accumulate all the information month by month or day by day in a sql database and then from Excel make queries to sql using Power query and then make a dynamic table that we will see in this video here we have then this file called month is an excel file I'm going to save that file control G simply and then I'm going to save it again But now as csv type f12 here in this folder called months asql Here I'm going to put the name january and here in type I simply press the c key and it is saved as csv delimited by commas save I am going to copy and paste values in this file ready and as an important step If we want to pass this information to sql we have to choose the date column in case If you have it, right click we say cell format and in personalized we are going to say year month format via because the database is handled with this format so I save the csv file now what we have to know is if in sql server we have a database in this case I am going to show you how to create a database and a table to accumulate these files I am going to open the sql server management studio application in the video that appears below I show you how to install sql server on your computer and also How to install this application here this application is installed locally on my computer if you have a sql server in the company one has to ask you or systems for access to create a table or two for you if you manage the sql server You are going to have to create one of data and a table as I am logging in locally to the sql server I am not going to put a password on it if you are logging into a company server then surely they are going to ask you for sql server authentication anyway this channel I have a mini course dedicated to sql server correct So let's connect remember here we have the sql server service on my computer I go here on the left side aveises and we have a single norwin database I right click here on databases I say New database We are going to create a new database called sales here Ok and then I display and here in Tables I say right click new Table we are going to design or we are going to create a new table that new table I need to have these four Fields so we start with the field date of what type of type of it then the vendor field of type nbarchar 50 50 characters then the branch field of type equal to nbarchar 50 And finally sales this will be an int or integer type so date vendor branch and sales depends on the complexity You can create a master key from your table. In this case, we want to create a simple table, correct, so we simply save the name of the table here. the table is going to be sales we put sales per month preferably put the name to the table without spaces OK now in the left part Here I am going to close this part in the left part I display tables and here is sales per month if I right click and I tell her Select top 1000 Rose is going to show me the information that is inside the table, it is currently empty, correct, we want to pass her the information from this file enero.cv so we agree that if we have a date field it must be formatted as year month day I close the file Important csv must be closed to upload it to the sql table I'm going back to the sql server administrator or the sql server management Studio here we agreed to make a query of the table this from Select we see it in the mini course I have canal sobre sql Remember that any content that you want to search on YouTube simply put excellent info space and the name of the topic that you want to search for if I have a video it will appear first correct then pay attention what we are going to do I click here on newquery and we are going to use this code this code is transac sql or tsql we use the bulk insert function what it does we are going to insert into the sales table of the sales database Sorry table sales per month in the sales database we are going to insert or we're going to load this file jan dot csv which is at this path the delimiter is the comma so the file was saved as comma separated this code is going to allow me to take this file and accumulate it or insert it all at once table sales per month I return here to move between tabs control tab So we are here and to execute this query we click execute here it says that a topic with the date But nothing happens here is that control tab is complete and I'm going to execute again the query leather of the sales table per month and the information is here, look carefully, it has already been inserted, I have done tests with files of one thousand thirty thousand records and they are executed or inserted very quickly here, for example, I am going to put Select top 1000 two zeros to make it 100,000 I execute and here we have the 1000 correct January records and now, since we already have the information in a database that is not Excel, I go back to Excel and we are going to make a query to the sql server to make a dynamic table based on a Power query I go here to data get data from a database I tell you from a sql server database here as we are entering a local instance simply here in the name of the server I give right click properties I take this name I copy the name I come back and I put it here If you were connecting to another database that is not local but on a network, they may pass you the name or the IP, optionally the base of data in this case it will be sales if you are going to connect directly to a sql server database be very careful if that table has millions of records before asking you if they can not create a view of that information so that you do not hit it to the performance of the sql server accept here we are connecting to the sql server that we have locally here on my computer We have here the sales per month table I have the information here I can transform data with power query that we already know remember that in this channel I have a Power course query or directly we tell it to load I'm going to tell it to load in and we are going to load the information from the table to a dynamic table directly accept on the right side the information is being loaded Here it is 10,000 rows were loaded Here is the panel of the dynamic table Look closely I move date here rows then sales good branch I move it here sales columns here values I right click here in any cell of the date field and I say group we want to group by months just accept and here is the accumulated information for January this table dynamic through Power query connects to sql server now let's do the following I open the Excel month file again and we are going to invent the information for February we wait for the file to open by choosing this cell control shift below and we are going to tell it about the first of February to 28 this year the month of February has 28 days control enter f12 now I tell it to call it February tabulator and type the letter c for that it be saved as csv save the format again I tell you that cell format at first may seem cumbersome But believe me when you already master it and do it many times you already do it mechanically then year month day I save and close I go back to sql server control tab and here now I am going to tell you February in the same folder is here It goes up I run very well here is the confirmation and here remember that we only have information for January so you ran this Curie and lower and we already have February very well here we are accumulating the information can be per day it can be per month as you want but the issue is that you have a cumulative to be able to make your pivot table or a dashboard or whatever you want I'm going to Excel right click on the pivot table update and it already gives me the information from February Very good So what do we see here very interesting topics Excel is not a database it is very clear to me but many times the information comes from Excel so we pass that information directly to sql that here in this video we saw The vulk insert Command But There may be other options, surely, whether you know it or that the IT department can give you a solution so that you can load your Excel files directly, or many options, there may be only one of many solutions. So how are the data in Excel? on many occasions you pass them to sql and now your queries are directly to a sql server database and now on the left side you will see the sql course where I teach you how to install it and the basic commands of transac sql and in the back right you will see the Power query course that I have for you if you handle data in Excel you are a crashboard analyst or you simply want to learn more visit this content
