Not understood how to decode .bak to sql

[beowulf83]

Hi!

I have no background in coding/programming. I appear to be facing the same issue with Expense IQ that you faced and came across your code while searching for a solution.

So I have understood the steps up to using log cat to find a line that causes restoration failure in Expense IQ.

However, after that I am completely lost how to decode the .bak Expense IQ file and use your tool to correct the lines that are causing the restoration failure.

Would you be able to help me with more guidance/instructions keeping in mind that I have no knowledge of power shell or coding?

I have been using Expense IQ for a long time and I switched to a new phone recently and while using the cloud sync option in Expense IQ all of my data got erased. And now I am not able to restore from the backup files that I had created.

Thank-you.

[vmkernel]

Hi,

It would be quite hard to fix the issue without coding and/or PowerShell background, but let's try to do something about it.

Do you managed to find an error message related to the restore failure using logcat?

[beowulf83]

Hi,

Thank-you so much for replying. Really appreciate it.

So this is the error message I got in logcat:

(1) near "s": syntax error in "INSERT INTO project(_id, name, description, budget, color, activated, currency, uuid, updated, deleted ) VALUES(3,'Kathmandu (Bibhav's Wedding)','',0.0,'7986CB',0,'INR', '5d9f005757d android.database.sqlite.SQLiteException: near "s": syntax error (code 1 SQLITE_ERROR): , while compiling: INSERT INTO project(_id, name, description, budget, color, activated, currency, uuid, updated, deleted ) VALUES(3,'Kathmandu (Bibhav's Wedding)','',0.0,'7986CB',0,'INR', '5d9f005757db47d7b44f04e7bbdfb7d1', '1487869451309', '0' ) at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1068) at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:673) at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:590) at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:62) at android.database.sqlite.SQLiteStatement.(SQLiteStatement.java:34) at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:2086) at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:2008) at com.handyapps.expenseiq.activities.ArchiveV2.importTran(ArchiveV2.java:6) at com.handyapps.expenseiq.activities.ArchiveV2.importDataV2ScopedStorage(ArchiveV2.java:10) at com.handyapps.expenseiq.fragments.settings.NCardListBackupMenuFragment$8.run(NCardListBackupMenuFragment.java:9)

Hope the above helps.

[vmkernel]

Hi,

The logcat ouput means that the problem is in description of one of your projects. As you can see, there's an apostrope symbol after Bibhav, which causes a restore process to fail. Kathmandu (Bibhav's Wedding)

I've updated the script a little, so I hope it would be easier to fix the issue.

1. Download the updated script to your computer following the link: https://github.com/vmkernel/expense-iq/blob/fix_and_update/expense-iq-backup.ps1

2. Download a backup file of Expense IQ to your computer as well.

3. Run PowerShell in administrator mode.

4. Execute the following command which set less strict script execution policy allowing to run a local unsigned scripts Set-ExecutionPolicy RemoteSigned

5. Load the downloaded script in running PowerShell session. This is an example from my PC: . "D:\Users\Aleksey\Projects\expense-iq\expense-iq-backup.ps1" Please note that the first symbol is a dot and the path "D:\Users\Aleksey\Projects\expense-iq\" sould be replaced to the path where you've saved the downloaded script.

6. Run a decoding command. This is an example from my PC: Decode-ExpenseIqBackup -InputFile "C:\Users\aleks\Downloads\daily_autobackup.bak" -OutputFile "C:\Users\aleks\Downloads\expense_iq.txt" Please note that: "C:\Users\aleks\Downloads\daily_autobackup.bak" sould be replaced with the full path to your Expense IQ backup file on your PC. "C:\Users\aleks\Downloads\expense_iq.txt" should be replaced with a path to any folder which is good for you, as well as the file name (it will be created during the decoding).

7. Wait until PowerShell finishes running the script Depending on your backup size in might take a while.

8. Open the decoded file (named expense_iq.txt in my example) in any text editor. I suggest using VS Code or Sublime Text as they support UTF-8 encoding.

9. Find the line with text Kathmandu (Bibhav's Wedding), remove the apostrophe symbol after Bibhav.

10. Save and close the file.

11. Switch back to PowerShell window and run an encoding command. This is an example from my PC: Encode-ExpenseIqBackup -InputFile "C:\Users\aleks\Downloads\expense_iq.txt" -OutputFile "C:\Users\aleks\Downloads\daily_autobackup_fixed.bak" Please note that: "C:\Users\aleks\Downloads\expense_iq.txt" should be replaced with the same path you've used to decode a backup file (see point 7 above). "C:\Users\aleks\Downloads\daily_autobackup_fixed.bak" should be replaced with path to any folder which is good for you as well as the file name (it will be created during the encoding).

12. Wait until PowerShell finishes running the script Depending on your backup size in might take a while.

13. Upload the encoded file (named daily_autobackup_fixed.bak in my example) to Expense IQ's backup folder on your phone/tablet.

14. Try to restore the uploaded file in Expense IQ.

Wish you luck! And feel free to ask any questions.

[beowulf83]

Hi,

Thank-you so much for providing such detailed and easy to follow instructions.

So I seem to be stuck at the sixth step. After I run the decoding command nothing seems to happen (have attached a screenshot below).

I left my computer running for around five hours but I didn't get any output file. What am I doing wrong?

Or is the script running and it will take over five hours to decode given that my Expense IQ back up file has 8 years of data? Is there anyway I can check whether the script is running or get some kind of progress bar?

Really appreciate you taking time to help me out. :)

[vmkernel]

Hello,

It seems like you missed a closing double-qoute symbol (") after the script file name. Based on your screenshot it should look like (attention to the last symbol): . "c:\users\angsh\downloads\b\expense-iq-backup.ps1" (note: the text above is just for example and misht contains typos)

The script processes data quite fast. My backup file contains 10 years of data and it's processed for 20-30 minutes. Moreover, the version of script I've made for you writes an output file as it works on an input file. So, just after the script starts working you'll see an output file wich size will grow as the script processes the input file. In your screenshot you should watch for a file named expenseeed.txt. If you see the file and its size is growing, then script is working.

Please wait until it finishes, and move to the next step. If you see the following text in a new line of PowerShell console with a blinking carret symbol, then you're good to go. PS C:\Windows\System32>_ (note: this is an example I've taken based on your screenshot)

[beowulf83]

Hi,

That did the trick. :D

I was able to restore my Expense IQ data.

Thank-you so much getting me out of this fix. I thought I had lost all my data.

[vmkernel]

Glad to hear this! )) You are welcome!