I am rather new to MySQL, I understand how to load data into a table from a text file that's columns are separated by tabs(white space) by default and I was reading that you specify the column value separator explicitly in the LOAD DATA statement. I've tried a number of combinations but i can't figure out where to specify the new separator?

ex.
mysql> LOAD DATA LOCAL INFILE 'bird.txt' INTO TABLE pet;

would be columns would be separated by tabs by default how would i specify say :'s or something other than tabs?

Thanks,
karl hungus

<pre>
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY '\t']
[[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\' ]
]
[LINES
[STARTING BY '']
[TERMINATED BY '\n']
]
[IGNORE number LINES]
[(col_name,...)]
</PRE>

i suggest you try this:

LOAD DATA LOCAL INFILE 'bird.txt' TERMINATED BY ':' INTO TABLE pet;

when i entered:

LOAD DATA LOCAL INFILE 'bird.txt' TERMINATED BY ':' INTO TABLE pet;

i got this back:

mysql> LOAD DATA LOCAL INFILE 'bird.txt' TERMINATED BY ':' INTO TABLE pet;
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'TERMINATED BY ':' INTO TABLE pet' at line 1

Thank you for your help both of you, combining the syntax information from the first post and the general form information from the second post i was able to specify the separator i wanted to use like this:

LOAD DATA LOCAL INFILE 'bird.txt' INTO TABLE pet fields terminated by ':';

terminated by could be any character instead of the default tab

Thanks,
karl hungus