Re: Another LOAD Infile Problem
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
Re: Another LOAD Infile Problem
Jason Ferguson wrote: The data is split into about 60 files, average file size of 5 MB (varying from 1 to 10 MB). Since there are many files, I'm trying to minimize the required work (if there was just one consolidated file, no problem). The work can be automated easily with the right tools ;-) If you have for example perl installed on your system and the files all have the '.dat' extension, you can use: perl -pi -e 's/unknown/0/gi' *.dat All instances of 'unknown' (without the quotes of course and case insensitive) will be replaced with '0' in all of the .dat files; use different wildcard constructions if your file have other names. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another LOAD Infile Problem
Jason Ferguson wrote: > The data is split into about 60 files, average file size of 5 MB (varying > from 1 to 10 MB). Since there are many files, I'm trying to minimize the > required work (if there was just one consolidated file, no problem). > > Jason Hi Jason If it's not too late (aren't timezones wonderful?) ;). Have you considered using an interim table into which you load your file in its entirety? Load all fields and have each field set to something like CHAR or VARCHAR big enough to accommodate the fields in the file. Then just pick the columns that you are interested in: Then you can do a : INSERT INTO final_table (col_1, col_2..col_n) SELECT col_1, IF(col_3="unknown", 0, col_3) AS col_2 FROM interim_table Also means that you don't have to necessarily upgrade to 5.x (as per your previous problem) - unless you want to, of course ;) Regards Rory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another LOAD Infile Problem
Then you are in for quite a lot of editing work. I've done it a lot myself. Don't expect your project to be easy. Look for automated ways to edit the data according to your needs and the actual table structure. Bob Cochran Jason Ferguson wrote: The data is split into about 60 files, average file size of 5 MB (varying from 1 to 10 MB). Since there are many files, I'm trying to minimize the required work (if there was just one consolidated file, no problem). Jason On 9/26/05, Jasper Bryant-Greene <[EMAIL PROTECTED]> wrote: Jason Ferguson wrote: 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? How about just replace occurrences of the string "UNKNOWN" in the original file with NULL (the logical equivalent) or 0 (if you're using NOT NULL columns) before doing LOAD DATA INFILE? -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- 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: Another LOAD Infile Problem
You'll have to edit your input file. There will always be instances where some field is quirky and you need to fix it/them/entire rows. Don't expect the input file to be perfect. I'd also suggest that you have a test database on a test machine that is devoted entirely to getting your tables set up correctly. It saves a lot of stress by giving you a platform to experiment on. Bob Cochran Jasper Bryant-Greene wrote: Jason Ferguson wrote: 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? How about just replace occurrences of the string "UNKNOWN" in the original file with NULL (the logical equivalent) or 0 (if you're using NOT NULL columns) before doing LOAD DATA INFILE? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another LOAD Infile Problem
The data is split into about 60 files, average file size of 5 MB (varying from 1 to 10 MB). Since there are many files, I'm trying to minimize the required work (if there was just one consolidated file, no problem). Jason On 9/26/05, Jasper Bryant-Greene <[EMAIL PROTECTED]> wrote: > > Jason Ferguson wrote: > > 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? > > How about just replace occurrences of the string "UNKNOWN" in the > original file with NULL (the logical equivalent) or 0 (if you're using > NOT NULL columns) before doing LOAD DATA INFILE? > > -- > Jasper Bryant-Greene > Freelance web developer > http://jasper.bryant-greene.name/ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >
Re: Another LOAD Infile Problem
Jason Ferguson wrote: 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? How about just replace occurrences of the string "UNKNOWN" in the original file with NULL (the logical equivalent) or 0 (if you're using NOT NULL columns) before doing LOAD DATA INFILE? -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Another LOAD Infile Problem
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
