Import Multiple CSV Files into SQL Server using stored procedure.

hello there my name is joseph losoma welcome to our channel uh today's video is going to be looking at uh how to import multiple files into database today's files we are going to be looking at are csv files but the same video can be used for text files uh so we are going to look at three items one creating file path creating tables that are going to hold our imported data as well as creating a smart procedure that is going to be importing data from files as you can see i've gone ahead already created my folders so on my c drive i've created importing files to db and in here we have two folders our new file folder is the one that is going to be holding our new files archive folder is going to be holding files that have already been processed so the flow is that after file is processed uh i'm going to move that file through the start procedure to this folder now let's look at our file so this is our csv file we are using as an example in today's video so this is how our file looks like row number one is a column headers as you can see um the rest are data rows as you can see that columns are separated by commas please note in this example we don't need we don't need this and we shall remove it because um we only this the the column headers are will be created as part of the table in esco while processing the data so we have to remove this and let's remain with the raw data that will be imported so the next step is to go ahead and create tables that are going to hold our data that is going to be imported so i went ahead and created our table that is going to hold price index data so i'm going to use this file as an example in this video so our table has been created to match the number of columns in our data ensure to match the number of columns in the data to avoid any failures when you do importing data from the files and our table we can see [Music] so we have created our price index table that is going to be holding our data uh uh i'm also going to create a table that is going to keep records of files that have been processed this is a file logs table i will be keeping record job files that processed the file name and the date it was processed now we know that we have the file the price index table that is going to hold our data and also the file table that is going to hold our processed files the next step is to create a stored procedure that is going to be running and extracting data into our into our press index table i'm going to create procedure we are going to name it import files and this procedure is going to have uh three three the first input is is the file path now the file path is where our [Music] files are located and also it's going to have a pattern now file pattern are file extensions so if we are processing files like csv or text files the pattern is used to differentiate the files then the last the last parameter is going to be the table name so this table the table name refers to the table where our data is going to be imported so we are declaring uh we are going to use this variable to to to hold our query commands uh also note that uh we are going to use a window functionality for whole windows functionality cmd it's an inbuilt functionality also in sql that will be executing commands via sql so this variable will be holding our [Music] our command for moving the file uh because we are going to to be reading file names from this folder and we are going to create a temporary table a temporary table to hold our file names so this temporary table will be holding our file file names so now we have created uh our temporary table now let's uh set now let's set our let's set our command so in sql how we execute the command the command is in master master database so i always make sure that i type master.dbo then xp command shell so this is the uh it's similar to windows cmd so but how we call it in sql we call it by by executing this command then in order our in order to extract file names let me show you an example here so the command directory for slash b returns file names in the in the given path as you can see only that when you run directly only uh it will show you extra information we don't really need information like that created uh the file name the size uh so here we are only interested in capturing uh file names extracting file names in a directory so for this case we are going to type a command directly and we are going to to put a file path and the pattern so we are going to insert all we are going to insert our all file names into our temporary table so we insert by calling the function execute and executing our query so our query is going to execute and return all file names in that sp given file path but also to note the query is going to return files that are aligned with the pattern for example if it the file is so sometimes after inserting file name sometimes folders can be empty and this command tends to insert null values in our table so to avoid processing null values what we do is to delete from our table our temporary table x our where name is is null so after deleting our null values from our temporary table then we need to give a unique id to each files to each file name in our temporary data table however we are not going to do it within the same table so what we are going to do is is to insert into this this value into another temporary table with named y so uh let's let's drop our our table x since we no longer need it uh currently our very values are being held in uh our temporary table y but we have gone further to give each each file name uh a unique id such that when we are doing a while loop down we shall be going through are they by referring to the unique ids we have assigned to each file so here we have declared two variables one a max one and count one a reason why i couldn't use max because max is a result word and count is a reserved word also so so max is representing the total number of files in our table y and count is going to be counting [Music] during our while loop so uh let's get the value of max1 by selecting the maximum id from our temporary table why so so the max id refers to the max the largest id in our our temporary table so that will refer to the uh largest number the the total number of of names in our table so let's also set at our count equivalent equal to zero um what we are going to do we are going to use a while loop so while count while count one is less than max where uh note that max is uh is the total number of elements or names in our temporary table y so if our count is less than max it means there are still more elements in the table so we are going to set our increment our count increment our count with one so we are using a while loop to pick file names from our temporary table why so let's say we missed out declaring a file name so let's define a file name here and let's name it give it 200 so let's get our file name by selecting by selecting name from our temporary table y where id is equal to count so after selecting the file name so the next step will be reading the contents of that file and inserting the contents of the file into our database so um let's use our variable query to to to to construct uh a command for extracting data from the file uh the file above uh so in this case we are going to use bulk insight so we are going to bulk inside table name where the table name is our price index um so file path and file name will give us a complete file path now with with our parameters or options for extracting data so the first parameter we are going to use is the field terminator as you can see that now the field terminator we have we are putting here is our comma because uh our data our data is separated or delimited by by comma so that's why our field terminator is a comma so another another parameter is the first row so the first row is used to to to determine where to start when we say row one it means we start from this first exact row when we say row two it means it will start on the second row now for this case uh we want to start from row one and uh finally the final final parameter is uh the row terminator so for row terminator uh the row terminator is a new line as you can see the column terminator is is a uh a comma now for rows rows are delimited by new line you can see each row is on a new line so every new line means a new row so that's why we are putting the raw terminator as the new line so this is our this is our command to extract data from the file in this path into our table and we have given parameters that define our data so uh when when bulk insert goes into the file it will check for columns basing on the uh uh comma and it will check for new roles basing on the new line and it will start by extracting data from row one so um we run we run this command we run the command by executing by calling the functionality called execute so after executing the file this command is going to insert data in our price index now after running the file we are going to record that file in our log table so we are going to record this file in our logo table so after uh logging our file the next step will be moving that file into an archive folder so we are going to set our variable uh move processed so we are going to write a command so in order to move files on window command we write move then have file file path with concatenated with a file name so we are having so the first part here is that we are moving a file from the source and then this is going to be the destination so this is going to be our file destination however our our destination in our destination we need to so we are going to get the original uh we are get we have gotten the original file name and now we have removed the the file extension so when we are archiving we are going to add a time a time so we are going to get the file name minus the extension and also add the timestamp and our the file extension at the the end so we've set our variable for moving a file the last thing we need to do is to execute now since since uh the variable you've created is a windows command so how we run it we run it by calling master and remove the fire so finally this is our final output as you can see we use a while loop to to get the file names that we have temporarily saved in our database we read the contents of the file and insert the contents of the file into our table and we execute this variable by calling the functionality execute then we will record the we record we record the file name in our log table then we move finally move that file to an archive location so uh thank you very much for [Music] so let's uh let's uh let's run so we have run and when we check our storage procedure it is there dbo import files so the next task will be uh running it will be running our our start procedure so this is how we run you can see that this is our folder where the csv files are we've also put the file extension this is the here let's run our job our our stored procedure import files you can see uh it has processed now when we check our price index you can see that data has been uh imported from the file and also when we take our file log you can see our file has been [Music] recorded so in go back to our folder you can see that our file is no longer here but it has been moved to an archive and you can see that we have put a timestamp on the file uh thank you very much that's it from us today i hope you've enjoyed and we shall share more contents bye

Channel: JSTS - Think Beyond Code

Views: 12,086

Keywords:

Id: L7IfpvaSz4A

Length: 25min 21sec (1521 seconds)

Published: Mon Sep 06 2021