How to export from Access to Mysql

And convert Yes/No check boxes properly.

[HughMungus]

I have an access database and some of the columns are Yes/No checkboxes (that is, in Access they appear in the "Datasheet View" as checkboxes).

When I exported the database to a .csv file, I get 0's for unchecked and 1's for checked boxes. In my Mysql table, I'm using the enumerated data type for the corresponding fields ('y','n'). It appears that what's happening is that when I upload the .csv file to Mysql, I get the first enumerated selection on all fields imported.

So what is the right way to go from Access Yes/No checkboxes to the Mysql enumerated data type?

[coopster]

when I upload the .csv file to Mysql, I get the first enumerated selection on all fields imported.

So you are getting a 'y' for every entry? That's odd, I would have expected you to get the empty string (numerical value zero) for every entry since neither '0' nor '1' are present in the list of allowed values. I'm assuming you are using LOAD DATA to get the table populated?

Anyway, I guess one of the easiest ways to do this would be to CREATE your TABLE originally with the column set to, oh, perhaps type BOOL. Import your data, then ALTER TABLE to MODIFY the column to type ENUM. Example:

CREATE TABLE testenum (nbr TINYINT UNSIGNED, val BOOL); 
INSERT INTO testenum VALUES (1, '0'); 
INSERT INTO testenum VALUES (2, '1'); 
INSERT INTO testenum VALUES (3, ''); 
SELECT nbr, val FROM testenum; 
+-----+-----+ 
¦ nbr ¦ val ¦ 
+-----+-----+ 
¦  1  ¦  0  ¦ 
¦  2  ¦  1  ¦ 
¦  3  ¦  0  ¦ 
+------+----+ 
SELECT nbr, val+0 FROM testenum; 
+------+-------+ 
¦ nbr  ¦ val+0 ¦ 
+------+-------+ 
¦  1   ¦   0   ¦ 
¦  2   ¦   1   ¦ 
¦  3   ¦   0   ¦ 
+------+-------+ 
ALTER TABLE testenum MODIFY val ENUM('y','n') NOT NULL; 
SELECT nbr, val FROM testenum; 
+-----+-----+ 
¦ nbr ¦ val ¦ 
+-----+-----+ 
¦  1  ¦     ¦ 
¦  2  ¦  y  ¦ 
¦  3  ¦     ¦ 
+-----+-----+ 
SELECT nbr, val+0 FROM testenum; 
+------+-------+ 
¦ nbr  ¦ val+0 ¦ 
+------+-------+ 
¦  1   ¦   0   ¦ 
¦  2   ¦   1   ¦ 
¦  3   ¦   0   ¦ 
+------+-------+

[HughMungus]

Thanks Coop. I ended up just changing my 'y','n' fields to '0','1' fields and they transferred right over like they should. I'll keep your answer on file for future reference, however. And yeah, I guess I should be using booleans...

[coopster]

No, no, I didn't mean you should be using BOOL. What I meant was that would be an optional way for you to get the data over and into the ENUM values that you truly desire. The snippet there was a quick test I ran to make sure it worked as planned. It did ;)

[DaButcher]

btw. you could have tried saving an export of the access in CSV or whatever and simply replaced the occurrances, and then imported the file in mySQL.