SQLite to MySQL

I've been a licensed amateur radio operator since 2015, and Mac user since 1997. The combination of these two meant that when I was looking for a logging application for my ham radio contacts, my options were limited, which is fine since MacLoggerDX is such a great application.

MacLoggerDX uses an SQLite database to store contact information. As a web developer, I naturally wanted to find a way to publish my contacts on my web site using MySQL. Fortunately, MacOS comes with an sqlite terminal client which allowed me to export the MacLoggerDX database. Unfortunately, the process involved one or two glitches I had to overcome. Here's the solution I reached. This Stackoverflow Article was also helpful. Before performing any task like this, you would be wise to backup your data.

From a bash shell, execute the following command:

$ echo ".dump" | sqlite3 MacLoggerDX.sql > test.sql

Edit the output file and remove the following lines at the beginning/end of the file:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT

For some reason I had to convert all the single quotes to backtick character, and I Also add the following line before the "CREATE TABLE" statement to make repeated testing easier:

DROP TABLE IF EXISTS 

Since I have some international contacts with UTF-8 characters in the names, I added the following:

SET NAMES 'utf8' COLLATE 'utf8_general_ci';
SET CHARACTER SET utf8;

The primary index for this table is the 'pk' column. Add the AUTO_INCREMENT option for this field to the CREATE_TABLE statement.

Then from the bash command line, import the file you exported above:

$ mysql --default-character-set utf8 -u <user> -p <database> < test.sql

Occasinally I receive a syntax error on "CREATE TABLE". It may simply be due to forgetting the semi-colon at the end of the statement. In these cases, I have simply deleted the line and truncated the table before importing.