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

I had to re-prepare the file before entering it into the database. I used the following bash script to remove the trailing spaces from the file. I posted the code in my question in order to be formatted properly. — jeremysawesome, Nov 12 '10 at 21:01

score 10 · Answer 1 · edited Aug 10 '17 at 20:01

10

$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.

edited Aug 10 '17 at 20:01

Brett

19,449
54
157
290

answered Apr 08 '13 at 17:57

crorella

333
3
8

1

hi, does it update the table after insertion of it trims in-memory. I want to use this but not at the cost of update after insertion. – unbesiegbar Jun 23 '15 at 06:45
2

This happens before insertion – crorella May 25 '17 at 17:19

score 2 · Answer 2 · answered Nov 12 '10 at 07:14

2

How about simply trimming the imported data in a second step?

$fix_query = "UPDATE `{$table}` SET LastFieldName = RTRIM(LastFieldName);";

answered Nov 12 '10 at 07:14

Tomalak

332,285
67
532
628

Hi Tomalak, Thank you very much for your help. I realize that you did reply first, and your answer would've been right on. However, I found that I needed to strip the spaces before inserting the data - for some reason the spaces at the end of the file were causing an extra double quotation *"* mark to appear in the field. Thus I had to reprepare the file as ajreal suggested. – jeremysawesome Nov 12 '10 at 21:00

score 2 · Accepted Answer · answered Nov 12 '10 at 07:16

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

answered Nov 12 '10 at 07:16

ajreal

46,720
11
89
119

Thanks for your help ajreal. As you suggested I had to re-prepare the file. – jeremysawesome Nov 12 '10 at 21:01

score 1 · Answer 4 · answered Nov 12 '10 at 07:18

1

use LINES TERMINATED BY '\n'

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

answered Nov 12 '10 at 07:18

ArK

20,698
67
109
136

Cite *"These spaces all occur **before** the new line character `"\n"`"* ;-) – Tomalak Nov 12 '10 at 07:20
it's not space, it's CR symbol. Use "LINES TERMINATED BY '\r\n'" – OZ_ Oct 01 '18 at 16:33

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

4 Answers4

Linked