BCP (Bulk Copy Program) - Excel to SQL Server - Part #4

okay so the final way I want to talk about getting data into sequel server from excel or from a text file is BCP bulk copy program now this one is a little different and you can see that I've gotten a little harder on each of these types this type here we actually have to use the command line so that can be a little scary for some people and I'll walk you right through it so it won't be too bad so the first thing you need to do is open the command window you do that by going to start and you can type CMD and it will show up up there searches or you can simply go to all programs and go down to accessories and do the command prompt there so I will open command prompt and then I want to say BCP question and if this doesn't show up then something's installed wrong and so you didn't get installed on your system so it should say with it I have here and actually tell you that BCP and then all the different switches you can use to run it well let me pull up management studio here and let's take a look at our products table let's empty that again so what I'll say is delete from products okay so 560 rows delete it no rows left in the table okay so what we want to do is we want to actually load into that and we want to do similar what we did here right which is we want to get data from it first we're gonna use the text file and then we're going to use two CSV file but we want to get data from a text file into products too so let's do that so the first step is we're going to say BCP and then we want to say where we want to get data we want to put data in which is this here so I'm going to copy it and I can paste it in now notice how it's fully qualified it has the database named Excel import training dot schema dbo dot table name products - now we want to put it into there so we say in and then the next thing is where do we get it from right so we get it from over here which is we're going to use the text file first which is the pipe delimited file if you remember right pipe the limit it right so but I also have it copied over here so let's copy that now we'll paste it in there and it's not the CSV this time we'll get the text file okay so now that we've got that there's a few more switches we have put on or a few more things we have to do to make this run we have stayed with their servers at so - s4 server and then my server switch back to management studio again here my server is up here localhost / sequel 2012 Express alright so local host / sequel 2012 Express yours will probably name something different but and if it's the fault instance you have to put it in there but in my case I have multiple instances of sequel server so this will I put in then we also have to put in - see that means it's going to read the text file as all characters is not going to try interpret any type of special numerix or anything like that she's going to read it in and then we need to say same thing we did up here when we're using bulk insert we have to say that we need to skip that first row when you start with row number 2 so we're going to say - F and that's so that - F if you look at your little help file says that is the first row the first row is going to be the second one right so let's start reading at the second row and then we need to put in well what's the what's the Terminator here so as we got here filled terminator comma well that's when we were doing the CSV file we need to do filled terminator which you use the - T not the capital T but the lowercase T and whereas I think there it is filled Terminator right there so lowercase T quote and I'll put a pipe in there okay so that's what's the that's what separates each of the columns in our text file if you don't remember let me pull it open real quick for you we look at one more time right there see every column separated by a pipe well that's what I just put in there to let it know that everything separated by a pipe so now the last thing I want to do is say how I'm going to connect and I'm going to connect in a trusted way and the trusted connection means it uses your Windows user so when you log into Windows you logged in as as a certain user if that years it has permissions to the database then you're fine if not you would have to use the - you for username and the - P wherever the P is for password okay but we're going to use - T which means trusted and so that's it so if this all works right and I hit enter it copied 560 rows so we come back over here select star from products - and there we go so we got some data so now let's do one more with the CSV and it should go a lot faster now let me delete the data real quick delete from products to run that I'm going to go ahead and comment that out so that it's still there and I can run it later but I don't want to accidentally run it any time so let's go try to get the CSV file if I hit the up arrow I get my commit my last command back and all I need to do is change it well remember the CSV file is it's comma separated so I changed the pipe to a comma and then I'll go over and change this dot txt to dot CSV right and now that should be it if I hit enter look 560 rows copied come back over here 560 rows put in so we're good to go so that is how you use BCP it can be a little intimidating because it's command-line if you've ever worked with command lines you can get used to it but it's there's not as much helpful interaction you know everything you're doing is you type in your see if it works and if it doesn't work then you have to try to troubleshoot it's it can be a little bit difficult sometimes but if you just follow what I did there you should be able to get right through it you you

Channel: Joey Blue

Views: 21,939

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

Id: JmcDgdRHcCw

Length: 7min 25sec (445 seconds)

Published: Fri Oct 05 2012