Importing Data From Access

[Marc]

Hi,

I'm transferring an application from a access-db to mysql as db server. For importing the data ans structure i want to use SQLyog. The only problem i have now is, that serveral tables have boolean fields (yes/no in access). SQLyog is importing them as char(1) fields but i want them to be tinyints (0/1).

I already tried some other tools for importing data but SQLyog seems to be much easier. The application is also already changed to use tinyint as boolean field.

Is there a way to tell SQLyog that i dont want the boolean fields to be single chars but tinyints and how the values shall be imported?

Editing each mapping is not really an option, since there are more than 30 tables.

Is there maybe some “global mapping” file where there is stored how SQLyog shall handle field types when importing a db-structure?

[Ritesh]

Currently there is no global option to do that. I have added it in the TO-DO list.

BTW, is there any special reason to use tinyint instead of char(1)?

MySQL considers TINYINT as a numeric datatype whereas CHAR is a text datatype.

[Marc]

Ritesh wrote on Aug 16 2005, 09:25 AM:

BTW, is there any special reason to use tinyint instead of char(1)?

The special reason is, that 0/1 is the most common way to represent boolean values. And mysql itself represents boolean values as 0 or 1. Only Access sees it as Y/N. That was the main reason i decided to change it to tinyint.

And all of that was done before i dicovered that i can use SQLyog for importing. We bought SQLyog for another reason first 😉

EDIT:

And by the way, i found a way to do that now. I already had the database on a test server. Just the data there is not the current data. We now have a server that shall be the final server soon and i wanted to test it and import the current data there. I now used the structur synchronistion to get the structure from the other server and am now importing the data. This way the boolean fields seem to be interpreted correctly.

[peterlaursen]

there are lots of workarounds, I believe

for instance this one

(b is tinyint, c is char)

ALTER TABLE and create tinyint field b

update tablename1 set b = 0 where c = 'n';

update tablename1 set b = 1 where c = 'y';

ALTER TABLE and delete charfield c

you can run this statement from “Notifications Service”., and even run it in batch-mode like

sja importjob.xml

sja convertjob.xml

[Author]

Posts