0

I'm using mdb-tools on FreeBSD to convert a Microsoft Access DB to MySQL.

The script looks like this (to_mysql.sh):

#!/usr/local/bin/bash
echo "DROP TABLE IF EXISTS Student;"

mdb-schema -T Student $1 mysql

mdb-export -D '%Y-%m-%d %H:%M:%S' -I mysql $1 Student

And I'm using it like:

./to_mysql.sh accessDb.MDB > data.sql

The problem is that the GUID (the second column) in the mdb changes for all rows.

In the access DB one row looks like this:

|{D115266B-D5A3-4617-80F8-7B80EE3022DA}|2013-06-11 08.54.14|2015-12-17 14.57.01|2|2||||||0|111111-1111||Nameson|Name|||||3|||SA|0||||0|Gatan 2|222 22|1234 567

And when I convert it to MySQL using the script above it looks like this:

INSERT INTO `Student` (
    `UsedFields`,`GUID`,`Changed`,`ChangedLesson`,`AccessInWebViewer`,`VisibleInWebViewer`
    ,`PasswordInWebViewer`,`Language`,`UserMan`,`SchoolID`,`Owner`,`DoNotExport`
    ,`Student`,`Category`,`LastName`,`FirstName`,`Signature`,`Sex`
    ,`Phone`,`SchoolType`,`Grade`,`EMail`,`Program`,`IgnoreLunch`
    ,`ExcludedTime`,`Individual timetable`,`Adress(TEXT) `,`Postnr(TEXT) `
    ,`Ort(TEXT) `
    )
VALUES (
    NULL,"{266bd115-d5a3-4617-f880-807b30eeda22}","2013-06-11 08:54:14"
    ,"2015-12-17 14:57:01",2,2,NULL,NULL,NULL,NULL,NULL,0,"111111-1111"
    ,NULL,"Nameson","Name ",NULL,NULL,NULL,NULL,"3",NULL,"SA"
    ,0,NULL,0,"Gatan 2","222 22","1234 567"
    );

Everything is correct except the GUID column, it changes from:

{D115266B-D5A3-4617-80F8-7B80EE3022DA}

to

{266bd115-d5a3-4617-f880-807b30eeda22}

It looks like all the chars just reordering, but I have no idea why.

Does anyone know why and how I can prevent this?

Thank you!

Victor
  • 1
  • 2
  • You mean it changes _to_ ... _from_ ...? Or? Maybe my edit is wrong? – Gustav Jan 28 '16 at 15:36
  • Yes, thank you. I've edited it now. – Victor Jan 28 '16 at 15:41
  • I'm confused. You have a hard-coded GUID: `VALUES ( NULL,"{266bd115-d5a3-4617-f880-807b30eeda22}"," ...` so how can you expect another value to be present? – Gustav Jan 28 '16 at 15:46
  • 1
    Somewhere along the way the order of *some of the bytes* is getting swapped, so `80F8` becomes `F880`. Nasty. – Gord Thompson Jan 28 '16 at 15:53
  • Gustav - I don't expect another value to be present. It should be the same, but it is not. Something happens during the conversion with mdb-export. – Victor Jan 28 '16 at 16:09
  • Have you checked the results to see if the byte-swapping is consistent? If so, you could always add a step to swap them back. – Gord Thompson Jan 28 '16 at 16:27
  • Yes the byte-swapping is consistent. The problem is I have no idea how to swap them back, my programming skills isn't that good yet.. – Victor Jan 28 '16 at 16:33
  • Is the `GUID` column in the MySQL table a VARCHAR or CHAR column? If so, then you could probably just run an UPDATE query after the data has been imported. – Gord Thompson Jan 28 '16 at 17:40
  • Yes it's a CHAR. I know there is a sql function replace(). But I don't know how to use it in this case. Could you give me a hint on how to use it? Would appreciate very much. – Victor Jan 28 '16 at 17:45

1 Answers1

0

seems like a byte order issue in mdbtools. for a workaround create a small sed script ''mdb_fixguids'', something like

#!/bin/sed -f
s/{\(....\)\(....\)-\(....-....-....-............\)}/{\2\1-\3}/g;
s/{\(........-....-....\)-\(..\)\(..\)-\(..\)\(..\)\(..\)\(..\)\(..\)\(..\)}/{\1-\3\2-\5\4\7\6\9\8}/g

put it into the path and use it in the conversion pipe, something like

./to_mysql.sh accessDb.MDB | mdb_fixguids > data.sql

BTW :) this is the first time I needed all the possible backrefs in sed

guest
  • 1