Mysql: How can I use RTRIM in my LOAD DATA INFILE query?

Question

In my code I have a query that looks like this:

$load_query = "LOAD DATA LOCAL INFILE '{$file}' INTO TABLE `{$table}`
      FIELDS TERMINATED BY ',' ENCLOSED BY '\"';";

Here is an example row included in the file that I am trying to load:

"MC318199","06160","1","P","00750","00000","TN598792","04/16/2009","91X"                 

You will notice that at the end of the example row there are quite a few spaces. These spaces all occur before the new line character "\n" that terminates the line. The problem is that these spaces get entered into the database.

How can I remove the additional spaces when running the LOAD DATA INFILE command? Is there a way to use RTRIM?

Edit

As ajreal suggested I had to re-prepare the file. After the file was prepared it was inserted into the database correctly. I modified the bash script found at: http://gabeanderson.com/2008/02/01/unixlinux-find-replace-in-multiple-files/ to accomplish this. The code is shown below:

#!/bin/bash 
for fl in *.txt; do
  mv $fl $fl.old
  sed 's/[ \t]*$//' $fl.old > $fl
  rm -f $fl.old
done

Answer 1

$load_query = "LOAD DATA LOCAL INFILE '{$file}' INTO TABLE `{$table}`
      FIELDS TERMINATED BY ',' ENCLOSED BY '\"' (col1, col2, ... , @col_with_spaces)
SET col_with_spaces = RTRIM(@col_with_spaces);";

That way you won't need to pre-process the file nor create other updates on the table.

Answer 2

dun think too complicate, right after u loaded the data
update $table set $last_column=rtrim($last_column);

or manually remove the space in the $file using vi (or any editor)

or re-preapare the $file