Page 1 of 1

LOAD DATA help (I need if/elseif/else syntax)

Posted: Wed Apr 11, 2007 6:32 pm
by Luke
I have this query I am working on to import data from a text file into my mysql database. This is what I have so far:

Code: Select all

LOAD DATA LOCAL INFILE 'C:\\htdocs\\members.txt'
  REPLACE
  INTO TABLE `users`
  FIELDS
        TERMINATED BY '\t'
  (@id, username, password, @passwordHint, first_name, last_name, middle_name, nick_name, spouse_name, @gender, @phone, @phone_b, @phone_c, @phone_type_a, @phone_type_b, @phone_type_c, @fax_a, @fax_b, @fax_type_a, @fax_type_b, @email, @email_b, @email_type_a, @email_type_b, @pref_contact, @date_join, @date_born, @date_resign, @address_a, @address_b, @city_a, @city_b, @zip_a, @zip_b, @state_a, @state_b, @address_type_a, @address_type_b, @image_ad, @image_name, business_website, @website_b, business_name, @business_classification, business_keywords, business_description, badge_number, @badge_plate, @full_perc, @badge_color, @team_id, @team_admin, @team_captain, @bio)
  SET
	gender = IF(@gender = 1, 'm', 'f')
Now I need a statement sort of like this (nested ternary):

Code: Select all

home_phone = IF(@phone_type_a = 'Home', @phone_type_a, IF(@phone_tybe_b = 'Home', @phone_type_b, IF(@phone_type_c = 'Home', @phone_type_c, null)))
I tried this but it does not work. Is there a way to do what I want?

Posted: Wed Apr 11, 2007 6:45 pm
by Luke
looks like it was due to a syntax error (forgot a comma)

Code: Select all

LOAD DATA LOCAL INFILE 'C:\\htdocs\\members.txt'
  REPLACE
  INTO TABLE `users`
  FIELDS
        TERMINATED BY '\t'
  (@id, username, password, @passwordHint, first_name, last_name, middle_name, nick_name, spouse_name, @gender, @phone, @phone_b, @phone_c, @phone_type_a, @phone_type_b, @phone_type_c, @fax_a, @fax_b, @fax_type_a, @fax_type_b, @email, @email_b, @email_type_a, @email_type_b, @pref_contact, @date_join, @date_born, @date_resign, @address_a, @address_b, @city_a, @city_b, @zip_a, @zip_b, @state_a, @state_b, @address_type_a, @address_type_b, @image_ad, @image_name, business_website, @website_b, business_name, @business_classification, business_keywords, business_description, badge_number, @badge_plate, @full_perc, @badge_color, @team_id, @team_admin, @team_captain, @bio)
  SET
	gender = IF(@gender = 1, 'm', 'f'),
        home_phone = IF(@phone_type_a = 'Home', @phone,
			IF(@phone_tybe_b = 'Home', @phone_b, 
				IF(@phone_type_c = 'Home', @phone_c, null)
			)
		     )

Posted: Wed Apr 11, 2007 7:27 pm
by Luke
Alright, a related question:
how would I remove all dashes/spaces/anything except numbers from the phone number? I looked in the string functions for mysql, but nothing jumps out at me except regexp and I don't really know how I would use that.

EDIT: Another one:
How would I turn a date formatted like mm/dd/yyyy to yyyy-mm-dd?

Posted: Thu Apr 12, 2007 11:12 am
by mpeacock
The Ninja Space Goat wrote:Alright, a related question:
how would I remove all dashes/spaces/anything except numbers from the phone number? I looked in the string functions for mysql, but nothing jumps out at me except regexp and I don't really know how I would use that.

EDIT: Another one:
How would I turn a date formatted like mm/dd/yyyy to yyyy-mm-dd?
You probably have a couple options. First, I'm not sure if you could do this during the load, but it would be trivial to either preprocess the input file before the load or load the data as is, and postprocess the various fields in your database. Of course, you may be stuck preprocessing since your load might not work if the date format is whacked.

The main benefit of preprocessing is that it would keep your loader script as simple as possible.

Something like this might work for you:

Code: Select all

$in = fopen('raw.data', 'r') or die($php_errormsg);
$out = fopen('cleaned.data', 'w') or die($php_errormsg);

while (! feof($in) ) 
{
    if ($line = fgets($in, 4096))
    {
       $fields = preg_split('\t', $line);
       // now process each field as needed
       $outFields = convert($fileds);
       fwrite($out, implode('\t', $outFields) . "\n");

    }


}

fclose($in);
fclose($out);

function convert($someArray)
{
   
// Fix the elements you need fixed
  while (list($foo, $bar, $baz, $theDate) = each($someArray))
 {
   $theDate = convertDate($theDate);
   ...
 }

 return $someArray;


}


function convertDate($date)
{
   return date("Y-m-d H:i:s", strtotime($date) );
}
Cheers

Posted: Thu Apr 12, 2007 11:55 am
by RobertGonzalez
Use the mysql date functions to help manage dates. As for handling the phone number, you could use the REPLACE() function.

http://dev.mysql.com/doc/refman/5.1/en/ ... tions.html
http://dev.mysql.com/doc/refman/5.1/en/ ... on_replace