From the course: Cert Prep: Excel Associate - Microsoft Office Specialist for Office 2019
Import TXT or CSV data into a new workbook
From the course: Cert Prep: Excel Associate - Microsoft Office Specialist for Office 2019
Import TXT or CSV data into a new workbook
- Did you know that just by simply opening a text file you can create a new workbook and import the data at the same time? After watching this video, you'll know how to import both a text tab delimited file and a CSV file into a brand new workbook. First, I want to take a look at the text file we will be importing in. Here is our employee information. The data is separated by tabs, so any place that you see a white gap in between the words there's a tab in there. Excel will go through the document and insert a new column any time it sees a tab separating the data. Let's close this file and go into Excel. We'll start by going to File and Open. And browse to the desktop where I have my file saved. Go into Chapter 2. Right now we're just seeing the Excel files in the Chapter 2 folder. But if I change the data type to all files that is when we can see the other text files in here. Here is our Employees tab delimited file. I'll select it, click Open and let's start the process. This is our text import wizard. And just in three simple steps we'll be able to create a new workbook and import the data. Now this is a tab delimited file. Delimited files can be separated with either a comma or a tab. The other option we have is a fixed width, where each of the columns have the same amount of space between the fields. We'll start importing at row one. And looking at the first row of data in our preview, row one does contain our column headings. So I'll make sure and click "My data has headers." Let's go to Next. In the second step, this is where we make sure that the delimiter that Excel chose is correct. You'll notice that Tab is selected and if we look at the preview window below, it looks like the information is being separated perfectly into columns. If I change it to Comma and remove Tab, you'll see that it's not separating the information at all because we don't have commas separated values, we have tab separating the values. I'll change it back to Tab and click Next. In step three, we can go column by column and change the data type before we even import the information. For this demonstration, I'm just going to go ahead and accept all of the default data formats and click Finish. And that quickly, our new workbook was created. Notice the title of the workbook. Excel pulled in the name of the text file that we're using and named our workbook, "Employees-Tab." We also have a brand new worksheet called Employees-Tab. Which is also taken from the name of our text file. So that's how easy it is to create a new workbook from either a text tab delimited file or a comma separated value file.
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Contents
-
-
-
-
Import TXT or CSV data into a new workbook3m 22s
-
(Locked)
Import TXT or CSV data into an existing workbook3m 7s
-
(Locked)
Search for data within a workbook2m 36s
-
(Locked)
Navigate data with named ranges and hyperlinks5m 27s
-
(Locked)
Format worksheets and workbooks5m 1s
-
(Locked)
Customize headers and footers4m 37s
-
(Locked)
Display and modify content in different views5m
-
(Locked)
Modify basic workbook properties2m 33s
-
(Locked)
Customize the Quick Access Toolbar3m 55s
-
(Locked)
Inspect and export workbooks6m 33s
-
(Locked)
Configure print settings4m 15s
-
(Locked)
Challenge: Manage worksheets and workbooks1m 10s
-
(Locked)
Solution: Manage worksheets and workbooks9m 10s
-
-
-
-
-
-
-