Friday, March 16, 2012

Android: How to Export all SMS from GO SMS Pro as Microsoft Excel format

Follow Step by Step guide.

  1. Open GO SMS Pro application in your Phone
  2. Click Application key (Normally Left bottom side of phone"
  3. Select "Services" from popup.
  4. Select "SMS B&R"
  5. Check All Options
  6. Select Settings Icon.
  7. Choose "Readable Mode"
  8. Choose "All Messages"
  9. Save.
  10. Press "Backup" to start backup process.
  11. You can email yourself the backup files. Choose your option if asked for email.
There are two ways to retrive backup files from phone to PC
  1. Download the file from Email, which was generated by GO Sms application.
  2. Copy File from Phone USB storage. You will need following two files
    1. smsbackup_yyyy-MM-dd.xml (Here yyy MM and dd is the date format)
    2.  gommssms.db (Located in Private folder of same path)
If you have private sms box, you will need to restore it separately. Lets restore common sms folder first

Restore Common SMS Folder in Excel.
  1. Open Microsoft Excel
  2. Click File > Open
  3. Select the XML file that we discovered above.
  4. Select "Open as Read only Workbook" when prompted.
  5. These are your SMS. Save a copy as xls file and enjoy ;)
Restore Private Box
  1. This is not pretty simple. But you can do it by following these instructions. You will need a good Database Reader software that understand the SQLite database. I choosed SQL Database Browser that can be free download from here
  2. Extract the downloaded zip in a separate folder in your PC.
  3. Double click to Execute "SQLite Database Browser.exe"
  4. Click File > Open Database
  5. Choose gommssms.db file that we discovered above.
  6. Now you will see some contents read by SQLite Browser. This is database file contains all your sms.
  7. Click again File > Export > Table to Csv File
  8. Select "SMS" table from the list
  9.  Click Export
  10.  Type the name with .csv extension and save.
  11.  Open File in Excel and save a copy as excel workbook.
You have exported two different excel files. One for common sms folder and the 2nd one is private box. You can combine both in one sheet later.
Enjoy... ;)

91 comments:

  1. Thanks...
    About the private messages..
    I opened the .db file..exported it as well to .csv format
    but the time and date arent readable. Everything else is fine.

    e.g.
    message sent at

    7/10/2012 0:16
    is displayed as
    1341861888064

    please help?

    ReplyDelete
  2. @Khan Sahib
    This is because The date stored in db file is integer format. To get actual date time, you will need to convert those integers into standard datetime format.
    There are two ways:

    1. Through SQL Query
    2. Through Excel Formula.

    Method 1: Unfortunately, the tool I have mentioned above is only support to export full table. Although it includes a query analyzer which displays the query results but cannot export.
    There are number of SQLite database browsers available that support to export query results. You can google for that.
    Open your "gommssms.db" in any such utility and Write the following query

    SELECT address as SMS_From, strftime("%d-%m-%Y %H:%M:%S", date/1000, 'unixepoch') as SMSDate, body as SMSText From sms

    You will have 3 columns in result to export: "SMS From", "SMS Date", and "SMS Text". That is what we required. Export your result and save in your desired format.

    Method 2: Excel Formula
    Export "sms" table in csv and open in Ms Excel
    (To prevent loss in formula Save it as xls workbook)
    Put following formula in a new column to convert exact date from integer date.
    =A1/86400000+DATE(1970,1,1)
    ("A1" is the cell reference of integer date)
    Format this new column as datetime.
    That's all :)

    ReplyDelete
  3. Thanks a lot man.. for the private msgs restore.. Love it! Have been founding a solutions since months.. Thank you :) You made my day!!

    ReplyDelete
  4. you are welcome. it's my pleasure ;)

    ReplyDelete
    Replies
    1. aoa rehan bhai...kindlybcz guide me in urdu ..bcz ma english is nt to much good...kindly tell hw can i store ma private message ..........tell me the whole procedure

      Delete
    2. Copy all the text you need to translate in URDU, and paste in there on google translater
      translate.google.com
      select Target Language "URDU" from the list.
      That's all :)

      Delete
  5. Hi,

    I'm trying to open a gommssms.db file created by GoSMS Pro v4.51. I've tried the latest version of SQLite Database Browser. I didn't install any other program. When I try to open the db file, nothing appears to happen. No messages, no warnings, nothing. I don't know what I'm doing wrong. There are almost 9500 messages and it's just over 3mb. Any help would be greatly appreciated.

    Thank you.

    ReplyDelete
    Replies
    1. Can you post the screenshot of sqlite after opening your db file?

      Delete
    2. It looks exactly the same as though I never opened any file except that the title bar just says the path to my db file and the name. It doesn't appear to be processing anything.

      Delete
    3. p.s. I'm sorry, I don't know how to post screen shots

      Delete
    4. Yes. In the export dialog box. the dropdown list for the table name is empty and when I click Export, i give the file a name like test.csv. It then creates a zero kb file with nothing it. it.

      Delete
    5. It seems your db file is corrupted. try again backing up.

      Delete
    6. I back it up every night. I've tried several different backups over several different nights and the same result. It seems to be read just fine in my sms program.

      Delete
    7. Hey Sorry for the delay, I found one solution.
      If you are using Firefox browser, there is an extension called SQLite Manager Addon. Add with firefox from here
      https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/
      (You will need to restart Firefox after installing it)
      I tested it, its working fine.
      After installing SQLite Manager in Firefox, Open your firefox browsers, click Tools > SQLite Manager
      Click Database > Connect Database
      Choose "All Files (*.*)" from file type
      Go to your database file path and open your db file.
      If your file is opened, you may proceed for next step.
      Click Table > Export Table
      Select "Main" From Database
      Select "sms" From Name of the table.
      Select CSV from available options
      Now Click "Ok" to export your file.
      That's all.

      Delete
    8. OMG! That worked, thank you! Now I can't figure out your instructions on how to convert the unix timestamp to date and time. I think the timestamp looks like this

      1335476834471

      in column F. I've never worked in Excel before, so I'm a little overwhelmed.

      =(

      Delete
    9. We have this formula
      =A1/86400000+DATE(1970,1,1)

      While "A1" is the cell reference of integer date.
      A1 = ['A' is the column, and '1' is the row number]
      When you combine the column and row it become a cell reference.
      You can replace "A1" with your cell reference of integer date.

      Delete
    10. How do I apply that to 10,000 rows without having to reference each row manually? I'm doing this in google Docs.

      Delete
    11. Method 1
      1. Put Formula on one cell
      2. Cursor on the formula cell, press Ctrl+C to copy cell data
      3. Select all Remaining Cells, right click on selection and paste.


      Method 2
      1. Put formula on top cell
      2. Cursor on the formula cell
      3. Hold down shift and go to last row by pressing "Down Arrow" key.
      4. After Selection completed, Press Ctrl+D to put the same formula on remaining below cells of the reference.

      Delete
    12. Sorry, you are doing it on google docs.
      Put the formula on top cell, and drag and drop the cell value by mouse to the remaining below cells.

      Delete
  6. Pls help me bro..
    To restore msg to phne...
    Msgs are in .db format

    ReplyDelete
    Replies
    1. within GoSMS app, press options key, and choose SMS B&R
      You will see options to restore messages.

      Delete
  7. Its unable to restore .db while i have already restored .dat files

    ReplyDelete
  8. buddy pls help to restore .db format messages....
    thnks a lot for the post to give us information to export to
    computer,,

    now m struck on how to restore msgs in .db format to my mobile

    ReplyDelete
    Replies
    1. Create a folder
      name it "private"
      copy both files in this folder (PrivateBoxContact.dat and gommssms.db)
      create a new folder "parts"

      Now you have 3 items in this folder
      1. parts [Empty Folder]
      2. PrivateBoxContact.dat [Private Contacts File]
      3. gommssms.db [Private Sms File]


      Out of "Private" Folder, create another folder and name it ".temp"
      move ".tmp" folder to a zip file and name it "2012_10_8_10_50_5[p].zip
      connect your phone to PC
      copy this file to your phone in following path
      SdCard\GOSMS\backup\localbr

      disconnect phone from PC.

      In your phone
      disconnect all internet connections
      open GO sms application
      menu key
      SMS B&R
      make sure you are not connected to internet
      tap on "Restore Messages"
      check that you have an entry "10-8 10:50"
      check this entry and tap "Restore".
      if the entry is missing, please check all steps from above.
      Let me know if you still do not find this entry in the restore list.

      Delete
  9. ENTRY IS THERE BUDDY,
    BUT ERROR COMES UP, THERE IS NO BACK UP FILE

    ReplyDelete
  10. Hi,
    In my gommssms.db are no messages. I tried it on two different phones. It has only 38 Kbyte. I have one database file at "sdcard\GOSMS\backup\private" and another at "data\data\com.jb.gosms\databases", but its the same size. Where are the messages stored at?

    ReplyDelete
    Replies
    1. first of all, you need to backup your private box. then select the backup file.

      Delete
  11. Hey dude, hi again..
    I wanted to convert that MS SQL database to MySQL. I have managed to do everything except the date time conversion.
    The MSSQL Function you earlier told was:
    strftime("%d-%m-%Y %H:%M:%S", date/1000, 'unixepoch')

    What would be its equivalent in MySQL? I got too much confused due to this.. Please help if you can

    ReplyDelete
    Replies
    1. Dude that was not Ms SQL Database. It was already a MySQL Database, and that query was also for MySQL Database.

      Delete
    2. Really? O.o
      I have Wamp Server installed and if i run that query in phpmyadmin, it says "FUNCTION db_name.strftime does not exist"

      Delete
    3. Ah.. nevermind dude. I found the same function in PHP and got it working. :) thanks a lot :)
      You rock :D

      Delete
    4. its a bug and you can resolve it.
      https://www.google.com.pk/search?q=%22strftime+does+not+exist%22&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:official

      Delete
  12. Asslam-O-Alaikum Rehan bhai.

    1) Main nay Aik APP install ki thi jis sy ma ny sms ka backup lia ha XML format ma. wo sms to asani k sath excel ma open ho jaty han lakin mje problem ye ha k excel ma open honay walay sms ki date show nhi ho rahi kuch digits show ho rahy han kindly is ka solution bata dain.
    2) main go sms pro k private box k messages ka backup XML main lena chahta hoon or apny message ko Excel ma open krna chahta hoon is ka kiya tariqa ha?

    ReplyDelete
    Replies
    1. The method of date conversion is described in comments above. Please read all comments for details. you will have all your answers.

      Delete
    2. Dear Rehan!
      I Apply the formula you mentioned above but i am facing still problem.
      When i apply this formula the value shows to me 0 and some time is shows to me 25569. i don't know where i am doing wrong, although i am applying this formula in A1 colum.
      I apply this formula several time and i get the value as i mentioned above.
      Kindly tell me in easy way and step to step how i can resolve my date and time problem.
      thanks

      Delete
    3. You put the formula with wrong reference.
      A1 is the cell reference where integer date is located.
      A Cell reference is the combination of Column and row number.
      A = The Column
      1 = The Row Number
      A1 = the cell, which is in column A, and row number 1.
      if "A1" cell is blank, the returned value would be 25569.
      you need to put formula with correct reference.

      Delete
    4. Rehan bhai is it possible you share with us SQLite database browser who doesn't only convert the table to us in CSV but also convert table date and time in Standard format.
      So that when we export csv file from that database browser we find our date and time in standard time automatically in correct form.
      please do it for me. i will be very thankful to you.

      Delete
    5. Rehan bhai i understand what you want to say.
      I know that A1 is cell reference. I put formula in other cell like i put it in G5 column and after that i replaced A1 with G5 in formula and press enter. After that i copy the entire G5 column and after that i put in head of the column where date and time is located and after that i drag this to bottom of my row where as it is the value shows to me is same.
      kindly now what i do? :(

      Delete
    6. See this example
      https://docs.google.com/folder/d/0Bx-n0OJy6TLWcnE5WG5hUTFXUjg/edit
      SMS-01-09-2012.xls
      Download this file in your pc if you do not use google docs.

      Delete
    7. Rehan bhai file is not exist.
      Bhai i was struggling yesterday again to convert date and time in standard form.
      For example i have E2 cell where integer date is exist i applied the formula and replace the A1 to E2 the value show to me like this 44021.13954. it seems i applied formula well but now i don't understand where the problem is again :-(

      Delete
    8. Rehan bhai you did give me file above is not exist.

      waiting for your kind response.

      Thanks

      Delete
    9. Download from here
      http://www.mediafire.com/view/?zjzg9zr1zc0pvfb

      Delete
    10. Thanks Rehan bhai.
      Dear you change it with same above mentioned formula who i applied ever but couln't get the result i wonder how you get result with this formula in correct form.
      give me your email address i will send you my workbook. kindly you change it for me as well. thanks

      Delete
    11. Dear Rehan.

      Your formula is working dafiniteley 100% but uou forget even one thing that after applying this formula you will get a value and after that you will format this value to date and time then you will get your result. i did it.

      Many Many thanks to you dear. thanks a lot.

      Delete
  13. Hi :) I am using go sms pro and i already set the schedule backup (local, not the icloud) in .xml file. Now i lost all the messages but i can't restore the file from local backup. Please help! Thankssssss!

    ReplyDelete
    Replies
    1. switch off your wife and other internet connections, then try to restore. Please also check that backup files exists in your backup directories.

      Delete
  14. Hi, I don't have the option in settings for readable mode, any ideas?

    ReplyDelete
    Replies
    1. They have changed some options. I don't know why. I got the setting here.

      Switch off your wifi and all internet connections

      Open Go SMS

      Select SMS B&R

      Select B to Cloud

      Select Backup SMS

      Confirm the network error message

      Select "Restore messages" from options

      Select "Old Restore" from rigth top corner of the screen

      When a popup window appears, Tap on the setting icon on the right top corner of window.

      Select "Readable Mode" from options and save the settings.

      Now whenever you create backup, it will generate readable xml files.

      I hope it helps.

      Delete
  15. can any one tell me how to move sms pvt box to normal box faslty

    i have 10,000+

    it takes to much time to move so give me fast solution thanks

    ReplyDelete
    Replies
    1. Try it, it may help.
      Within Private Box. Press the Application key (left Menu key).
      Select "Batch Mode"
      Select "Mark All"
      Long press on any thread, and select "Move out of private box".

      Delete
    2. Open GO SMS Pro application in your Phone
      Click Application key (Normally Left bottom side of phone"
      Select "Services" from popup.
      Select "SMS B&R"


      I do not understand it, I do not see this "Services" "SMS B&R"

      Delete
    3. I am unable to move my SMS private box to normal box either. I only have 1400 and nothing I do works. I have tried the Select All from the Menu button but Batch does not appear. Please help with this. The Devs from Go SMS don't seem to be able to assist. Thanks!

      Delete
  16. Hi! I recently uninstalled GoSMS (with regular weekly backups and I checked the GoSMS folder in the SD card which was still there) and when I installed it again, it did not recover my old SMS, even though there are still DAT files in mnt/sdcard/gosms/backup/smsfolders. I noticed that when I tried to choose Restore in the app, it made a new copy of a backup file and it only shows the recent backup as an option for restore. This file is saved in sdcard/gosms/backup/setting as an .xml file. Is there any way I can import the data from the DAT file and be recovered/restored in GoSMS app?

    Hoping for your kind response to my query. thank you! have a nice day!

    ReplyDelete
  17. The file you mentioned is go sms setting backup file. It is not sms backup.
    try this.
    switch off your all internet connections
    open go sms
    press edit key
    select "SMS B&R"
    Select "B to Cloud"
    Select "Restore SMS"
    Confirm the message of internet connection error
    Select "Restore Messages"
    check that your backup file int he list, select to restore it.
    if you did not find your desired file in the list, select "old restore" from top right corner on the same list.
    hope this helps you

    ReplyDelete
    Replies
    1. Hello!
      This helped me very much, it worked! I restored all the messages!
      Shukran! I really appreciate it.
      May Allah continue to bless you!

      Delete
  18. Hi Rehan,

    Thanks for the info above, much appreciated.

    Do you have a formula to convert the contents of the address column into actual telephone numbers?

    ReplyDelete
    Replies
    1. There is already a separate field for phone number. Why to convert from other fields?

      Delete
  19. Hi, I made a backup of my messages and after reinstalling Go Sms Pro, when I want to restore these messages, after choosing .zip file with the backup the app shows 'restore finished, no backup file' and doesn't restore any messages. Can you help me?

    ReplyDelete
    Replies
    1. Try "Old restore" option from top right corner of the same restore screen where zip files are listed.

      Delete
    2. The same message - even when I choose my .zip file in Advenced it says 'There is no backup file'. I think that this file is good, when I unzip it on PC I can see my all messages in .xml file.

      Delete
    3. It may be due to version difference of application. You may created backup file with old version and restoring it from another version. Try restoring from same version of application version.

      Delete
    4. I tried something like 10 different versions of Go Sms and it always ends the same :( Is there some way to check which version of the app was installed on my phone?

      Delete
  20. HI, i exported the sms from ggmmssms.db into excel using VALANTINE STUDIO nd by using your query ["SELECT address as SMS_From, strftime("%d-%m-%Y %H:%M:%S", date/1000, 'unixepoch') as SMSDate, body as SMSText From sms"] i got the result in excel format.

    My question is---> i ve deleted some rows which i don't want . and can now convert that excel file into gommssms.db file and use in my gosms apps

    ReplyDelete
    Replies
    1. converting back to .db format is a long process, check your MySQL program if there is any option to import excel files in database.
      The easy and alternate solution is to work with your data within gommssms.db
      you can execute SQL queries for deleting unwanted records.
      Open the table and look for "smsid" column,
      note the all smsid's of unwanted rows.
      for example you have following smsid's
      4,6,8,9
      now execute the following query
      "Delete From sms where smsid in(4,6,8,9)"
      or if you have a range then its good, you can put the range in query
      "Delete From sms where smsid between 4 and 10"

      Delete
    2. Hello and thank you in advance. I am in need of solution to a problem with go sms pro.
      What happened is this, a couple of nights ago I tried to do what I do often, and back up my messages with sms backup and restore. However, the private box is where these messages were and the backup will not backup anything BUT regular threads. So, I right clicked on the thread and asked to move it out of private box. There were over 62,000 messages. It began to move them and was taking a long time as usual so I left it alone. When it said they were moved, I told the backup to back them up. When it completed, there were only a little over 12,000 messages and they had doubled or tripled the messages (this has happened often though I have it set to archive the messages and only copy what is new.. I could not find the messages anywhere. The normal xls document was in the backup folder on my computer(I had backed up the entire phone again to be safe) but again, only 3,244 kb and perfectly readable. But now I have located a folder that has inside it 3 files, one says parts and is a file folder, inside of it there are 8 files different sizes that are all numbered, there is a folder thats labeled gommssms and is a data base file sized at 4,643 kbs, there is a foldr called private box contact list and is a dat file 1 kb. I tried opening it with a free file opener and it came up as some sort of chart. How do I restore them to xls files to be readable? HELP Thank you so much..I've also messaged you on your google page

      Delete
    3. There are number of sms backups located in "localbr" folder in zip format. You should check all of these one by one to find your missing sms.
      The full path is
      SDCard\GOSMS\backup\localbr
      you will find some zip files with backup date.

      Private box contact list contains contacts information only, nothing to do with it. You need to open gommssms.db.
      The method to open and export gommssms.db is described above.

      Delete
  21. I also again backed up my entire phone just now to be safe.

    ReplyDelete
  22. hello sir, while deleting the records of sms table using VALENTINA STUDIO i'm getting a error "database disk image is malformed". actually i ve exported the sms table to CSV and i deleted some of lines . now i want to all the sms records of gommssms.db and import my CSV file
    please help

    ReplyDelete
  23. i hav a problm....aftr following the forementioned steps i opened the xcel file which has only 2 entries locale and en_in.....i do not know wat to do....pls help me

    ReplyDelete
    Replies
    1. You need to select "sms" from tables list when exporting.

      Delete
  24. it is pretty difficult n confusing .can i have a video because that ll help me n others to understand in a better n easier way ?i got more than 20 thousand messages in my private box n i want to store those all at my pc .

    ReplyDelete
  25. i ve 26 thousand files n my private box for 3.6mb. m nt able to recover it completely in one single stretch..
    can u help me?

    ReplyDelete
  26. Hi its been 2 years since this work but there's a new version of it and its ver.v10 i think? But how can i used this? i really need this because my simcard is full because of the 7564 mess that i don't to delete maybe there's a solution to it? please help me. how can i move it to sd card?

    ReplyDelete
    Replies
    1. yeah it's updated, but if you are not using private box, you can use same method to backup/export/recover your messages.
      They made more difficult for private box recovery.

      Delete
  27. Please help me
    When I try to export my 20573 sms into SD card when 18354 sms upload then an error occurs sorry faild
    There should be a network problem
    There should be less space in card

    I cant understand this problem
    Please let me know about that
    Thanks!

    ReplyDelete
  28. hi i have Android Version 4.2.1
    And i have installed Go SMS Pro version 6.1
    so i have totally all the different options as you are mentioned above.
    What can i do ???

    ReplyDelete
    Replies
    1. check the folder
      Storage> GO SMS
      there should be some zip or db backup files.

      Delete
  29. i reinstalled my go sms pro but when i restore my private msgs that were not complete is there any way to restore them?

    ReplyDelete
    Replies
    1. Check the folder
      Storage > GO SMS
      There should be some zip or db backup files. I am sure you'll find your desired messages.

      Delete
  30. i made a backup file through go sms and then i reset my phone...
    now when im trying to restore my messages its giving a error that no restore file but the file i present in my laptop as a zip file.
    what should i do ?

    ReplyDelete
    Replies
    1. and i even transfered the file to my phone in the go sms's restore folder.

      Delete
    2. Backup Zip file should be in following location

      GOSMS\backup\localbr\2015_8_26_15_5_20.zip

      filename mentioned here is just an example, your file may have different name with date time format.

      Delete
  31. any one can help me
    i uninstall go sms pro
    but i have no backup of private box
    how can i get back these sms

    ReplyDelete
    Replies
    1. check gosms folder in your internal storage and if found, you can restore backup (read above information)

      Delete
  32. You did a perfect job :)
    One thing, what is the formula about the conversion of that integer date and time column into only in time like that of date.
    Than you.

    ReplyDelete
  33. Yes... i did... it worked for Date not for time... I used this formula: A1/86400000+DATE(1970,1,1) and change column type to "long date" but when i tried it for "Time", im having 4 - 6 hours error.
    Can u suggest something.

    ReplyDelete
  34. How can i see the messages from private box of Go sms pro app in my child phone from my phone...

    ReplyDelete