Monday, August 31, 2009

Cleaning a gmail contact csv extraction for Excel purposes

My wife put all her contacts in Gmail but needs them for publication in a excel array. She was never able to have one line per contact. This simple stuff is not yet super easy because of incompatibility of application interpretation.

I need to drill into gmail help to find a thread about the issue, which is partialy solved.

At the end I propose the following process to suppress the return in the field "notes" that creates the mess:

You need a gmail account, word, excel.
  1. Export you gmail contacts in export for other app mode (contact.csv)
  2. open contact.csv with word
  3. suppress the automatic correction option during the key in of quotes. You should have alway " but no <<>>. That may occur in French word for example.
  4. put in macro of word this macro (for word 2007 to be changed if necessary) which basically suppress the return everywhere it is not needed, suppress the tabulation which creates also some mess and get you a clean file
  5. copy paste this file into excel
  6. into excel convert the data : delimited / comma separator
  7. You should get your data cleaned.

Put a comment if you need more explanation.

7 comments:

André said...

Hi there!

Thanks for the macro. Part of the problem persists, though. In many email fields, the value comes, for example, as a single string such as:

adriano.ranieri@krit.com.br ::: adriano.ranieri@elp.com ::: aranieri@ens.com.br

I think this is the way GMail nests several emails of the same kind.

1) No ap seems to recognize this GMail trick.

2) I wonder: would importing it back into GMail in this format work? I don't dare try...

Thanks,
André Branco

Mike - CSV File Format Guru said...

I dis almost identical job of CSV file creation in Open Office using this article:

Create CSV File in Open Office

Anonymous said...

Thanks for solving my problem! :-)

Gerard said...

This was just what I needed. Thanks for your effort!

Richard C. Lambert said...

My wife put all her contacts in Gmail but needs them for publication in a excel array. She was never able to have one line per contact. This simple stuff is not yet super easy because of incompatibility of application interpretation. electric pressure washer reviews

Anonymous said...

Worked for me. Thanks!

Mark said...

Well that was easy - almost as easy as using the hotmail sign up page to register a Hotmail account. Except I'm lying, that wasn't easy at all! Gmail has a way better sign up page, imo.