Learn how to append data from a text file to data in worksheet. Author Jen McBee demonstrates the process for importing data and points out what to watch for while using the Import Wizard.
- [Instructor] With Excel 2010, you can import data into a worksheet from a wide variety of data sources by creating a connection to an external data source. The different file types that we can import into an Excel workbook are: a text file, files from an access database, and information from another Excel workbook. In this video, we'll explore how to insert data from a text file using the Text Import Wizard. Importing text into an Excel worksheet is probably something that will be covered on the Excel 2010 MOS Exam.
So I want to make sure that you're comfortable using the Text Import Wizard, and are able to quickly import that information if asked to do so. I have the 03_01 exercise file opened, so let's tackle this topic. Here is our 03_01 exercise file and we have a new worksheet called New Products and we already have our column headings on there. I'll go ahead and click in cell A1. That's where I want to insert the information. Now I want to show you the text file that we're gonna be bringing in.
It's always good to look at the file to see is it separated by tabs or commas or another type of delimiter. I'll go ahead and go into my Explore window, go to the desktop, Exercise Files, Chapter 3. Here's our New Products text file that we're going to be importing in. I'll double click to open it up. Taking a look at it, it looks like everything is separated by tabs. We're ready to close this and start the import process. Let's go back to our Excel file.
We'll go to our Data tab, in the Get External Data group. Notice this is where we can choose from access, from the web, here's from text. Go ahead and click on that. Brought me right to Chapter 3, here's my New Products text file. I'll go ahead and select it and click Import. Now we looked at the file ahead of time so we know it's delimited, it's separated with tabs. So we'll leave that radio button selected and click Next. In step #2, this is where you can choose the delimiter.
Tab is going to be the default delimiter. That's what our information is separated with, but it could also be a semicolon, a comma, or a space. Let's go ahead and click Next. The third step is where you can go column by column, and format the data in the column. I like to wait until I bring it into the workbook and then format it, so I'm just gonna go ahead and click Finish. This window is asking where do we want the data imported. We had already clicked in cell A2. You can see the mark key going around the cell.
But notice that we could change the location now in this Import Data window, or we could put it on a brand new worksheet. I'm gonna leave it just as it is and click OK. Look, there's our data that quickly. Now keep in mind that when you import data using the Import Wizard, you actually create a live connection between your external data source and the information in Excel. On the Data tab, I can go in and check the connection, using the Connection command button. And you can see here's our New Products document.
What this means is anytime the New Products text file is updated, it will automatically update the Excel workbook the next time we open the workbook. If we want to remove this connection, we can select the connection in the connection window and click Remove. I'm gonna go ahead and close the window. So when you're setting up an import for the very first time, it may seem like more than just a few steps but you have to remember that the benefit to connecting to an external data source is that you can analyze the data in Excel without repeatedly copying the information into Excel.
Once you've connected to an external data source, you saw how you could automatically update the information in Excel using the Refresh button. Make sure you're familiar with the steps to import data from a text file because, chances are, you'll come across this on your Excel 2010 MOS Exam.
Explore the MOS certification program, its cost, and its format and walk through each Excel certification objective in detail. Get hands-on experience with free downloadable practice files and take the challenges to test your skills. The course wraps with a full-length, 50-minute practice exam.
- Preparing for the exam
- Saving Excel files in different formats
- Printing workbooks and worksheets
- Inserting headers, footers, and document properties
- Inserting, copying, and moving data
- Formatting cells and worksheets
- Creating, deleting, copying, and moving worksheets
- Splitting and arranging windows
- Creating basic and conditional logic formulas
- Creating named ranges to use in formulas
- Applying conditional formatting
- Creating charts
- Inserting and modifying SmartArt
- Filtering and sorting data
- Taking a full-length practice exam