Import Nokia SMS and Contacts CSV Export to Google Excel Spreadsheet

Submitted by tomo on March 25, 2011 - 5:03am

Using Nokia's PC Suite, one can export SMS messages from one's phone (maybe only their smartphones with USB). The format is CSV but the output is basically unreadable due to PC Suite bugs. Only a phone number is exported per message, no contact name. But more importantly, the CSV output is broken -- double quotes aren't properly escaped.

I think it's useful to be able to browse old messages online, say in a Google spreadsheet. But to do that, we need to rewrite PC Suite's output a bit.

[I didn't know anything about scripting Excel (or Google's spreadsheet which is basically Excel) but I did want to look up some old messages because I couldn't remember somebody's birthday. After importing messages from a year ago to Google docs, I could read messages between my friend and I around that day to find out exactly.]

First, export your messages. If your phone has a strict limit of stored messages, or gets really slow after a few hundred messages, then you should be doing this regularly. Also, export your contacts to CSV.

Then, run this script on each SMS CSV file:
[Update: I rewrote this in Javascript which you can use directly at the bottom]

#!/usr/bin/python

import sys
for line in sys.stdin:
    line = line.replace('\r', '').replace('\n', '')
    x = line.split(',')
    x[7] = x[7][1:]
    x[-1] = x[-1][:-1]
    print ','.join(x[:7]) + ',"' + ','.join([y.replace('"', '""') for y in x[7:]]) + '"'

E.g. save that as fix.py, then run:

./fix.py < exported-inbox.csv > fixed-inbox.csv

Now open up a new Google Docs spreadsheet and import fixed-inbox.csv as a sheet, and your exported contacts as another sheet.

In your contacts sheet, add a new column before the first column and populate it as a function:
=EXPAND(RIGHT(O1:O2, 7))"
You can then drag all the way down, so that each row of that column has that expression, with appropriate row numbers. This creates an index of the most significant numbers of a phone number (my Vietnamese phone numbers are a mix of +84... and 09..., this normalizes them). It's important that this column is to the left of your contacts' names.

Now in the sms export sheet, add a column, say column D after the number, which is column C. Use this expression:
=VLOOKUP(RIGHT(C1, 7),contacts!A:B, 2, FALSE)
What this does is use the "contacts" sheet's A column as an index column to lookup, and we again normalize the phone number in column C (right-most 7 digits) to print out the name in column B of contacts.

And Voila! You have contact names next to each message.

Below is a JS snippet that will fix your Nokia PC Suite exported csv files.



Click this link to transform the pasted text into the box below:
Fix CSV

Read the rest of this article...
Guriiyzyh (not verified)

Dutch non-profit organization Mars One intends to be the first to be able to deliver mankind to Mars and build a city. Following the schedule, it will occur very soon - in 2023 , that is two years faster than the other participants of the " race of Martians " - the U.S., China and Russia .

Who thinks about this? # notraty

Anonymous (not verified)

This is really great, i have looked for this information literally for years haha

btw i can't use the java script snippet where you paste the csv file, I tried to copy the csv file from windows explorer then right click and paste it in the box, nothing happens.. please enlighten me

File Share Live (not verified)

Great website you have here but I was wanting to know if
you knew of any discussion boards that cover the same topics talked about here?
I'd really love to be a part of online community where I can get advice from other knowledgeable individuals that share the same interest. If you have any suggestions, please let me know. Thanks!

Northern Lights (not verified)

Your style is so unique compared to other people I have read stuff from.
Thank you for posting when you've got the opportunity, Guess I will just bookmark this site.

younger looking skin (not verified)

Howdy! Do you know if they make any plugins to help with Search Engine
Optimization? I'm trying to get my blog to rank for some targeted keywords but I'm not seeing very good success.
If you know of any please share. Kudos!

a fantastic read (not verified)

Superb website you have here but I was wanting to know if you knew of any forums that cover the same topics discussed here?

I'd really like to be a part of online community where I can get advice from other experienced people that share the same interest. If you have any suggestions, please let me know. Many thanks!

tomo

Here's a forum: http://www.saigonist.com/forums/living-and-working-vietnam

Tarifvergleich (not verified)

I гeally love уour webѕite.
. Great colors & thеme. Did yοu maκе this amazing site уourself?
Рlease reply back as I'm wanting to create my very own blog and would love to find out where you got this from or just what the theme is named. Thank you!

alexander group llc (not verified)

I would also love to add if you do not now have an insurance policy otherwise you do not participate in any group insurance, you could well
gain from seeking the help of a health insurance broker.
Self-employed or people who have medical conditions commonly seek the help of a health insurance
agent. Thanks for your text.
Thanks for your short article. I would also like to say that your health insurance brokerage service also works for
the benefit of the particular coordinators of your group insurance policies.
The health agent is given a long list of benefits desired
by a person or a group coordinator. What any broker really does
is look for individuals and also coordinators that best complement those requirements.
Then he provides his tips and if all sides agree, this broker formulates
a contract between the two parties.
I will also like to express that most of those that find themselves without the need of health insurance are
usually students, self-employed and those that are unemployed.

More than half of those uninsured are really under the age of Thirty five.
They do not think they are in need of health insurance since they're young in addition to healthy. Their income is frequently spent on homes, food, plus entertainment. Many people that do go to work either 100 % or part time are not offered insurance by way of their jobs so they proceed without due to the rising price of health insurance in the us. Thanks for the thoughts you discuss through this blog.
Thanks for the ideas you write about through this site. In addition, lots of young women that become pregnant tend not to even seek to get health insurance because they have anxiety they wouldn't qualify.
Although many states today require insurers produce coverage despite the
pre-existing conditions. Prices on these kinds of guaranteed
programs are usually larger, but when thinking about the high cost of medical care bills it may be your safer route to take to protect one's financial future.
I have observed that of all kinds of insurance, medical insurance is the most dubious because of the turmoil between the insurance company's
duty to remain making money and the buyer's need to have insurance policies. Insurance companies' profits on wellness plans have become low,
hence some organizations struggle to generate income.
Thanks for the strategies you write about
through this web site.

zur seite (not verified)

I'm gone to tell my little brother, that he should also pay a visit this weblog on regular basis to take updated from latest information.

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Allowed HTML tags: <h2> <h3> <h4> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd> <img> <div> <pre>
  • Lines and paragraphs break automatically.
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <c>, <cpp>, <drupal5>, <drupal6>, <java>, <javascript>, <php>, <python>, <ruby>. The supported tag styles are: <foo>, [foo].
By submitting this form, you accept the Mollom privacy policy.
© 2010-2014 Saigonist.