Bulk Insert - Excel to SQL Server - Part #3

okay so the second tool I want to talk about is the bulk insert so I showed you about the import/export wizard which is pretty nice too it makes it real easy to import and export but there's times where you may want to actually do it through sequel and so sequel has this book insert statement you can see one here it's an example from the help database on Microsoft and it's a bulk insert sales order detail from some some file so let's head over and let's try it ourselves so the last time we left off we have data in the products Excel and we don't have any data in the products table right and so I have a sequel script already up has the book insert stuff in it so how do we how do we actually crack this thing up and the first thing you do and here let me go ahead and do this I will go ahead and open a new query window and we'll do it from scratch so the first thing you do is you say bulk insert and then you say what you want to insert into and is probably best to say a fully qualified table name so fully qualified means database dot schema table name so the database is Excel import training dot DV o dot products so we're gonna put it into the products table the next thing you do is you say from let me go ahead and get the the case right here it doesn't matter but it just looks better for this example so I'm just putting that upper case so the next thing is from well were you getting it from right and so I'll put two ticks and then I need to get the location of where we're getting this from and we're getting it from the demo folder in my C Drive / and then I need the products table so let's have to do a little copy paste that in there right and so then there's a few switches we need to put in there and so we put a couple parentheses we need to know the field Terminator so the field Terminator says well is a commas is it pipes how is it terminal how's each field separated and in our case right now with this file where you're going to use the pipe so let me use a comma and then we say row Terminator and then we use the backslash in lower case in which is the carriage return and let's just start there okay so this should bulk insert into our table from the products database but before I do that there's one other thing I want to do and I forgot to do this but when we when we were in the last the last video we just did the default so we brought everything into the import/export wizard and it brought everything as a far char well cost is actually a decimal product dates actually a date right so so those things in a regular database you'd have those the right data types well I've got a little script over here somewhere there it is and I want to create a new table products product too and it has you know integer for Product ID integer for category ID subcategory ID decimal for cost and a date so now we've got some real data types here and so don't need that it'll default so I've got this script here create great table so let me execute that and let me actually a new connection sorry I killed I killed this I had a database going before and I killed the connection so let me just grab that I'll copy it into a new query with a connection and we'll run it again and so now I've got a new product stapled to over there and there it is right and the data types for the columns we have integers and decimals and stuff so this this is going to make it interesting and we should get some failures so let's go back over here and step insert into products products - okay so let's run this and see what happens in correct syntax near-field terminator ah I forgot of keyword the keyword I forgot is with there we go so before you put these parentheses you need the width keyword so let's go again okay so this is what I was looking for it says both a load data conversion error type mismatch and it says row 1 column 1 product ID well how do we how do we troubleshoot something like that well the product ID is now an integer we know over here so let's go look at the first row of our text file I'm going to open it up oh the first row is the header right and so it's trying to take this and insert it into an integer and that's a string so what we need to do is tell it to skip that first row don't we let's go back over and there's another clause we need to put in there and it is called the first row we'll say the first row is number 2 so now let's do this again and everything went in just fine so that first row was getting us in trouble so now if we go to the products - table let me get new query again select star from products - and we have data and products - and so let's go ahead and the next thing we want to do we go ahead and delete out that table and I don't need to start there okay so we got rid of all the data there so now if we just select okay data is gone the next thing I want to do though is we have that excel file right and we want to get that import it into a table so let's go open the excel file it's an excel uh SX and with bulk insert you can't do it directly so what we're going to do is we're going to take it and save it as a CSV file so I go to file save as and I'm going to say CSV file where's that at right there so I'm gonna have a product CSV file out there now we'll get this little message that says the file type does not support workbooks that contain multiple sheets to save all sheets save them individually or we can just save the active sheet well we only care about the active sheet okay so that's fine oh and then we got another message products CSB may contain features that are not compatible with CSV MS DOS do you want to keep the workbook in this format well right now if you look at like this header row this header row has a formatting on it you know colors borders and stuff like that well when it says saves as a CSV it is not going to be able to save that formatting stuff and it wants to know if we want to if we if we actually save it we won't lose all that formatting right well we don't care about that we have an xlsx file that has that in there and we want to save the CSV that's just text okay so let's go ahead and say yes and then close it down it asks us to save again and we could save it again or you can just say don't say so now if you look we have a product CSV file and we'll right-click on it and I'm going to open it in notepad and you can see that we hit its comma separated all right so all the values are separated by commas the first row has the header in it and then one other thing to look at is this right here so the cost on some of these has a comma in it this is going to show us this is gonna prove to be a problem here but I'm going to run it anyways just to kind of so you can see what happens alright so now we have a CSV file it was it was what our Excel was we saved it excel at CSV and now let's go and let's get this thing in there so we're gonna stick it into products too and we're going to get it from the CSV file right get the case right okay now the filled Terminator is a comma still going to skip the first row so here we go you ready and so now we've got some problems and we have a problem with row 7 and 8 14 and 15 on the product cost right let's go look at those so if I open this a notepad and we look at 7 & 8 right there you can see that there's commas in there well calm is not a valid value to stick inside of a decimal it's just there for formatting reasons isn't it it's just third for to help us read it but it's really not a number so what we need to do is resave our CSV file this is the problem that you end up having I've seen quite a bit when you have an excel file that somebody save it as a CSV but there is a cure for that and so what we want to do is all these commas I'm just going to take this column highlight everything and I am going to turn it into text so see now those condos commas are gone okay so let's go ahead and now let's save it as a CSV file and we will replace it yeah the other one doesn't work anyways and we have multiple sheets that's okay and we're not going to have a format anymore okay great okay so now we've got it saved let's go ahead and close this out and now if we go look at the CSV file notice that our comments are gone out of the numbers and then they didn't have to put quotes around it either so let's go try this again so all we have to do is rerun this first of all before I do that I want to see where I mean data in here okay no data now let's go back over and I will run it and no errors 560 rows were put in that error is gone and we can just simply select from the table 560 rows are in everything looks good so that is how you use bulk insert and not the import/export wizard but the bulk insert in order to get data from a text file or Excel into your sequel server

Channel: Joey Blue

Views: 46,168

Keywords: Microsoft Excel (Software), SQL Server, SQL, data, Technology, Office, Bulk Insert, microsoft, sql, server, 2012, excel, bcp, bulk, copy, insert, import, export, Microsoft Office (Software)

Id: fAETcQ5CST0

Length: 12min 25sec (745 seconds)

Published: Fri Oct 05 2012