How to import data from Microsoft Excel into Microsoft SQL Server

how can you import excel data straight into sql server in this video we're going to have a look at a couple of ways hello my name is philip burton from sqlserver101.com so we've got here some data so you can see we got an employee number which is a whole number we've got first name last name and middle name middle name can be nullable in other words it doesn't need to be a value for every single row we've also got government id a date of birth and a department so how can we get this into sql server well there are two ways the first way requires us to have this not in spreadsheet form but as a flat file so a csv comma separated values or a text file so to do that i'm going to go to file save as browse and i'm now going to change this so that it is a comma separated value file and i'm just going to add csv to the end of the file name so click save now this does not support workbooks which contain multiple sheets that's absolutely fine i've left some additional sheets in here so you can see these sorts of messages so let me close this now and we go into sql server and i'm going to right and click on the database that i want to insert this table into and i go to tasks and import flat file so remember you must right and click on the database don't right and click for instance on the tables you won't get that same array of options so let's go into import flat file so this takes me down a very nicely done wizard so let's click next we need to specify the input file so i'm going to click browse and there is my csv so you can see i have actually added csv to the file name but this is the only time you can do in this particular dialog box csvs all text so next i can preview the data so that shows the first 50 rows click on to next and now we can modify the columns so you can see it has got some suggested data types now these are a bit overblown en varchar so that means i can use the entire array of characters they don't have to be western european in basics so i could be using japanese characters chinese characters korean characters so quite frankly this is a bit too much also it's given me a default of 50 which i don't mind so much to be honest so you might want to change this from an n-var chart to a varchar for example but i'm just going to leave these defaults as is you might also want to set up a primary key so that is something that can't be null so it's got to be present in every single row and uniquely defines that row so the employee number is different on every single row so let's click next so here we can see a summary of what i'm going to do click finish and there we are it is inserted now this was fairly quick for this particular database and table because it wasn't that big it may take a few seconds so let's just have a look select star from and we can see the table here data to import csv if you can't see that particular table you might need to refresh it i've also got the squiggly underlines so i need my intellisense to be updated and i can do that by holding down control shift and r that will get rid of the squiggly underline so let's execute that and there we can see my data now what if i didn't want to export it into a csv what if i wanted to use an actual excel spreadsheet well we can do that as well but this time it's instead of going to tasks import flat file we import data now this is a more complicated dialog box this is a cut down ssis sql server integration services task so what i'm going to do here have my data source one of my data source is in microsoft excel data source make sure we've got the excel version to be honest it doesn't really matter as long as you've got before 2007 or after 2007 so i'm going to say excel 2016 even though excel 2019 is available it's not included in this so it doesn't matter too much i'm going to keep to excel 2016. click browse and there is my non-csv data so you can use xls xlsx xlsm and xlsb so click open and now my destination or my destination is my sql server database so i'm going to scroll down to the very bottom the sql server native client 11 and set the server name server name is dot in my case or localhost and authentication i'm going to be using windows authentication it's exactly the same way as you've got if you were connecting to your server and then which database are you going to connect to well i'm using the avengerworks 2014 database so i can write a query to specify the data transfer or can just transfer the lot so i'm going to transfer the lot copy data from one or more tables in all views and you can see that it's giving me the table name based on the spreadsheet name and then a dollar sign so you might also see any tables that you set up in your excel workbook so i'm going to select sheet 1 and it's giving me a destination of dbr dot sheet one dollar i'm going to leave it to that now if i click on edit mappings so this gives me a similar type of dialog box that we've seen before it's going to tell me what exactly is going to use as the data type again it's gone overboard but even more so this time so for instance it's using a flaws as opposed to an int it's using nvr char 255 and it's saying that all of them are nullable however what i do usually is i accept all of these basic types i import the data and then i'll refine it from inside sql server so let's click ok let's click next so i can run immediately i can also save it as an ssis package as i said this is a cut down version of ssis so let's finish and here you can see it's going through all of the steps and once it's done click ok now i do find that there are often problems with doing it this way and so then i really do have to investigate what's going on my general rule is if i'm importing data i don't import it into an existing table that usually causes a lot of problems i import it into a new table and then i can import it from one table to another use the select star into command so let's have a look at this new table so again i'm going to have to press refresh it may not be there there it is instantly so i've got it now so here it is and then i can do other things like for instance i can alter this table and alter my particular columns or employee number to make it an int for instance so just need to add the word column to the front also i'm going to press ctrl shift and r just to allow for all these squiggly lines to disappear click execute there it is click refresh on the columns we now have int and then go through each one of these in turn so i hope you enjoyed this video there are other ways of importing data into sql server like using the bcp utility but this is probably one of the easiest ways just right and click and go to tasks and import flat file or import data to import it into a particular database and this particular video incidentally was a suggestion of that was put on a comment of a previous video that i've got so if you've got any suggestions for future videos please put them in the comments to this particular video if you did like it why not click the like button and then click on the subscribe and click the bell next to it that way you'll be informed of any new videos well thank you very much for watching this and i'll see you in the next video keep [Music] learning you

Channel: SQL Server 101

Views: 7,095

Keywords: sql server, sql, microsoft sql server, sql server learn, sql server videos, t-sql, tsql, import sql, import sql into excel, sql server into excel, excel into sql, excel data into sql, load excel into sql, import excel into sql server, add excel into sql, load excel workbook into sql, load excel spreadsheet into sql

Id: JVP9frj2VSQ

Length: 9min 28sec (568 seconds)

Published: Wed Apr 28 2021