Jason Ferguson <[EMAIL PROTECTED]> wrote on 09/26/2005 10:58:02 PM: > Many thanks for the earlier response to why LOAD DATA INFILE wasnt working > for me. However, another problem has appeared. > > In the file I am reading, 2 of the fields are SUPPOSED to be float values. > However, in several places, they are set to "UNKNOWN". This seems to cause > LOAD to abort. > > Is there a way for me to tell it to ignore this problem and just use the > default value for the column? > > Jason
One option is to stage that data into a table that has those columns defined as varchars. Then copy the data from there into the original destination table. This is also a way to get around your "ignored columns" problem of your previous post. Import everything into a flexible staging table (mostly varchars) that will accept the data. Then, only migrate from your staging table those columns you actually wanted in your data. Truncate or drop your staging table when you finish each batch. Sure it takes up more room but you can scrub your data in MySQL which may be easier for you to handle than trying to scrub the raw text files. I never take raw text data and merge it into a production database in one step. This is how I screen out bad inputs, malformed text, and otherwize invalid data. It sometimes takes 4 or 5 times to get the raw data into the staging table (depending on how messed up the raw data is). Once it's there, it's much easier for me to screen and fix. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
