Export iPhone SMS to the Excel worksheet

Posted on January 19, 2011

Folks, today I am going to share a trick with you, The trick is – How to Export iPhone SMS to Excel spreadsheet. This trick is quite simple though and you need not to be a technical person to do this. Just follow the guidelines from bellow to export iPhone SMS to Excel spreadsheet.

Export iPhone SMSHow to export iPhone SMS to Excel spreadsheetDownload a software called – SQLite (a Database browser)  from here

Well, SQLite Database browser is a light GUI editor for SQLite databases, built on top of QT. The main goal of the project is to allow non-technical users to create, modify and edit SQLite databases using a set of wizards and a spreadsheet-like interface.Now backup your iPhone information in iTunes before startingNow find SMS database file located in a backup folder on your computer. The file is named – 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata- In Windows 7/ Vista the file is stored in this path – C:\Users\\AppData\Roaming\Apple Computer\MobileSync\Backup\(some random id)\_ In Windows XP the file is stored in this path – C:\Documents and Settings\\Application Data\Apple Computer\MobileSync\Backup\(some random id)\

Export iPhone SMS Excel

NOTE – Generally the iPhone messages are stored in an SQLite database formatNow Copy 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata file from the folderNow Save iPhone SMS db file as SQLite file and to do that, Save 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata in desktop and rename it to sms.sqliteNow Open downloaded SQLite zip file and extract SQLite Database Browser.exe and Run it

Export iPhone SMSNow Click File menu >> Open Database>> and Open sms.sqlite file from the desktopNow click File >> Export>> Table as CSV fileSelect table name as message from the Drop down>>Select exportAnd Save the CSV File as Sms.csv

Export iPhone SMS

Now Simply Open the Sms.Csv file in Microsoft Excel and Save it as the file name as Sms.xlsNow Open the Sms.xls file and see few fields that you are interested in -address: This holds the phone number of the person that sent you or you sent the message to.date: This is a Unix timestamp of when the message was sent.text: The actual message.flags: This should be either 2 or 3. The messages flagged 3 are messages that you sent (outgoing), while the messages flagged 2 are incoming messages.

Export iPhone SMSNow insert a new column after the date column and insert this formula

=(((C2/60)/60)/24)+DATE(1970,1,1)+(-5/24)) and drag the formula for all cellsNow select all cells and right click select Format all cells>>Number>>Custom>>MM/DD/YYYY as shown image bellow

Export iPhone SMS Excel

Export iPhone SMSAnd that’s it. You have simply done it.

Well, Stay tuned with us for more updates like this, and if you have any questions or comments, or find any errors in the process just leave a comment

Caller ID spoofing – iPhone Application Allows to Fake Number

Posted on January 19, 2011

Caller ID spoofing has been around for several years now and the controversy surrounding it has not waned. Call spoofing is the act of changing the phone number that appears on someone’s call display when you place a call to them. Instead of blocking or revealing your real number, you choose the number that is displayed.

Although it is still somewhat unknown to the general public, this type of service has continued to gain in popularity and is used by everyone from regular individuals to professionals, law enforcement, and even celebrities. It’s a tool that allows people to retain anonymity and keep their identity, or at least their phone numbers, private. Even when placing a call to someone that is familiar and trusted, there is a chance that the person’s telephone could be lost or stolen with your number contained on it. For celebrities in particular, this could be disastrous.

There are several providers that offer the call spoofing service to anyone. In addition to call spoofing, other services and calling features have been added over the years. Some of these include voice changing, call recording, SMS spoofing, personal Spoof Numbers, and, most recently, mobile applications.

SpoofTel, one caller ID spoofing provider, has recently released an iPhone application that allows you to use its service from your iPhone. Due to its controversy, it has not become available on the iPhone store but is still available for download on other sites. Although it is still fairly new, the application has already been downloaded by thousands of iPhone users.

Caller ID spoofing has appeared in various news stories over the years, typically related to some type of proposal to ban or outlaw the service. There is a lot of controversy surrounding call spoofing because it has been know in the past to be used by scammers. Certain individuals have used spoofing services for fraudulent and illegal purposes in the past. However, most providers have become quite successful at preventing scammers from exploiting their services and it has not been as large of a problem as it was in the past. As well, spoofing services are used by many individuals for a multitude of purposes. The majority of individuals use spoofing services legitimately.

With spoofing providers continuously producing additional services and features, it doesn’t look like caller ID spoofing is going anywhere any time soon.

iPhone: If you can’t send or receive text messages

Posted on December 29, 2010

Summary

This article outlines how to troubleshoot issues with sending and receiving SMS or text messages using the Messages application on the iPhone. If you are having difficulty sending or receiving MMS messages, see this article.

f you can’t send or receive text messages

  • Check the top left corner of iPhone’s display to make sure you have a strong signal: If you don’t have a strong signal, try moving to a different location. If you’re indoors, try going outdoors or moving close to a window.
  • Update to the latest iPhone Software. For more info, see Backing Up, updating, and restoring your iPhone and iPod touch software.

If you still can’t send or receive text messages:

  • Ensure that you are first tapping in the text-entry field at the bottom of the screen (highlighted below) to bring up the keyboard. Until you begin typing, the Send button will remain grayed out.

[Text-entry field screenshot]

  • Power off iPhone, then turn iPhone back on.  Press and hold the Sleep/Wake button until the red slider appears. Slide your finger across the slider to turn off iPhone. To turn iPhone back on, press and hold the Sleep/Wake until the Apple logo appears.
  • Make sure that every phone number in your contacts list includes an area code.
  • Make sure Airplane Mode isn’t on. From the Home screen choose Settings, then turn Airplane Mode off.
  • If you are trying to send a text message to multiple recipients and you type in each number manually instead of using the contacts list, make sure you tap return instead of space to separate each one. For more info, see Sending SMS messages require a valid 10-digit mobile number (United States).
  • If you have recently ported your number from another carrier, please contact your current carrier to verify that the porting process has completed successfully.

Note: Both SMS and MMS support text messaging. If Show Subject Field is enabled in Settings > Messages > MMS > Show Subject Field, you can only send messages after you have typed content in the Body field. Until then, the Send button will be grayed out, and you will be unable to send the message. If you add text to the Subject and Body field you will automatically send via MMS. If you wish to send via SMS, not MMS, either type text in the Body field only, or disable the Show Subject Field option. The following table illustrates this relationship:

Subject Field only Will not send
Body Field only SMS
Subject Field and Body Field MMS

Additional Information

You may also see the message, “Error sending message”. Troubleshoot as above.

For more information on SMS, see the iPhone User’s Guide (you can also view the iPhone User Guide with your iPhone: tap Safari, then Bookmarks, and then iPhone Users Guide).

Recovery Utility for Deleted iPhone Text Messages

Posted on December 29, 2010

I’ve been getting barraged with so many requests to help folks retrieve deleted text messages from their iPhone backups that I decided to write a small utility to help out — fumbling around with a text editor was a little daunting for some.

Those using Windows can download my iPhone SMS Retriever here.

[Update 09/23/2009] – there have been many reports of installation problems. Thank you to those folks who have reported the issue — I’m working on an update to the utility within the next week to fix this problem. Thanks for your patience and understanding. I’ll keep you updated here.

iPhone SMS Retriever Screenshot

Here’s how to use it:

  • Download and unzip the file
  • First, run the SQLite Setup.exe file — this is a pre-requisite for iPhone SMS Retriever to work.
  • Second, run the iPhone SMS Retriever Setup.exe file
  • The app will install and run. It will appear under your Programs as iPhone SMS Retriever
  • You do need to know how to get the iPhone SMS backup file so you can point the app to load it. If you need help locating this file here’s some help — please read carefully as this step is where most folks get hung up:
    • First, connect up your iPhone, run iTunes (if it doesn’t autostart), and start a sync. iTunes should say “Backing up iPhone…” or something to that effect. Let the backup process finish.
    • If you’re running Windows XP: go to the c:\Documents and Settings\[your username]\Application Data\Apple Computer\MobileSync\Backup\ directory. There will be at least one directory with a very long and cryptic name — this is the backup directory that contains all the backup files from your iPhone. If you see more than one of these directories that’s fine — it just means you have several backups, each from a different time and/or iPhone on your computer. Go into the backup directory of your choice and locate the file 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata.Copy that file to your Desktop or some other easily remembered location.
    • If you’re running Windows Vista or Windows 7: go to the c:\Users\[your username]\AppData\Apple Computer\MobileSync\Backup\ There will be at least one directory with a very long and cryptic name — this is the backup directory that contains all the backup files from your iPhone. If you see more than one of these directories that’s fine — it just means you have several backups, each from a different time and/or iPhone on your computer. Go into the backup directory of your choice and locate the file 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata. Copy that file to your Desktop or some other easily remembered location.
    • If you cannot find the Application Data or AppData directory make sure you go into the Tools menu in your Windows Explorer window, select Folder Options and be sure the enable Show hidden files, folders, and drives.
  • Click the “Load iPhone SMS Backup File” button in the iPhone SMS Retriever app and select the file 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata you saved a few steps above.
  • Voila! The messages in the file will appear in the window for you to browse.

This is a v1.0 app, I wrote it in about an hour, there are no bells and whistles, but you should find that it serves the basic function of loading an unencrypted iPhone SMS backup file and displaying all messages sent from/received by the iPhone in question and the date/time of the messages.

I’ll add more features over time — would love to hear your ideas for features to add. Finally, I’m providing this as a free utility, use it at your own risk and only on your own iPhone backup files or those of others only with their permission.

[Update 09-10-2009]

There was a bug in the original installer I posted. I have updated the download link in the following post to point to the new installer package.

Text Message For Free on iPhone & iPod Touch and iPad

Posted on December 06, 2010

Text Message For Free on iPhone & iPod Touch and iPad

The real iPhone 3G,3GS rip-off: Text messages

Posted on December 06, 2010

While everyone throws a fit over Apple and AT&T’s tricky pricing for the iPhone 3G, the real travesty in pricing is slipping by largely unnoticed: AT&T is including no text messages in its iPhone 3G monthly plans.

This means that on top of your cellular plan, and on top of your new 3G data plan, you will need to purchase a text messaging plan. This is a rip-off of epic proportions.

Lets think about this for a minute. What is a text message? It’s a relatively tiny amount of data (160 characters or fewer) that is sent using the short messaging service (SMS). With the iPhone’s unlimited data plan, which includes everything but texting, you’re probably sending hundreds of megabytes, and soon gigabytes, of data each month for $20-$30. Yet to send 200 of these 160-character text messages, it will likely be $5 to $10 extra a month.

A text message is data, and a very small amount of it. So why am I paying so much extra — let alone anything extra for it?

This seems like it would be a perfect candidate for one of those “Fleecing of America” segments that run on the NBC Nightly News — except that it’s arguably worse in the rest the world. Of the 2.3 trillion text messages are expected to be sent this year, the United States represents a relatively small percentage with “only” 300 billion sent. Not surprisingly, China is the largest market for the messages, with well over 500 billion sent. Mobile companies are making billions of dollars off of these messages.

Even worse is that carriers effectively double charge for each message. They charge both the sender and recipient of each one. In the United States these messages usually cost around 10 cents per message but can go as high as 25 cents a message without a pre-paid plan.

If you’re anything like me, you’re going to use many more than just 200 messages included in a bare-bones plan. Like many first generation iPhone owners, I signed up with the default 200 messages that were included in the original plans. One month I overshot this by 350 messages, and I would hardly consider myself a heavy text messenger.

But the iPhone seems to do that. Just as it has spurred use of the Internet on mobile phones, the iPhone has made text messaging very appealing to me. One reason is that I actually have a keyboard now instead of using that bogus T9 typing mechanism all of my previous phones had. And the other is that Apple makes text messaging look just like its iChat application, with conversations threaded just like an IM.

I use text messaging on the iPhone now just like I use IM. This is a major problem when I only have 200 messages I can send or receive — and each one after that is 10 cents a message. I upgraded my plan to 1500 messages, but under this new iPhone 3G payment structure, this is likely to add around $20 to my monthly bill. Everyone is all up in arms over the $10 raise for 3G data plan, what about the extra $20 many of us are going to have to pay?

Perhaps one of the companies building applications will come up with a way to circumvent this ridiculous charge for text messages. During Apple’s SDK road map event, AOL was unveiled as partner to bring its AOL Instant Messenger program to the iPhone, but we didn’t hear anything about it at WWDC. Hopefully that is still in the cards, because it could be exactly what I’m looking for — assuming that Apple’s new Push Notification Service works as well as advertised.

Instant messaging on phones with unlimited data plans such as the iPhone could easily kill text messaging. And it should, because I don’t want to keep paying for unlimited data only to have it be limited to certain kinds of data.

Text messaging almost makes the mobile industry’s other scam, the ringtone, look like a deal. And it makes that $10 a month extra on your iPhone bill for 3G data look like a steal.

Export iPhone Text Messages to a Spreadsheet

Posted on December 06, 2010

iPhone 2.0 only: Want to save that string of flirty text messages for posterity (or evidence)? While there’s no super-easy way to get your SMS history off your iPhone, with some elbow grease it’s possible. While this method is a bit complicated and more proof of concept than anything else, let’s take a look at how you can FTP into your iPhone, transfer your text message database to your computer, and open it up in a spreadsheet application like Excel.

Nerd alert! This method requires a jailbroken iPhone and some FTP and DB skills. The end result does give you a spreadsheet-friendly file that includes your entire history of text messages in it, but no names of the people who sent the message (only the phone number). File this post under “proof of concept” instead of “must actually do.”
As per a helpful tipster, I tested Syphone , but try as I might, it would not see my iPhone (and, it’s Mac-only). If anyone knows of an iPhone application that saves text messages to your computer without going through this rigmarole, post it up in the comments and I’ll add it here.
Here’s one messy way to do it:

  1. Jailbreak. First, you’ll need to jailbreak your iPhone using these instructions (here’s the Windows user version). Make sure you install OpenSSH (here’s how) to make your iPhone accessible via SFTP.
  2. SFTP into the phone. Go to the phone’s network settings, and view the details of the active connection. Write down the device’s IP address. Using your favorite FTP client, SFTP into that IP address using the username root and password alpine.
  3. Download sms.db. Your text message database is located in /var/mobile/Library/SMS/sms.db on your iPhone. Download that file to your computer.
  4. Open the SQLite database. That sms.db file is a SQLite database, which you can edit and view using free tools. Download one of those tools—the SQLite Database Browser—and install it on your computer. Open up the sms.db file, and click on the Browse Data tab. Choose the message table and you’ll see your SMS history, and it’ll look like this:
  5. Export to CSV. From the SQLite Database Browser’s File menu, choose Export > Table to CSV file. Save the resulting comma-delimited file, and open it using your spreadsheet of choice (whether it’s Excel or OpenOffice.org).

Now, the big problem with the resulting file is that while it includes the messages themselves, the name of each person who sent each message is not there. (If any database wizards more hip to Apple’s DB structure know how to get that info there based on ID’s, I’m all ears.) Like I said—proof of concept. On closer inspection, it turns out that the “address” field displays the phone number of the message sender. Given that, you can easily grok who’s who (and add names based on that number into your spreadsheet).
You can also jailbreak your phone and download your voicemail messages this way. Is this living to geek instead of geeking to live? Yes it is. But I don’t judge how people get their kicks, so you shouldn’t either.

How To Save and Read Your iPhone Text Messages On Your Computer for free

Posted on December 06, 2010
The Apple iPhone has a multitude of features. Other than making and receiving phone calls, you can surf the web, send an email or type a text message. Learn about the ways that you can send and receive text messages on your iPhone.

Instructions

  1. 1

    Tap the SMS Text icon from the iPhone’s main menu. Next, tap the notepad icon to send a text message. Type in the recipient’s phone number, or tap the contacts icon to pick a saved contact. This icon is identified by a blue circle with a white plus sign in the center.

  2. 2

    Read your new text messages. Access the SMS Text icon. The total number of unread messages will appear above the SMS icon. Go to the text messages list and tap the unread messages that are identified with a blue dot next to them.

  3. 3

    Reply to your text messages by tapping a name or a phone number in the text messages list. Enter your message, then touch “Send.”

  4. 4

    Delete an old text message. Touch the “Edit” icon, then tap the deletion icon next to the message. The icon is represented by a red circle with a white bar in the middle. Or, swipe left or right across the text message with your finger and tap the “Delete” icon that appears next to the message.

  5. 5

    Call or email a text message sender. From the text message list, go to the contact, then the message. At the top of that message, tap “Call.” If you’ve saved your contact’s email, tap “Contact Info,” then tap the email to send the email message.

  6. 6

    Add a new contact by tapping the name or phone number in the text messages list. Choose the “Add to Contacts” icon to save that contact.

  7. 7

    Decide if you want an alert tone whenever you get a new text message. From the main menu of your iPhone, go to “Settings and Sounds.” Turn on the “New Text Message” alert.

iPhone text Messages Remain Long After “REMOVING”

Posted on December 06, 2010

[Original Post]
The other day I installed MobileFinder.app, a third-party, native iPhone application that allows browsing of the iPhone’s file system. While I was poking around the iPhone file system today I noticed a file called sms.db under the ~/Library/SMS directory. I opened the file in MobileTextEdit.app and saw that it was a SQLLite database file; no surprise there.

But what did surprise me was that every text message I had ever sent or received since I bought my iPhone on June 26 was stored in sms.db, despite the fact that I “deleted” these text messages via the iPhone SMS app user interface long ago.

It seems the iPhone SMS delete function is a soft delete. Perhaps Apple should implement a hard delete function similar to the Safari “Clear History” function, which incidentally now has me questioning whether “Clear History/Cache/Cookies” really deletes them.

I figured I’d post this as a follow-up to my previous post a few years back mainly because I regularly get questioned on the topic of how to retrieve deleted text messages. Furthermore, I wanted to post a method which didn’t require JailBreaking a phone or that was overly technical. So here you go, a pretty straighforward method for accessing text messages from your iPhone which may have been deleted but might still be available in the iPhone’s SMS database.

ASSUMPTIONS: I assume you are running Windows of some flavor and have synched/backed up your iPhone using iTunes on said machine.

1. Download and install TextPad
2. Open TextPad.
3. Select Search > Find in Files…
4. Complete the “Find in Files” dialog as you see in the following screenshot. Make sure you’ve checked the Search subfolders checkbox or else this won’t work. Note: this was taken from a Windows 7 machine. The path value for the “In Folder” field should be the same for Windows Vista (of course, your username will appear instead of “mike” in the path. For pre-Windows Vista your path will be under c:\Documents and Settings\[username]\Application Data\Apple Computer\MobileSync\Backup\

The General Theory of RIAtivity

Pondering the New Fabric of the Web — Rich Internet Applications (RIAs)


If you want to try retrieving deleted text messages from your iPhone check out the free Windows utility I wrote here.


[Original Post]
The other day I installed MobileFinder.app, a third-party, native iPhone application that allows browsing of the iPhone’s file system. While I was poking around the iPhone file system today I noticed a file called sms.db under the ~/Library/SMS directory. I opened the file in MobileTextEdit.app and saw that it was a SQLLite database file; no surprise there.

But what did surprise me was that every text message I had ever sent or received since I bought my iPhone on June 26 was stored in sms.db, despite the fact that I “deleted” these text messages via the iPhone SMS app user interface long ago.

It seems the iPhone SMS delete function is a soft delete. Perhaps Apple should implement a hard delete function similar to the Safari “Clear History” function, which incidentally now has me questioning whether “Clear History/Cache/Cookies” really deletes them.


I figured I’d post this as a follow-up to my previous post a few years back mainly because I regularly get questioned on the topic of how to retrieve deleted text messages. Furthermore, I wanted to post a method which didn’t require JailBreaking a phone or that was overly technical. So here you go, a pretty straighforward method for accessing text messages from your iPhone which may have been deleted but might still be available in the iPhone’s SMS database.

ASSUMPTIONS: I assume you are running Windows of some flavor and have synched/backed up your iPhone using iTunes on said machine.

1. Download and install TextPad
2. Open TextPad.
3. Select Search > Find in Files…
4. Complete the “Find in Files” dialog as you see in the following screenshot. Make sure you’ve checked the Search subfolders checkbox or else this won’t work. Note: this was taken from a Windows 7 machine. The path value for the “In Folder” field should be the same for Windows Vista (of course, your username will appear instead of “mike” in the path. For pre-Windows Vista your path will be under c:\Documents and Settings\[username]\Application Data\Apple Computer\MobileSync\Backup\
capture

5. Click Find.
6. In the TextPad search results pane you should see at least one search result found (even though multiple results may have been returned, they all will point to the same SMS DB file per backup folder). Below is a screenshot of what might appear (your filename will likely be different):

7. Double-click on one of the search results and the SQLite file will open in the upper pane. Much of it will look like garbage, but there’s alot of human readable info in the file, particularly your text messages. Enjoy

How can I get a silent text tone on my iPhone?

Posted on December 06, 2010

How can I get a silent text tone on my iPhone? You can filter out unwanted texts by assigning a silent text tone to specific contacts in your list. You can also set a silent text tone as the default and use normal text tones for people you want to hear text messages from. The iPhone comes with a silent text tone. You will find this under Settings -> Sounds -> Text Tone -> None. You can set this silent text tone for specific contacts or as the default on your iPhone.