Importing text files | CSE 148 – Excel Exam 1

Still feeling nervous for your exam? Check out the Excel Cram Kit (Exam 1) for practice modules that emulate the difficulty that you'll face on your exam. Each module includes a thorough step-by-step explanation where I walk you through the entire problem solving process!

Importing text files explained

Say you're planning a vacation with some friends for your senior year, and are leading the charge planning this trip of a lifetime.

You found a great vacation site to do some research. It's a little sketch, but is giving you some great information, tips, and pricing information. They offer the ability to download their vacation info, only hitch is... the file type for download was a text file.

Before diving into this example, let's understand why someone may transfer Excel information via a text file instead of a massive Excel sheet.

Sometimes, people work with massive, 1000+ row Excel files that take up too much space. Transferring these databases as a text file(s) reduces file size.

When dealing with text files, the primary thing you need to take note of is:

Determine whether or not the values in the text file are separated by tabs, colons, semicolons, spaces, commas, or other characters. This is referred to as the delimiter of the text file.

Oftentimes, you'll deal with values being separated by tabs or commas, but it's important to be ready for anything.

How to import a text file

To import a text file, we just need to open our blank sheet, navigate to the "Data" tab in the ribbon, click "Get External Data", then select "From Text".

Once we select our text file, in this case "vacations.txt", we're then prompted with a box to determine whether we're dealing with a "Delimited" file or a "Fixed width". Our text file is separated with commas, so it's "Delimited".

If we were dealing with a fixed width text file, it'd look more like this:

Notice how each value is separated by an even amount of space? That's what fixed width means...

A fixed width text file has all fields aligned with spaces between each field values.

After we click "Next" with "Delimited" selected, we're prompted with a new page asking us to specify our delimiter.

In our case, our values are separated with a comma, so we'll select that option.

Say we had the following text file...

...where each value is separated with not 1, but 2 commas. In this case, since we'd have consecutive commas signifying a separation of values, we'd click the checkbox.

In short, the consecutive delimiters means that our field values are separated by multiple of the same character.

Next, we must specify what the data type is for each of the columns. In our case, the "Destination" and "Clout" columns are text...

...and the "Distance" and "Price" columns are number values. Since these are already specified as "General", we can go ahead and click "Finish".

Last step is to select where we want to import our text data, and in this case we'll just select $A$1 in the existing sheet.

Perfect! We've successfully imported our text file!

Practice problem

Given the groceries.txt file in your downloadable materials, import it successfully into your sheet.

Check out the GIF below for the process!

You may have been wondering, why is everything surrounded in quotation marks? All that does is signify to Excel that the value is a string. You don't need to do anything to remove the quotation marks from your output, they'll automatically be removed.

Excel (Exam 1) Follow-along Guide

It's no secret you retain info better when you write it down. That's why I created the Excel (Exam 1) Follow-along Guide for you!

As you come upon key concepts highlighted in yellow, like this sentence here, you can fill-in-the-blanks on your Follow-along Guide so that you remember all the important stuff for later!

You can obtain the Excel (Exam 1) Follow-along Guide for FREE by entering your email below!

Congratulations! You've gone through all the core concepts that will be on your first Excel exam! Now what?

Practice, practice, practice.

I cannot emphasize enough how much more important it is to practice Excel rather than study it. Without practice, it's difficult to truly understand what you're coding when it comes time for your exam.

In the Excel Cram Kit (Exam 1), I've compiled practice questions that emulate the difficulty that you'll face on your exam. Each question includes a thorough step-by-step explanation where I walk you through the entire problem solving process. That way, you never feel lost, and are prepared to solve any exam problems thrown your way! Click below to get started!

Free Resources

Each exam concept broken down in simple, real-world examples!

ToolsExcel (Exam 1) Follow-along Guide
LessonYEAR, MONTH, DAY
LessonSUM & AutoSUM
LessonRelative vs. Absolute Cell References
LessonRANK
LessonCOUNT
LessonROUND
LessonWEEKDAY vs. WORKDAY
LessonVLOOKUP
LessonPMT
LessonIF
LessonPercent change
LessonExcel charts
LessonImporting text files

Practice Problems

Step-by-step walkthrough for each of the questions you need to be ready for!

"I bought this Cram Kit the night before my exam, and it helped me tremendously. It provides step-by-step explanations for all the units covered in class."

Matt Lamanna
Oct. 24, 2021

Rating: 5 out of 5.

Leave a Comment