RE: Migrating Access Tables -- Empty Columns, Date and Time
Show warnings is not available until 4.1.x ... The empty field will be imported as that. Empty and not a null value. ... Have you tried importing the data yet to see how the date fields look in MySQL? -Original Message- From: Robert L Cochran To: [EMAIL PROTECTED] Sent: 6/28/04 9:20 PM Subject: Migrating Access Tables -- Empty Columns, Date and Time I'm migrating a Microsoft Access 2002 (Service Pack 3) table constructed by my wife to a corresponding table in MySQL 4.0.20. Some columns in most of the 3000+ rows are empty. Some of these are contiguous empty columns. I don't know if Access considers them NULL or not, but when you export an Access row containing empty columns to a comma separated values file, the empty column will be represented by a sequence of placeholder commas. Here is a part of the first table row exported by Access: "WEEKEND",,8,1,,0,,,"at",,,2/12/1998 0:00:00,11/27/1998 0:00:00,,"MB" Based on recent experience with loading a simpler Access table, these empty columns will be imported as is by both mysqlimport and LOAD DATA LOCAL INFILE, but with warnings. I have 2 questions associated with this: 1) How do I make mysqlimport or LOAD DATA LOCAL tell me the text of each warning? By default they print a summary count of warnings but don't issue actual warning messages. The default log files show nothing. mysqlimport -v does not do it. 2) When consecutive commas (meaning at least 1 empty column, sometimes several) are seen, what does mysqlimport/LOAD DATA do to the corresponding column entrie(s)? Will it set them to NULL? Or to the default specified in the CREATE TABLE statement? Should I explicitly set these to NULL where permitted by the column type? Last of all, look at this date and time stamp exported by Access: ,2/12/1998 0:00:00, Will mysqlimport choke on this, since MySQL likes dates to be in ccyy-mm-dd format? Will I need to reformat the date with a sed script? Thanks Bob Cochran Greenbelt, Maryland, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Migrating Access Tables -- Empty Columns, Date and Time
Why don't you use the export utility in ms access to load the data into mysql. The export will create the table in the database. All you need is a dsn connection and the mysql odbc. Osvaldo Sommer -Original Message- From: Robert L Cochran [mailto:[EMAIL PROTECTED] Sent: Monday, June 28, 2004 8:21 PM To: [EMAIL PROTECTED] Subject: Migrating Access Tables -- Empty Columns, Date and Time I'm migrating a Microsoft Access 2002 (Service Pack 3) table constructed by my wife to a corresponding table in MySQL 4.0.20. Some columns in most of the 3000+ rows are empty. Some of these are contiguous empty columns. I don't know if Access considers them NULL or not, but when you export an Access row containing empty columns to a comma separated values file, the empty column will be represented by a sequence of placeholder commas. Here is a part of the first table row exported by Access: "WEEKEND",,8,1,,0,,,"at",,,2/12/1998 0:00:00,11/27/1998 0:00:00,,"MB" Based on recent experience with loading a simpler Access table, these empty columns will be imported as is by both mysqlimport and LOAD DATA LOCAL INFILE, but with warnings. I have 2 questions associated with this: 1) How do I make mysqlimport or LOAD DATA LOCAL tell me the text of each warning? By default they print a summary count of warnings but don't issue actual warning messages. The default log files show nothing. mysqlimport -v does not do it. 2) When consecutive commas (meaning at least 1 empty column, sometimes several) are seen, what does mysqlimport/LOAD DATA do to the corresponding column entrie(s)? Will it set them to NULL? Or to the default specified in the CREATE TABLE statement? Should I explicitly set these to NULL where permitted by the column type? Last of all, look at this date and time stamp exported by Access: ,2/12/1998 0:00:00, Will mysqlimport choke on this, since MySQL likes dates to be in ccyy-mm-dd format? Will I need to reformat the date with a sed script? Thanks Bob Cochran Greenbelt, Maryland, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 6/27/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 6/27/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrating Access Tables -- Empty Columns, Date and Time
Bob My versions are Access 2000 and MySQL 4.0.20. Here's what I do. When exporting my Access table, I choose the file type Text Files. Then, on the next dialog box, make sure to click the Advanced button to get to the formatting information. Change the date order to YMD and the date delimiter to "-". That will take care of the date. As for the NULL value. The table into which I'm importing has columns defined to allow NULLs, but when I import the Access table with empty columns, like you describe, it doesn't force a null -- it just leaves the value blank. It doesn't cause any problems, but if you want to force a NULL then I guess you could rework the exported file to state NULL for the empty fields you want to read as such. Wes On Jun 28, 2004, at 10:20 PM, Robert L Cochran wrote: 2) When consecutive commas (meaning at least 1 empty column, sometimes several) are seen, what does mysqlimport/LOAD DATA do to the corresponding column entrie(s)? Will it set them to NULL? Or to the default specified in the CREATE TABLE statement? Should I explicitly set these to NULL where permitted by the column type? Last of all, look at this date and time stamp exported by Access: ,2/12/1998 0:00:00, Will mysqlimport choke on this, since MySQL likes dates to be in ccyy-mm-dd format? Will I need to reformat the date with a sed script? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Migrating Access Tables -- Empty Columns, Date and Time
I'm migrating a Microsoft Access 2002 (Service Pack 3) table constructed by my wife to a corresponding table in MySQL 4.0.20. Some columns in most of the 3000+ rows are empty. Some of these are contiguous empty columns. I don't know if Access considers them NULL or not, but when you export an Access row containing empty columns to a comma separated values file, the empty column will be represented by a sequence of placeholder commas. Here is a part of the first table row exported by Access: "WEEKEND",,8,1,,0,,,"at",,,2/12/1998 0:00:00,11/27/1998 0:00:00,,"MB" Based on recent experience with loading a simpler Access table, these empty columns will be imported as is by both mysqlimport and LOAD DATA LOCAL INFILE, but with warnings. I have 2 questions associated with this: 1) How do I make mysqlimport or LOAD DATA LOCAL tell me the text of each warning? By default they print a summary count of warnings but don't issue actual warning messages. The default log files show nothing. mysqlimport -v does not do it. 2) When consecutive commas (meaning at least 1 empty column, sometimes several) are seen, what does mysqlimport/LOAD DATA do to the corresponding column entrie(s)? Will it set them to NULL? Or to the default specified in the CREATE TABLE statement? Should I explicitly set these to NULL where permitted by the column type? Last of all, look at this date and time stamp exported by Access: ,2/12/1998 0:00:00, Will mysqlimport choke on this, since MySQL likes dates to be in ccyy-mm-dd format? Will I need to reformat the date with a sed script? Thanks Bob Cochran Greenbelt, Maryland, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
