Learn how to append data from a text file to data in worksheet. Author Jen McBee will demonstrate the process for importing data and point out what to watch for while using the Import Wizard.
- [Voiceover] With Excel 2013, you can import data into a worksheet from many different file types. Text files are easy to import and append to your data. Notice the MOS icon next to text. That's an indicator that on your Excel 2013 MOS Exam, you're probably gonna be asked to import some text information. We can also import information from a CSV file, and that stands for Comma Separated Values. And lastly, we can append and import information from other Excel files.
In this course, we're gonna focus on how to insert data from a text file using the Text Import Wizard, because that's what you will probably be asked to do on your MOS Exam. I have the 03_01 Exercise File open, so let's go ahead and get started. Here's our Shelley's Spices Sales Report. And scrolling down through the information and focusing on the region, I notice right away that there's no information for the western region. And the reason is, we haven't imported the data yet. Now I just happen to have a text file with the information for the western region for their sales.
Let's go ahead and take a look at that file. It's stored right in your Exercise Files, so if you wanna open it up and look at it with me, please do so. Here's our text file. I see our product. Here's our western region. And then we have our sales information. Notice that there aren't any column headings on this data. It's simply just raw data that we wanna bring in. I'm gonna go back to my Excel worksheet. The first thing I wanna do is click in the cell where I want the data to be imported to.
And that would be A41. And then on the data ribbon in the Get External Data group, I see an option to pull the data From Text. That's perfect. That's what I'm gonna click on. This is how we can bring our information in from a text file. I'll go ahead and choose the 03_01 WestSales document, and that brings us right into our Text Import Wizard. Now this first radio button is called Delimited and this is what I wanna choose because my data is separated with tabs, as we saw earlier.
Sometimes you might see a text file that has commas separating the data, but because ours just had spaces between them, that told me that it was separated with tabs. You also wanna pay attention to this option: My data has headers. When we looked at our text file, I pointed out to you that there were no column headings in there; it's just raw data, so because of that we are going to leave this unchecked. We can go ahead and click Next. Step two is where we let Excel know how our information is separated, how it's delimited.
It is delimited with tabs, so we're gonna leave that selected, but please notice that we could also say it's separated with semicolons, commas, just a space. Or if you have another character that's separating your data you can type that in here. We also see at the bottom of the window a nice data preview. Once we've told Excel what is separating the data, it puts it into a preview window so we can make sure that the information is being separated correctly. Everything looks great, so I'm gonna go ahead and click on Next.
In the third step of the Text Import Wizard, we can go ahead and select each column and make sure that it's formatted correctly before we import the data. I usually wait until I bring the information into Excel, and that way, I can format the information just the same as the data that's above it. So all we need to do is go ahead and click on Finish. In our last Import Data window, it wants to know how we wanna view the data in our workbook. Well, Excel has chosen Table for us, which is great because that's how the information is stored above.
Also, it's chosen to an Existing worksheet in cell A41, and it knew to do that because I had already clicked in cell A41, so Excel already knew that's where I wanted it to go. But look at the option down below. If you want it to go in a brand new worksheet all by itself, you could click that radio button. We're gonna leave it just as it is and go ahead and click OK. Hmm, there is our new data. Now we have information for all four of our regions, and the data is complete.
Now one thing that I wanna point out to you is that when we imported this information, in the background Excel automatically created a connection to our data source. So I wanna show you how that works. I'm gonna go back to our text file, and I'm gonna update some of the information. I'm gonna put the word New in front of Curry Powder. I'll go ahead and save the file. I'm gonna go back to our Excel file, and I'm going to use the Refresh All to refresh all of the information and all the connections that we have for this worksheet.
I'll click Refresh All. It's going to ask me to point it back to that original text file, so I'll double-click on WestSales, and that quickly it is updated the information for the curry powder for the western region. When setting up the import for the first time, it may seem like you're taking a lot of time doing it, but once it's set up and that connection has been created, we can see how easy it is to update the information. Make sure you're familiar with the steps to import data from a text file because, chances are, you're gonna come across this on your Excel 2013 MOS Exam.
Disclaimer: Microsoft does not produce, provide, or endorse this video training course.
The course first explores the MOS certification program, its cost, and its format. Jennifer then walks through all of the Excel certification objectives in detail. She includes free practice files so you can follow along as she demonstrates each skill. There are challenges to test your skills at the end of almost every chapter, and the full-length, 50-minute practice exam at the end of the course will ensure you're ready for the real exam.
- Preparing for the exam
- Understanding the registration process
- Creating new Excel workbooks and worksheets
- Formatting data
- Inserting columns and rows
- Moving data
- Applying conditional formatting
- Creating tables
- Applying formulas and functions
- Summarizing data
- Creating charts
- Taking a full-length practice exam