Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
If the data that you want to import is in comma separated values or indeed in anything that's just a regular text delimited file-- in fact here I've got to some very straightforward values, very similar to the last time, but they are separated with the vertical bar. I can also bring this into SQL Server although the way of doing it does have quite a few different options than doing it with Excel. So I'm going to go ahead and open up my Import and Export Data Wizard. I'm going to use the 32-bit one.
The source I'm going to pick here is Flat File Source. And when I select that, not surprisingly it's going to ask me to browse to that file, which is currently saved as a CSV on my desktop. And once I do that we have a very different screen for determining what that data is. If I click on the Columns section on the left, it will take a look and think yes, this column delimiter seems to be a vertical bar, and if you select that, this is what the date is going to look like and it looks pretty good. Although it does seem to say that the first name, last name, address, and city are values. Now it's not the case. I actually did one of those to be the column names.
If I look back on my general section I'll see that I do have a checkbox. The column names are in the first data row. So we click back to the Columns, and yes we seem to have that now sorted correctly. Interestingly, if I go to the Advanced section, it will tell me the way it's interpreting these values. So it's breaking down all the different columns and saying FirstName it thinks is a string that's 50, and LastName as a string that's 50, and Address as a string that's 50. And in fact you'll see this a lot. It's pretty much going to assume that everything as a string of 50, even a zip, and email, and phone, and that's okay, but it might lead us into issues.
For example, I'm pretty sure I have some e-mail addresses that are longer than 50 characters. There is a button-down here called Suggest Types. Now what that will do is look through some of this semi-mapped data and make a prediction that if something is purely numeric, well maybe it should be an integer. I'm not a big fan of using Suggest Types, because I tend to find that it gets a bit too pessimistic. So for example, if I were to click Suggest Types here, it might go through all the last names and if the largest one that it finds is 12 characters, it's going to assume that the widest this column needs to be is 12 characters.
That may be true, but that may lead you into issues later on. So I'm going to leave that alone. If I click Next, we then say where are we copying this data to. And I'll get a chance to map it again in just a minute, but I'm copying it to my current SQL Server database, which I'm going to represent with just the dot, the period. I'm using Windows Authentication and that should give me the drop-down list where I can select my new custom database. Click Next. It's telling me it's going to import a table.
That's importing from the CSV data and it's going to create a table called csvdata. I could choose to change that if I wanted, but let me just leave that right now. Once again, I'm going to click Edit Mappings. Now we'll see here that everything is now being listed as a varchar with a maximum size of 50. That may work. That may not. In fact I'm pretty sure that certain things like my address should be at least a hundred and that my e-mail should be at least a hundred as well.
I'm going to click OK, click Next, tell it that yes I want to run immediately. click Finish and go. And we see that we've got an error here, we've got some messages popping-up hat's saying, unfortunately the output column Email failed because truncation occurred. And the truncation row disposition on the output column specifies failure on truncation. What does this mean? Again it's to do with how we're specifying this copy, this import, to work.
I'm going to click Back quite a bit here and go back to where it thinks these mappings occur, and in fact the problem is probably on Email. Wll that's were it said the problem was. Right now it does think that it only needs 50 characters to do its mapping. I'm going to say no, you need a hundred characters or truncation will occur. I'm going to click Next and go ahead keeping the same data. What I've got an issue with now is with Mappings. So we'll take a quick scan of those, now it did say address was different, but it didn't complain about address so I'll just leave that.
Click Finish and try this again, and that looks a lot better. We now have a warning here. The total number of rows processed is 101. There is a partial row at the end of the file. That's quite common message to get. You can check that later, but we do seem to have a hundred rows transferred. So I'm going to close that, open up Management Studio, and just double-check that everything looks okay. And it does seem to have brought everything in. We do have the columns named correctly.
We do have a hundred rows. It does not seem to have created anything in that last one, so we're looking good. Once again of course we could change the definitions of that. But if you know that you are immediately needing to go in and change this information, you should try and do a lot of it as you are mapping it before you actually create the table during the import. But that's the process of bringing in a CSV file.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 86740 Viewers
80 Video lessons · 136018 Viewers
59 Video lessons · 54744 Viewers
52 Video lessons · 68640 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.