From MSAccess to Localexchange

The marathon run for importing Norwich LETS's msaccess-based database to LocalExchange's MySQL-based database has ended a while ago. It took almost 7-8 hours and pushed my SQL knowledge to its limits. Here's how I did it.

0. Open database in MSAccess and export all tables as CSV file.

1. Make a list of columns in each table and create identical tables in MySQL.

2. Import the tables from the CSV files into MySQL:
$ mysqlimport -u dbusername -p --local --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by='\r\n' norlets tablename.txt

Got this tip from

3. Now that all tables have been brought into MySQL, it's time to push them into LocalExchange's database.
Here are the schema of the "categories" table from MSAccess:
description -> varchar(200)
And here's the schema of LocalExchange's "categories" table:
category_id int primary key
parent_id int
categories varchar(30)
Localexchange is residing in the "norlets" database and the imported tables are in "tmp". Copying was easy:
mysql> insert into norlets.categories select * from tmp.categories

4. Next I copied in the "wants" table from "tmp". Here's the schema of tmp.wants:
autono int
member_id int
member_no int
firstname varchar(30)
category varchar(50)
description varchar(50)
And here's the schema of the "listings" table of localexchange:
| Field | Type | Null | Key | Default | Extra |
| title | varchar(60) | NO | PRI | | |
| description | text | YES | | NULL | |
| category_code | smallint(4) unsigned | NO | | 0 | |
| member_id | varchar(15) | NO | PRI | | |
| rate | varchar(30) | YES | | NULL | |
| status | char(1) | NO | | | |
| posting_date | timestamp | NO | | CURRENT_TIMESTAMP | |
| expire_date | date | YES | | NULL | |
| reactivate_date | date | YES | | NULL | |
| type | char(1) | NO | PRI | | |

This "listing" table stores both the wants and offers.
The following SQL copied all the wants:

INSERT INTO listings(title, category_code, member_id, status, posting_date, type)
SELECT tmp.wants.description as title, category_id as category_code, member_no as member_id,
'A', now(), 'W'
FROM tmp.wants, norlets.categories
WHERE tmo.offers.category = norlets.categories.description;

A similar SQL copied everything from the offers table.

5. Now we have to copy tmp.member_detail into norlets.member and norlets.person.
First we copy into norlets.person using the following SQL:

INSERT INTO norlets.person(person_id, member_id, primary_member, directory_list, first_name,
last_name, address_street1, address_street2, address_city,
address_state_code, address_post_code, email, phone1_number,
phone2_number, address_country)
SELECT member_id as person_id, member_no as member_id, 'Y', 'Y', firstname as first_name,
surname as last_name, address1 as address_street1, address2 as address_street2,
address3 as address_city, county as address_state_code, concat(post, " ", code) as
address_post_code, email, phone as phone1_number, mobile as phone2_number, 'UK'
FROM tmp.member_detail;

6. Next we copy into norlets.member from tmp.member_detail with the following SQL:

INSERT INTO norlets.member
SELECT member_no, 'password hash', 0, null, null, 'A', null,
CONCAT(notes, "\nSubscription paid? ", IF(sub_paid=1, 'Y', 'N')),
DATE(STR_TO_DATE(join_date, "%d/%m/%Y %H:%i:%S")), null, null,
IF(jointp=0, 'S', 'J'), 0, SUM(lokes_total)
FROM tmp.member_detail GROUP BY member_no ORDER BY member_no;

Especially important to note here is the use of the date format string inside the str_to_date function.
Instead of using "%M" for minute as in php, it is using "%i". This is MySQL specific.

7. After this, some specific records were update with suitable values (e.g. primary membership) to match the needs of local exchange.

8. Next I added the "admin" account with an id of 999 into the norlets.member table and started to test Local Exchange with the newly created database.