Load data in strange formats
Introduction
You’ve received some data from a colleague or vendor that’s in a format you’re not familiar with, for example:
- Tab-delimited text
- Text with other delimiters
- Fixed width text
This guide will show you a couple quick tricks to get this data loaded so you can use it.
Tab-delimited text
Opening these files is generally pretty easy in Excel. The easiest way is simply to open a blank Excel document and drag/drop the file from your computer into that Excel window:
Tip
Try it out for yourself with this sample file.
If for some reason this method doesn’t work, don’t worry - you can still load the data the old-fashioned way like you would text with any other delimiter as we will cover in the next section.
Text with other delimiters
If you’ve got a stubborn tab-delimited file or a file that contains other delimiters like pipe (|), this section is for you. Excel has a helpful tool for importing this data. Open a blank Excel document and on the ribbon, go to the Data tab and select “From Text/CSV.” Excel will ask you to select the file you want to load, and then will open a helpful wizard.
What’s a delimiter?
A delimiter is a character that separates pieces of information in your file, telling Excel where one column ends and the next one begins. For example, in a CSV file, the comma (,) is the delimiter. A single row of a CSV file might look like:
data for column 1,data for column 2,data for column 3
Loading this CSV with , as the delimiter will result in an Excel document with one row and three columns. The commas tell Excel where each column ends.
We’re going to load this sample file that contains pipe-delimited text, but the process is the same for any delimiter:
Notice that Excel automaticaly detected the delimiter, | in our case, and provided a sample of what the data will look like once it’s loaded. If you have a different delimiter, or if Excel for some reason doesn’t correctly detect your delimiter, you can tell it which one to use by selecting the right delimiter from the drop-down or choosing “Custom” and typing in your delimiter.
Once you’ve finished, Excel loads your table directly into a table. There are other load options beyond the scope of this guide, but feel free to experiment with the sample file.
Fixed width text
Tip
Follow along with this sample layout file and sample fixed width file.
Fixed width files are structured such that each row in the data contains the same number of characters. Rather than having the fields delimited by special characters, each field is a set number of characters in length, padded by spaces if necessary. Loading fixed width files requires a layout file that describes how many characters are in each line and which positions of each line correspond to the various fields.
In our example, we’l be using this layout file:
This file is telling us that our fixed with file has two fields in it: Email Address and MRC Amount. Email Address comes first, starting with the first character in each line and being 30 characters long. After that comes MRC Amount which starts on the 31st character of each line and is 8 characters long. Here’s what our fixed width file looks like:
We’re going to load this file using the same “From Text/CSV” wizard we would use for delimited text. Odds are good that Excel will automatically detect your columns but if the file is very large or the data is very oddly structured, you will need to manually enter the starting positions for each field. All we will need is the starting postion for each field (1 and 31 in our case).
Warning
Excel considers the start of each line to be character 0 while most layout files (including our layout file) start at character 1. This means you will need to subtract 1 from each of the starting field positions in the layout file when entering them into the wizard.
Here’s how we load the data:
We choose our file, select “–Fixed Width–” as the delimiter, and then enter the starting position of each field separated by commas. Excel shows us a trial parse of the data and then loads it into a table.