Splash of Style...Macs, Photography, Design, and other Passions

Importing Plain Text Into Excel Spreadsheet

July 5, 2009 by debbie T | Content ManagementMac SoftwareWindows on the Mac

I needed to convert a text document with personal data to an Excel spreadsheet. I wanted to use Neo Office (Open Office) but couldn’t find a way to import text, so I fired up my Windows XP virtual machine, and installed an old copy of Office 2000.

So, here’s the dilemma, the data was pasted into a text file from a web page and it was formatted with line breaks. There were 180 blocks of data like this:

first last
title
address
phone
email

first last
title
address
phone
email

After googling, I found that it’s fairly easy to import text as long as there is some sort of delimiter like tab, comma, semi-colon separating the data.

In excel, choose Data>Get External Data>Import Text File

import text file into excel

Initially, I tried comma, but didn’t work well, since some of the text data already had commas, so I decided to use a semi-colon delimiter.

I typed a semi-colon after each section & painstakenly removed each line break.

first ;last;title;address;phone;email
first ;last;title;address;phone;email
first ;last;title;address;phone;email

I worked in small chunks, importing after a few sections. This was taking forever. I needed a short cut!

Since I was using Text Wrangler, I figured there might be a way to automatically remove the line breaks. Yay! Found it. Text>Remove Line Breaks. Couldn’t be easier!

excel-import-breaks

Okay, now if only there was a way to somehow add a semi-colon after each line. Whoo-hooo, how about this:

Text>Prefix/Suffix Lines

I selected each section, added the suffix of “;” then removed the line breaks.

Text Wrangler cut my work in half!

NOTE: When Text Wrangler removed the line breaks, it added a space in between the items, so that messed up my spreadsheet by adding a space before each item in the cell. Before I imported, I did a “find/replace” and replaced all the ;(space) with ; and that worked!

There are 3 comments

  1. This is possible using a find/replace command. In Adobe In-Design you can select various options like “forced line-break” or “discretionary line-break”. So if I wanted to find all instances of a line-break and replace it with a comma and a space, it would be something like this:

    in the find field: ^n (what in-design refers to as a line-break)
    in the replace field: “, ”

    Now, I don’t know what text editing applications can do the same thing, but I suspect you’ll find this functionality available somewhere.

    Comment by Brian on September 16th, 2009
  2. After a bit more research I found this was possible using regular expressions. I found this thread after a bit of google searching: http://user.services.openoffice.org/en/forum/viewtopic.php?f=7&t=15329

    Using Open Office you should be able to use find/replace commands to get the data formatted in the way you need it.

    Comment by Brian on September 16th, 2009
  3. Wow, thank you so much Brian! This is so helpful.

    It looks like ^$ will do the trick!

    Thank you so much for posting!

    Comment by debbie T on September 16th, 2009