Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
So I'm going to go ahead and take a straightforward Excel spreadsheet and import that into SQL Server. This is the spreadsheet that I have at the moment. It's a list of names, addresses, and zips and email, just some contact information. I can see that the first row does seem to contain the column names for this, so that might be useful in just a moment. But that's what I want. I just want it in my database, because it's way too easy to mess it up when it's an Excel. So I'm going to open up the SQL Server 2008 R2, Import and Export Data.
I'll open up the 32-bit one, so it should be all I would need here. I'm going to click Next. Select that my data source is Microsoft Excel. It's going to ask me for the file path, which is just out here on my desktop. And it's going to ask me for the Excel version and I think it's 97-2003. If there is a problem with that it will probably tell me in just a moment. And I'll leave this checked. Yes the first row has column names. I'll click next. It's asking me now where is it going to. Well, this really means the current SQL Server, SQL Server Native Client 10.0.
But I could be copying to anywhere. Again, all these management tools don't have to be running on the local machine of a SQL Server instance, so it's just asking for the server name. Well, I'm just going to put in the period here. I could type in the name of my machine as well. That would work. If I click the drop-down box it will try and see if there's any more that it knows about and there won't be right now. So that's just my local machine. It doesn't matter which one I use. I'm going to say yes, I'm talking to it Using Windows Authentication.
And because I've named that if I click the drop-down box now I should see a list all of the databases there, and yes I do. I'm going to select my TwoTreesTest custom database that I have. I would even have the option to make a new database at this time. Now I'm going to do that. I'm going to click Next and it's going to ask what we are copying. Now this is a little bit over-complex for what I'm doing right now, because it's saying are we copying data from one or more tables or views? And this is a bit more useful when say you're coping from an Access database and you only want to copy certain pieces of it.
I'm going to leave the default selected and click Next and it's going to tell me this is the stuff that it knows about. Apparently, it's got a source of data called exceldata$. And it will create something called dbo.exceldata$. Not quite what I'm after, but I can always change that later. The interesting button here is going to be this one down at the bottom, Edit Mappings. That's going to tell me what it's going to try and do with the columns in the Excel data source and what it's going to map it to.
Now right now, they're named quite well. We've got the FirstName, LastName, Address, City, State, and so on. I'll keep those names and it will even tell me here what it's going to do with them, what data types it's going to use when it creates this table. And mostly they're listed as nvarchar, so variable-length Unicode fields that are nullable and is this generic size of 255. That's not quite what I was looking for and this one definitely looks wrong. Yes my Zip is numeric, but it's certainly not a float.
That's an estimate based on the data that existed in that spreadsheet. So I could change that. And again, depending on what I'm doing, whether the postal codes I know might have letters in them or they might have dashes, even if I'm using US postal codes. So I could change that to an nvarchar as well. But I see that what it did there was actually change it to max, which is not what I really want. It doesn't need 2 gigabytes of space to go and in fact, 20 would probably do it just fine. You will occasionally find that it does this, where it doesn't accept something that you're typing into.
And there is a couple of different ways as you could do it. So for example, I've changed the data type and suddenly seemed to take my 20. So I'm going to now change it back. It just seems to be a little of strange behavior I've seen a couple of times. And it's up to you whether you want to change them to be Nullable or not. I'm going to leave this as is. Click OK. Then I'm going to click Preview, which just shows me, yes this is the data that it's going to try and fetch from Excel. It all seems to be mapped correctly. I'll click OK. Click Next.
It's giving me a little prompt here that it's kind of letting me know that it thinks that zip was a double, and I'm wanting to store in an nvarchar. So is there a conversion process going on? And yes there is. Well, what if there's an error, what do I want to do? Do I want to do something special, do I want to fail the whole thing or just ignore it and move on? If the data was going to be truncated, so if say I decided to move it into a varchar with a maximum length of two digits, we'd have a lot of truncation going on.
I'm just going to use the Global Settings, which are these ones down here. Any error makes it fail and any truncation makes it fail. I'll click Next, and I'll tell it yes, Run immediately. The other option would be to save this as an SSIS Package, which is SQL Server Integration Services. Again, we'll talk about more of that later. But just to let you know, even a simple import that you're doing is using integration services. I'm going to say Run immediately. Click Next. It gives me a summary and click Finish.
It runs through a whole bunch of stuff. We even get a message here that 101 rows were transferred. I'm going to close this, go back into SQL Server Management Studio, connect to the database, and not surprisingly we should find now in our tables this rather ugly looking dbo.exceldata$, but I could open it up and say Select Top 1000 Rows. And we're bringing back all our content. I could right-click the table right now, and just rename it to something more specific.
Let's imagine these were coming from customers, so I called it the Customer table. And of course, I can even right-click and hit Design, jump into this, play around with the data types, even add a primary key, perhaps even generate one. This is the basic procedure of how you would import from an Excel spreadsheet.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64627 Viewers
80 Video lessons · 124266 Viewers
52 Video lessons · 60223 Viewers
59 Video lessons · 46053 Viewers