Importing delimited files in Excel

Posted on December 30, 2008 at 9:02 am

Sometimes we receive plain text files from colleagues that have data separated by a comma or tab (or could be any character or characters). We call these separators delimiters. Plain delimited files are hard to analyze and sum up if we don’t use a spreadsheet program like Microsoft Excel. The following are simple steps you can follow to quickly import your delimited files into an Excel spreadsheet.

Step 1: Open up Excel and copy-paste the content of the delimited file into the spreadsheet:

In the example above we have a file delimited by semicolons. Upon copy and paste, this showed up as a couple of rows in column A. At this point you could see that the whole thing occupies only Column A so we still need to break up this data into columns.

Step 2: Click Column A’s header to select the whole column.

Step 3: Click the Data menu then Text to Columns

Step 4: The Convert Text to Columns Wizard will appear. Choose the Delimited file type then click Next.

Step 5: Choose the delimiting character, in this case semicolon. You should see a data preview if you do this correctly. If the file you have is delimited by some other character, choose Other then enter the character on the box after the Other option.

Step 6: Set the data type if needed. Click Finish to exit the wizard. I usually don’t touch this step anymore as we can change data formats on the spreadsheet it self if needed.

That’s it! You should see a delimited spreadsheet occupying the other columns like below:

The delimiter character disappeared and Excel automatically treated our columns with the applicable data type. On the example we can see that the first column are names in plain text string so the alignment was defaulted to left while the other columns are identified as numbers so they were aligned to the right.

By the way, traditionally the comma delimited files are saved as csv files so Excel has a capability to open these files. Just click File > Open then choose Text Files.

Once you have converted the data to columns you can apply the aggregation techniques discussed on this post to summarize your data. With the steps mentioned above, you will not have a problem crunching those text delimited files in no time!

Ben Carigtan shows you how it’s done!

» Filed Under Cool Websites

Comments

One Response to “Importing delimited files in Excel”

onebir said on : January 4th, 2009 at 10:11 am

You can skip straight to step 4 – just use File->open to open the file (you’ll need to set “Files of type” to “All files” to see it). The a dialog box very like the one in step 4 will appear right away.