LOAD DATA INFILE with whitespace between fields 1

[UltraSmooth]

I have a txt file that I need to import regularly into a database. The fields are separated by '|' but the problem is that there can be whitespace after the field and the length of this whitespace can vary. The reason being is the txt field is created by a report engine that pads any field data that does not take up the maximum characters of the field definition.

If the field type is Text and the field has a lenght of 10, then any field data that does not have 10 characters will be padded at the end with whitespace for the remaining characters.

My data file can look like this (- represents whitespace)
H645464.34|Blue--
H3435.23--|Red---
H454 343--|Orange

I do have a small amount of control over the format of the txt file. I can surround the field data by quotes but whitespace can still come between the closing quote and the field terminator or the endOfLine character, ie.
"H3435.23"--|"Red"--

I could also have the quotes outside of the padded whitespace,ie.
"H3435.23 "|"Red--"

How can I tell the LOAD DATA INFILE function to ignore this whitespace? Any help would be appreciated.

 

[KarveR]

LOAD DATA INFILE 'file_name.txt' INTO TABLE tbl_name FIELDS TERMINATED BY '|'

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[sleipnir214]

Where does this file reside? If it's on the same filesystem as the server, drop the "INFILE" keyword. That is used to transmit the file to the server through the server connection.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[UltraSmooth]

Thanks KarveR, I was looking for a complex solution to an easier problem. It never camed to me to just accept mysql's defaults for the LOAD DATA function and see what happens, I assumed I had to do something trick.

Thanks again.

 

[sleipnir214]

I'm sorry, all. I'm an idiot.

It's not the "INFILE" keyword...it's the "LOCAL" keyword.

Excuse my while I got find some more caffeine.

Want the best answers? Ask the best questions!

TANSTAAFL!!