How to Import CSV Data to a Database Using C#

hello everyone welcome to today's video in this video we'll be discussing how to import CSV data to a database using a c-sharp a CSV is a comma separated value file and it stores tabular data where each column is separated by a comma and each row is separated by a newline as you can see here we've got our stock list CSV which just contains a list of products and then we've got three columns product product code and price you can see they're separated by a comma and then we've got 37 lines each separated by a new line at the end here in the description below there is a link to our github page where you can find this product CSV and the code files used for this tutorial feel free to download them and take a look ok now before we can import a CSV we first need to create our database so you need to open sequel server management studio connect to your relevant server highlight the databases column select right-click and select new database this will bring up the database creation screen we're going to call our database CSV import now we can leave these other settings for now they don't need to be changed we'll click OK and our database has been created ok now we need to create with it create the table we do this by clicking on the tables folder right click in selecting table the table creation screen now we're going to base our table on our CSV file and if you remember from our CSV file we add the three columns product product code price so we're going to need to create the corresponding columns so the first we're going to create our product column I'm gonna make it my char 50 and we're going on take a loud noise next we'll create a product code column again in varchar' 50 and one I'll take a long nose finally we'll create our price column should be a decimal and we'll make it a precision of eighteen - and I'm ticked alone ours now we just save this and we'll name it products okay and as you can see our products table is being created see our columns here that match our CSV file ok now we've created our table we can go over to visual studio and start writing our code it's open your version of Visual Studio will create our new project by going to file new and then project type of project we want to create is a console so if you can't find that in the list do a search for it we want to create a console app with dotnet framework so make sure you have that one selected click next we'll name our file products CSV import demo and we'll ensure that this is a correct location yet that's where I want to store it create our project will begin to be created the first thing we need to do in our program is set up our SQL connection do this by creating a using statement it will then I use in statement or initialize a variable of the type SQL connection give it the name Conn I want to sign it the value new SQL connection you'll need to import this program to your library you can do that by clicking on this orange little light bulb over here it comes up when you soak this then you want to import the using system data dot SQL client okay now that's added now I've been here we need to initialize our database we'll be using a connection string of course your connector string will depend on where your server is located the version of sequel server management studio and how you're authenticated teenies your server you can read more about these on the Microsoft web site if you're unsure of how to do this I'll just copy and paste my connection string for my database in here I'm using a like local version or sequel server management studio so I'll just be using my integrated security so I might need to write in my username and password but you might need to do that for yours and then I use in statement the first thing you want to do is initialize our connection we do that by doing conn dopant this opens up our SQL connection to the database then we need to initialize our stream reader the stream reader is used to read data from a file so we'll create a variable type stream reader give it a name reader assign it the value new stream breather now again we'll need to import this you click using system to initialize our streamreader with the location of our slot list CSV so I'll just paste that into there make sure I important to remember to make sure you use these at symbols in both these strings otherwise the compiler won't be able to recognize the slashes of course you could not use the app and just do double slashes but I prefer to do it this way okay now that we've initialized our screen reader we need to create a while loop and within our Rio Luke without the condition not reader dot end of stream this this while loop will continue until our reader has reached the end of the stream and there's nothing left to read him from the file in other words once it's gotten to the bottom of the file and it's finished reading in line 37 ok now I've been out wildly we need to read in the lines so far line equals reader don't read line this will read in the the file line-by-line so every loop through of this while loop will read in the line the while loop will move on to the next line we'll read it again and again and again until there's no lines left in the file and then the wild loop or exit ok now that we've got our line from the file we need to separate our columns using the string split so we'll create a variable called values and we'll assign it the value of line dot split and we're going to split on the comma because that's what separates each column and this will just create a an array for each column so our rate will be have three elements in it okay now that we've got our elements we need to create our SQL insert let's redo that PI cream bar SQL and then we're going to write out it an insert statement string so to insert into and now we need to do the name of our database which is CSV import dot DB Oh dr. products which is the name of our table so this is the name of our database and this is the name of our table there were two values and then within here we're going to concatenate your string of all the values we want to insert so first we're going to insert our product which is the first value in our array which of course starts at zero so this will get this will get out the product name remember to surround this with a single quote as it's a string SQL for an error otherwise open our comma and use one which will be our product code again surrounded by a single quote always miss without there as it's a string value and do a plus and values to which is our final column which is the price of course we don't surround this with a single quote as it's a decimal value there we go our SQL statement that's been built now we fill our SQL statement we need to actually carry out the SQL and execute on our connection we do this using something called an SQL command the SQL command allows you to execute SQL queries or stored procedures so we'll start by creating a variable called CMD assign it the value of new SQL command the first part of the command we need to build is is the text so this is the the query that's going to be executed so we do CMD command X we're going to assign it the value SQL because that's the SQL statement we've just built and that we want to execute next we need to do command type which in this instance is text so make sure you do system data command type dot text that's because our query here is pure text we're just going to get the SQL to run a pure text query next we're going to set up our connection which we've initialized up here I asked your connection this is the connection directly to our database so do CMD doc connection equals con finally we'll do CMD not execute non query and this will execute our query we put a bracket to the end there okay there we go so now every time we go through this loop or read the line will split out the values we'll build our SQL statement we'll start our SQL command will execute our SQL command and then we'll loop back through we'll read in the next line and we'll do all the same until there are no more lines left to read and then we exit the wildin which point at the bottom of this using statement we wanted to conduct close we don't we never want to keep our connection to our database open this we'll just close it off stops our program from using it we could run this now but it will cause an error and the reason that an error will be caused is because we'll be reading in our heading line the heading line is the first line of a CSV not all CSVs contain it but the head in line will define what the columns are so we know that the first column is product second column is product code and the final column is price at the moment our program will read in this line it will try to insert price into our price column in the database which will cause an exception because of course it's not a depth the word price is not a decimal so there are a number of ways we could avoid this we can have a CSV we could just remove the M heading from the CSV file we could do some validation checks but the simplest way to do it is to just not read in the first line we do this by first create a variable called line number assign it the value of zero then we're going to create if statement just under where you've done var line equals reader don't read line it'll be if line number does not equal zero then we'll just copy all this yeah put it inside our if statement and an outside of our statement we'll do a line number plus plus which will increment the value of line number okay so let me just explain what we've done here so on our first pass-through of the while loop we first enter or read in the line well which will be the first line of the file which of course is the heading line we'll go to the if statement and at this point line number is zero because that's what we set out to begin with and we're on our first pass through so we won't meet the condition for this if statement so we'll pass over all of this so you won't process the header line will then increment line number by one and we'll go back through the while loop will read in the second line which of course is the second line of product data just under the head of column we'll make it into the if statement because at this point line number does equal one will come in here will process our data we'll insert it to the database and we'll keep looping through until we reach the end of the file at which point we'll exit the while loop and the connection will be closed okay now finally sanity purposes I'm going to do a console dot write line at the end of this to let me know that the price enters has done import complete the console.readline okay now this should be ready to run and hopefully fingers crossed we'll rinse out our data to the database without any issues okay that's good it's just building but our products import complete so I'll close this now I'm going back over to our sequel server management studio go to our products table do you select top 1,000 as you can see we've got all of our products and set it in the database of course we've only got 36 rows because we've skipped the first line we don't want to import that that's the header line and we've just import our first 36 products okay that's it for this video thank you for watching I hope you found it useful and if you have any questions please feel free to leave leave them in the comments below as mentioned at the start of the video our github page can be found in the link in the description where you can find the code files and the product CSV we've used in this tutorial make sure you check out our website code absorb com for more c-sharp tutorials thank you for watching see you next time

Channel: Code Absorb

Views: 5,266

Rating: 4.9487181 out of 5

Keywords: Csharp, C#, c#, csharp, Programming, programing, program, programm, CSV, SQL, How to import csv data, How to import csv data using C#, C# CSV import, C# Data Take on, C# Datatakeon, C# data takeon

Id: 9bt4Ixe10tQ

Length: 16min 9sec (969 seconds)

Published: Mon Jan 20 2020