There will come a time when you must deal with all sorts of information stored in other kinds of files and bring it into Microsoft Excel. You cannot run away from an ever-present text file. I bet you find some of them every day.
Here are a few everyday examples:
- An Excel analysis of sales or product information stored in a text file.
- Exchange of data between two different software (maybe, from a database to a spreadsheet).
- Names, addresses, and email ID stored in an email program (e.g. export from Microsoft Outlook to Excel).
Microsoft Excel gives you all the tools to connect to external sources of data. Let’s talk about delimited text files.
This Is a Delimited Text File
As you can see, the first and last names, the companies they work for and other details are separated by commas. This comma delimited text file is easy to create in any text editor.
Right now, it isn’t useful. Bring this into a spreadsheet and you can make a more professional document.
For instance, you can easily look for duplicate data and remove them. Then, you can use the spreadsheet to create labels and mail merge if addresses are also part of the data.
The basic idea here is to import the information from a text file and split up your various pieces of information into separate columns and name each column with an appropriate header.
Let’s look at a delimited text file in more detail…
The 3 Different Kinds of Delimited Text Files
There are three common kinds of delimited files depending on the way you separate (delimit) each value. Any character can be used to separate the individual entries in a file.
For instance: the pipe (|) or a simple space. You will find these three to be the most common kinds of delimited separators between each text entry.
- Comma separated values.
- Tab separated values.
- Colon separated values.
The Text Delimiter keeps each value separate from the next. Any value that follows the delimiter and precedes the next occurrence of the delimiter is imported as one value. Do remember that the value between the assigned delimiter can have another delimiter character, but it needs a quotation mark (“) or an apostrophe (‘).
Confusing? Not so much. Let’s see how with an example:
In a text file with city and state names, there may be some values like “Albany, NY”.
Microsoft Excel can read the comma (,) in between the two words as a delimiter. To treat the city and country names as one value and import them into one Excel cell we have to use double quotes or an apostrophe as a text qualifier. If no character is specified as the text qualifier, “Albany, NY” is imported into two adjacent cells as Albany and NY.
In brief, to retain any value in a column exactly as it is, you can enclose the value in quotation marks or an apostrophe.
As we will see below, Microsoft Excel gives you complete control over the import process and a Preview pane to see the format of the data before it fills the cells.
Convert From a Delimited Text File to a Spreadsheet
There are many online converters that can take a raw CSV text file and spit out an XLS spreadsheet. Zamzar and Convertio are two excellent tools.
But you don’t need to hunt for an online converter because Microsoft Excel has a native feature that does the job much better.
Let’s take a sample CSV file and walk through the steps to convert delimited text files to spreadsheets. The screenshot above of a jumble of comma-separated values in a Notepad file is a good example.
Microsoft Excel can help turn this confused jumble into neat rows and columns. You can then go to work on it and turn it into a beautifully formatted report or prepare it for printing.
There are three ways to bring data into an Excel spreadsheet from a CSV file. Start with the easy one first.
Method 1: The Automatic Import
1. Click the File tab, then click Open.
2. Select the CSV file you want to open. Microsoft Excel opens the text file automatically and displays the data in a new workbook.
This is the most direct (and quickest) route to open a CSV file. Microsoft Excel uses the default data format settings to read and import each column of data. But the automatic import does not give you the flexibility you want.
So, let’s see the second way which uses a wizard.
Method 2: Restore the Text Import Wizard
The Text Import Wizard enables you to control the structure of the data you want to import. It starts automatically when you import text files (i.e. a file with a TXT extension).
Open Microsoft Excel and browse to a text file (or change the extension of the CSV file to TXT).
Microsoft hid the old Text Import Wizard in Excel 365 and 2016 (version 1704 onwards). But you can bring back the text import wizard from Excel’s Options.
1. Go to File > Options > Data.
2. Scroll down to the Show legacy data import wizards section.
3. For importing text or CSV-files, select From Text (Legacy). Click OK to close the Options.
4. Now, you can use the wizard from the Ribbon. Go to Data > Get Data > Legacy Wizards > From Text (Legacy). Browse to and open the CSV file you want to import.
Use this three-step process to control the format of the data.
Step 1
This is what the Text Import Wizard looks like in the first step.
Select Delimited — When items in the text file are separated by tabs, colons, semicolons, spaces, or other characters.
Select Fixed Width — When all the items are the same length and neatly structured in space separated columns.
Sometimes, the raw data might have a header row. For instance: ["first_name","last_name","company_name","address","city","county"].
Use Start import at row to select the row from where the import will begin.
The File Origin can be left at its default for most cases.
The Preview displays the values as they will appear when they are delimited into columns on the worksheet.
Click Next.
Step 2
Choose the Delimiters for your file (comma, in our case). For some other character, check Other and enter the character in the little field. The Data preview window gives you a glimpse of the columnar data.
Select the Treat consecutive delimiters as one checkbox if your data contains a delimiter of more than one character between data fields or if your data contains multiple custom delimiters.
For example, this helps you handle files which may have an extra space before or after another delimiter. You can help identify space as another delimiter and tick this box.
Use the Text qualifier dropdown to select the character that encloses values in your text file. We talked earlier how a text qualifier can help you import some values into one cell instead of separate ones.
Use the Data preview window to check the appearance.
Click on Next.
Note: The wizard screen changes when importing fixed-width data.
The Data preview window can help you set column widths. Use the top bar on the window to set a column break represented by a vertical line. Drag a column break to increase or decrease the width. Double-click a column break to remove it.
In a fixed-width file, no delimiters are used to separate the values in the file. Data is organized in rows and columns, with one entry per row. Each column has a fixed width, specified in characters, which determines the maximum amount of data it can hold.
Step 3
The Preview windows become more important in this screen because you can fine-tune the format of data that goes into each field with the Column data format. By default, Microsoft Excel imports data in the General format. Select the column in the preview window and set the appropriate format.
For example, you can select…
- Text for the text fields.
- Date and the date format for any column that contains dates.
- General for converting currencies to the Excel Currency format.
Use the Advanced button to specify the type of decimal and the thousand place separators for numeric data.
For instance, if you want to display 100,000 as 1,00,000. Excel displays the numbers as per the format set in your computer’s regional settings.
Click Finish. A final Import Data dialog box pops up.
Don’t worry about it too much now. It gives you a few options to insert the data in the spreadsheet or create a connection with an external database. Inserting the text-delimited values as a table in the present worksheet is the default setting.
The “old” legacy method is still the best way to go about it when your CSV file is simple. If not, there is a new method now which can fit the bill for any text import needs.
Method 3: Use Get & Transform Data
The Data tab includes all the tools you will need to gather external data and make it behave the way you want it too. For instance, you can create a PivotTable report and refresh it whenever the external data changes.
It is an overkill for simple CSV files, but let’s go ahead and see how to bring in columns of delimited data into Excel.
1. Open a blank workbook.
2. Go to the Data tab on the Ribbon. Then click the tiny dropdown arrow under the Get Data button (in the Get & Transform Data group). Choose From File > From Text/CSV.
3. In the Import Text File dialog box, browse to the location on your desktop and select the CSV text file you want to import. The Text Import Wizard is neatly displayed with the options you can now select.
As you can see, you can change the character encoding and the choice of the delimited character or enter a custom delimiter.
Excel figures out the delimiter by analyzing the first few hundred rows of the dataset by default. You can change this and let Excel work with the entire dataset too. It’s not recommended if you have millions of records to import.
4. Click the Load button to import your data into a new pretty worksheet.
5. Whenever you import data into Excel, a data connection is created. You can run queries and connect the worksheet to the external source. Any change to the original source data will be automatically updated in the Excel sheet.
6. You can cancel this connection by going to Data > Queries and Connections to open the panel on the side.
Right-click and choose Delete. Or, hover over the file name of the data source and hit Delete in the window that comes up. If you are sure, click on Delete again.
Transforming the data is beyond the scope of this tutorial. So I will direct you to Microsoft’s Getting Started with Get & Transform in Excel support page for more help.
The Many Uses of Delimited Files in Excel
Need to separate a list of first and last names and prepare them for printing? Convert the delimited text file to Excel. You can import or export up to 1,048,576 rows and 16,384 columns. And delimited files are supported almost everywhere. Use Excel as a timesaver with the above techniques and the tips below.
Read the full article: How to Convert Delimited Text Files to Excel Spreadsheets
