Pinterest Stumbleupon Whatsapp
Advertisement

You don’t want to be the guy in the office who has to type everything into Microsoft Excel time and time again.

So, there will come a time when you must deal with all sorts of information stored in other kinds of files and bring it into 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:

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

A Simple CSV File

In this screenshot of a simple text file, the names and addresses are separated by commas. This is a simple comma delimited text file. It’s easy to create in any text editor.

Advertisement

But on its own, a whole series of such data wouldn’t be so useful in a text file if you had to prepare a list or a report. Bringing it into an Excel spreadsheet will help you make a more professional document and do more advanced operations on the information. For instance, you can easily look for duplicate addresses and remove them. Then, you can use the spreadsheet to create labels and mail merge How to Print Labels with Mail Merge in Microsoft Word and Excel How to Print Labels with Mail Merge in Microsoft Word and Excel Are you still using copy-and-paste to create labels, name badges, or other personalized mass communications? Mail Merge, a simple Microsoft Office automation tool, will let you print your labels in seconds. Read More .

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 delimited text file in more detail…

The 3 Different Kinds of Delimited Text Files

Delimited text files are designed for export of data from one program into another. There are three common kinds of delimited files per the way you separate (delimit) each value in the plain text file. 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.

  1. Comma separated values.
  2. Tab separated values.
  3. Colon separated values.

Just as it is named, 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”. 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 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, 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 How to Convert Any File Format Online with Free Tools How to Convert Any File Format Online with Free Tools If you need to convert a file, here's the ultimate list of sites you should turn to. Read More that can take a raw CSV text file and spit out a XLS spreadsheet. Zamzar and Convertio float to the top on the search page.

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 below shows a jumble of comma separated values in a Notepad file.

CSV File

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 Automatically Format Data in Excel Spreadsheets With Conditional Formatting Automatically Format Data in Excel Spreadsheets With Conditional Formatting Excel's conditional formatting feature lets you format individual cells in an Excel spreadsheet based on their value. We show you how to use this for various everyday tasks. Read More  or prepare it for printing.

Imported Text

There are two ways to bring data into an Excel 2016 spreadsheet from a CSV file. Start with the easy one first.

1. The Automatic Import

Click the File tab, then click Open.

Select the CSV file you want to open. Excel opens the text file automatically and displays the data in a new workbook.

Open a CSV File in Excel

This is the most direct (and quickest) route to open a CSV file. 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.

2. 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 Excel and browse to a text file. The three-step process helps you control the format of the data you want to import.

Step 1

This is what the Text Import Wizard looks like in the first step.

Excel - Text Import Wizard

But as you saw earlier, the text import wizard does not start automatically for the CSV files (i.e. a file with a .CSV extension). Open the Text Import Wizard manually and control the format of the rows and columns. To start the Text Import Wizard…

Go to the Data tab on the Ribbon. In the Get External Data group, select From Text. In the Import Text File dialog box, select the CSV text file you want to import. The Text Import Wizard is displayed with the options you can now configure.

Import Data

The wizard will take you through three screens and help fine-tune the data you are trying to bring into the spreadsheet.

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.

Excel - Text Import Wizard

Select the Treat consecutive delimiters as one check box 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.

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.

Fixed Width Data

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, Excel imports data in the General format. Select the column in the preview window and set the appropriate format.

Excel - Text Import Wizard

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 thousands 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.

import-data-dialog

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.

Just Three Easy Steps of Excel Wizardry

Some practiced mastery of Microsoft Excel Need to Learn Excel? 10 Experts Will Teach You for Free! Need to Learn Excel? 10 Experts Will Teach You for Free! Learning how to use Excel's more advanced features can be tough. To make it a little easier, we have tracked down the best Excel gurus who can help you master Microsoft Excel. Read More can help you deal with large chunks of data. You can import or export up to 1,048,576 rows and 16,384 columns. Don’t let big data scare you. If the fear of spreadsheets still gets to you, look at these tips to learn Excel 8 Tips for How to Learn Excel Quickly 8 Tips for How to Learn Excel Quickly Not as comfortable with Excel as you would like? Start with simple tips for adding formulas and managing data. Follow this guide, and you'll be up to speed in no time. Read More quickly.

So, what kind of problems do you face while exporting delimited text files? Let us troubleshoot it for each other in the comments.

Originally written by Saikat Basu on November 8, 2009.

  1. Mark Knight
    January 23, 2017 at 4:28 pm

    Saikat

    I have several files of csv data, is there a way to bring them in automatically to append to each other? So file one has 8 lines and file two has 12 lines that I want added to the bottom of file ones 8 lines of data to make 1 file with 20 lines. I might have up to 60 files, is it possible to create a macro to bring in all of the files and concatenate them as one?

    Thanks,

  2. EMMA
    December 1, 2016 at 4:18 pm

    BLESS YOU THANK GOD!

  3. Vanya
    September 20, 2016 at 8:50 pm

    Thanks alot, this helped much

  4. Jody
    June 19, 2016 at 3:10 pm

    Thank you so much. This really helped. I have a column with values which I think are just end tags. No worries since I won't be using them. Thank you very much!

  5. umesh
    February 18, 2016 at 7:00 am

    i have sheet contain address , when i am converting in excel and using comma then the address separated in diff collum..help me

    • Saikat Basu
      February 18, 2016 at 2:42 pm

      Depends on what you want to achieve Umesh. The "comma" is being taken as a column delimitator.

  6. Doucheman Macgee
    March 3, 2015 at 12:01 am

    You are a lifesaver. Thank you.

  7. Stu
    January 14, 2010 at 11:30 am

    Saikat,

    Periodically, I have to open several delimited .txt files in Excel. They all have the same columns. Some of the columns contain codes with leading zeroes. If I leave the column data type as the default, General, the leading zeroes are dropped.

    I have to go through your step 6 when opening each file and set the data type to Text.

    Is there a template or something in which I can pre-define each column's data type and avoid all that manual effort?

  8. FunDreamz
    November 9, 2009 at 7:59 am

    @Chris
    You have suggested the best and the shortest route. Rename file to .csv, open with excel and then save as a normal excel file. Your original .csv file remains intact for future use, if required and you have brand-new spreadsheet with data already converted.

  9. Chris
    November 8, 2009 at 4:16 pm

    If the file is comma delimited, all one needs to do is change the suffix to .csv and then excel or just about any decent spreadsheet will open it automatically.

Leave a Reply

Your email address will not be published. Required fields are marked *