How can I convert an ordinary text file to a .csv file, and import it to Excel?

Question

I have a group of names and addresses that I would like to import into Outlook.

At the moment I have imported them into Excel, but all names and addresses are in one long entry.

All are already separated by a comma.

How can I get Excel to select each "value" and move it to a separate cell?

Edit: I had already tried taking a text file and saving it as a .csv file. However, all contacts load into a single cell.

I am using Excel 2003.

Thanks.

Solution 1

What you need isn't Excel. Excel and Calc (OO.o) can both open CSV as spreadsheets, but what you want is something to manipulate the text file into a properly-formatted CSV file.

For this you want a good text editor. There are very few text editors that allow you to do multiline search and replace (don't know why, as this is an incredibly useful feature and easy to implement). But a very good free editor that (sorta) has this functionality is Notepad2. It is basically an upgraded version of Windows' default Notepad, but not quite as bulky as Notepad++. You can replace Windows Notepad with Notepad2 or Notepad++, but that's beyond the scope of this answer.

After installing Notepad2:

1. Open up the text file and hit Ctrl + H to bring up the find & replace dialog.
2. Type a comma or comma and space into the Search String box.
3. Now you'll notice that the Replace with: box is only a single-line. That's OK because Notepad2 supports regex right out of the box. So check the box that says Regular expression search.
4. Despite what the above checkbox reads, it's actually Regular expression search AND replace—meaning that you can use regexps in the Replace box as well. So in it, type \n and hit Replace All.
5. Then just save as *.csv and quit. If you open it up in Excel/Calc, it should now be a column of names/e-mails.

Edit:

Actually, re-reading your question, it occurs to me that you may want 2 columns: 1 for email, one for name. If that's the case, then it's a little more difficult to do than just replacing all commas with line-breaks. But fear not! Regexps are here to help once more:

1. Open up the original text file.
2. Pull up the search & replace dialog.
3. Type this into the search box: \([^,]+\),\([^,]+\),
4. Type this into the replace box: \1,\2\n
5. Hit Replace All.
6. Save as *.csv and quit.

You may optionally modify the regexps to put a space after the commas if that is how the text file is formatted or how you want it formatted, e.g.

first last, [email protected], bob builder, [email protected], foo...
versus:
first last,[email protected],bob builder,[email protected],foo...

Edit2:

For the Notepad++ flavor of regexp, you don't need to escape groupings with backslashes, so the corresponding search regexp would be:

([^,]+),([^,]+),

If there are empty email addresses:

([^,]+),([^,]*),     // assuming that the email is the second parameter
([^,]*),([^,]+),     // make the first parameter optional

Edit3:

To deal with completely missing fields, it gets a little messy. This is because neither Notepad2 nor Notepad++ support optional groups or negative lookbehinds. So we need to apply 2 different regexps:

1. This takes all consecutive e-mails and injects an empty field (, ,) between them. Run this (with Replace All) multiple times until you stop getting matches.:

   ([^,]+@[^,]+), ([^,]+@[^,]+),*   // search for
   \1, , \2,                        // replace
   

2. Manually add the extra empty field at beginning or end of document if needed.

3. Depending on whether the e-mail comes first or name comes first, replace either of these:

   ([^,]+), ([^,]*),\s     // email first
   ([^,]*), ([^,]+),\s     // name first
   

   with:

   \1, \2\n
   

And that should do it. The \s above is just a space character. You can either put a space there or write \s in the regexp.

Note: I normally try to avoid asking a person to install a new text/graphics/audio/video editor just to solve a specific problem, but this is one instance where: 1.) it's a very lightweight (~300KB) free program that I know works; 2.) it's incredibly useful and almost anyone who hasn't already installed a notepad replacement like Notepad2/Notepad++ would benefit from installing it. In fact, I install it on every Windows computer I use, and also every Windows workstation at my work.

Solution 2

CSV is nothing more than a fancy way of saying, "plain text separated by commas," hence the name Comma Separated Values.

To convert the file to CSV format change its extension from .txt to .csv.

Excel knows that files with the .csv extension are, well, comma-separated-values, and it will put each value into its own cell.

For instance, I started out with this file:

a,1,bla,10
2,r,sasdfa,1as0

I saved it as bla.csv and opened it in Excel. This is what it looks like:

My installation happens to be new, and I made no changes to Excel's setup to open CSV files automatically.

Solution 3

For what it's worth, in Excel 2003 the tool is still under the Data menu but is called "Text to Columns" and allows you to select the delimiting character.

Solution 4

On Excel 2007, select the first cell, go to the data tab and click Convert text to table. A window pops up, select delimited and click next. Now select the right delimiter (comma in your case) and click next. Click finish.

Check here also for a more eleborate explanation: https://ask.qut.edu.au/app/answers/detail/a_id/2076/~/how-to%3A-split-delimited-text-into-multiple-cells-in-excel-2007.

View more solutions

Comments

[Pylsa]

Which version of Excel are you using?

[Pylsa]

is it solved now?

[Xavierjazz]

@BloodPhilia - as I have commented under Michael's answer, it works, but I need it to split the data into rows, not columns, as there is more data than columns. As a work around, I can split the file into 2, but it would be great to have a way to have the program change them from columns to rows. I'm looking. Thanks

[Nathan Fellman]

@BloodPhilia: only if the question itself is wrong. The point is, judging from the OP's description, he already has a CSV file. Now, if he were asking how to convert it to XLS, you'd have a point, but he's asking how to convert comma-separated-values to CSV.

[Pylsa]

The OP is asking: "How can I get Excel to select each "value" and move it to a separate cell?" That would be unthreading the CSV and converting it to xls no?

[Xavierjazz]

As I look more, I see that my question is not specific enough. I will edit it. Thanks.

[Pylsa]

@Nathan, Excel will NOT automatically display CSV correctly... It has to be set manually.

[Xavierjazz]

This works, however, it splits into columns. I get a message that some data will be lost as there is more data than columns. Is there a way to have it spit out rows rather than columns?

[Pylsa]

There should be an option to not omit that information.

[Pylsa]

Well I never had that... I always had to set it manually >.< What did I do wrong? lifted my -1

[Xavierjazz]

Hi Nathan. What program did you write the text into, and then use to save as a .csv? The names I have are in a text file, produced by selecting all the names in a CC file I was sent and pasted into a text file. I then changed all the ";" to "'" and saved it as a .csv file. When I open it in Excel 2002, all the names are in one cell. I want to spit out each name into a separate cell so I can then import the info into Outlook.

[TFM]

This is not a real answer, as there's no explanation to HOW the problem can be solved. You could however mention this as a comment to your previous answer.

[Nathan Fellman]

I created the text file using gvim. I could just have easily have used notepad.

[Pylsa]

That's a different answer, Excel just does not have the posibility to convert columns to rows, it can only convert csv records to rows.

[Xavierjazz]

Thanks. The 2nd operation you described would not work, but the first one worked like a charm. Perhaps the 2nd operation failed because not all addresses have names attached??? I have Notepad++ installed and used it.

[Xavierjazz]

Well, it didn't work for me. Regards.

[Lèse majesté]

I haven't tested it on Notepad++ (I use N++ at work, but only N2 at home), so I don't know if they parse RegExps the same. But, yea, the way I wrote it, you need to have both. How are the addressless items formatted? Do they have an empty item after them where the email would go, like: ..., grandpa, , grandma... or is it just ...grandpa,grandma...?

[Xavierjazz]

Here's a better example: [email protected], Axxx xx Cxxxx <[email protected]>,

[Lèse majesté]

I haven't tried it with display names, but as long as the spacing is as you've written, and only the names are ever missing (there aren't any names that don't have addresses), then the 3rd edit should work.