Over the years I used CanIon, I ended up with 13 different SQLite databases. Some contained overlapping data which made it difficult to track when and how much data I had collected. To do the data analysis shown elsewhere on this forum I had to bring this data together in one consistent DB on my PC. This is the procedure I used.
Step 1 Install SQLite on the PC
You can find the package here:
https://www.sqlite.org/download.html
under Precompiled Binaries for Windows.
Step 2 Collect the CanIon btcan.db files in one folder on the PC.
Step 3 Open the most recent btcan.db in SQLite. This insures that you have the tables such as SECLOG with all the columns
Step 4 Export this DB with only the table structure
File>Export>Database to SQL file gives this dialog:
Note that “Export schema only” is chosen in the second to the last box. Do not change the other settings. Click save and choose a new DB name as e.g. btcanAll.db.sql in the next dialog. Then save the file.
Step 5 Export the same DB with only the data
File>Export>Database to SQL file gives this dialog again:
Note that “Export data only” is chosen in the last box. Do not change the other settings. Click save. Then save the sql file with the original btcan name.
Some but not all btcan files have a table called android metadata. If this table isn’t found in btcanAll then avoid exporting data from this table. You can do this by clicking on android metadata in the above dialog and thereby removing the highlight.
Step 6 Open each of the btcan files and repeat step 5. Now you have one empty db.sql file “btcanAll.db.sql” and db.sql files with only data for each of the DBs you wish to merge.
Step 7 Import the empty db.sql file which I’m calling btcanAll.db.sql using File>Import>Database from SQL file. Choose btcanAll.db.sql and open. Answer yes when you get the “Do you what to creat a new database” question. In the next dialog choose a new name, you can use btcanAll.db, and click save.
Step 8 If you suspect that the same data can be found in more than one DB you now have to remove the primary key in the tables with primary keys. If you don’t do this the process of importing all your data into this empty database will stop if it encounters data already in the database. The tables with primary keys are:
ODOLOG on ODO
ODOLOG_MILES on ODO
SECLOG on TIMESTAMP
TRIPS on TSDEB
TRIPTIMER on TIMESTAMP
To remove the primary key, click on ODOLOG in the Database Structure dialog. Right click and choose Modify Table and in the next dialog remove the check mark under PK by ODO. Click OK and repeat the process for the other 4 tables.
Step 9 Now import the SQL data files into btcanAll using File>Import>Database from SQL for each of the SQL files with data only. Answer NO to the “Do you what to creat a new database” question. If everything works as it should you will see a progress bar then an “Import completed” dialog. Click ok and repeat the import for the next db.sql data file.
When you have finished the process you should have one btcan database with all you data.
If you have duplicate data in your tables and you need a data set without duplicates you can do this with a SQL query. Here is an example for the SECLOG table:
You can export this unique data set to a comma separated file using the magnifying glass icon in the fourth line from the top. Comma separated files can be imported into Excel and many other programs. They can also be imported into a new btcan database without duplicate data and with the primary keys intact.
