LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 05-15-2004, 08:59 PM   #1
joelhop
Member
 
Registered: Mar 2004
Location: Pennsylvania::USA
Distribution: Fedora Core 6
Posts: 100

Rep: Reputation: 15
Question MySQL Load Data Separators


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
 
Old 05-15-2004, 09:32 PM   #2
AltF4
Member
 
Registered: Sep 2002
Location: .at
Distribution: SuSE, Knoppix
Posts: 532

Rep: Reputation: 31
<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>
 
Old 05-15-2004, 09:34 PM   #3
AltF4
Member
 
Registered: Sep 2002
Location: .at
Distribution: SuSE, Knoppix
Posts: 532

Rep: Reputation: 31
i suggest you try this:

LOAD DATA LOCAL INFILE 'bird.txt' TERMINATED BY ':' INTO TABLE pet;
 
Old 05-15-2004, 10:12 PM   #4
joelhop
Member
 
Registered: Mar 2004
Location: Pennsylvania::USA
Distribution: Fedora Core 6
Posts: 100

Original Poster
Rep: Reputation: 15
Question Didn't work

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
 
Old 05-15-2004, 10:26 PM   #5
joelhop
Member
 
Registered: Mar 2004
Location: Pennsylvania::USA
Distribution: Fedora Core 6
Posts: 100

Original Poster
Rep: Reputation: 15
Smile Got it Working!

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
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
quick hand with awk multiple field separators pld Programming 10 05-28-2010 07:51 AM
MYSQL data dir android6011 Linux - Software 22 10-10-2005 09:32 PM
mysql - load data warning prabhatsoni Linux - Software 0 06-16-2005 06:08 AM
LOAD DATA INFILE error verokard Linux - Newbie 0 07-13-2003 11:02 PM
trouble installing suse linux [There is not enough memory to load all data] rishineedsajob Linux - Newbie 1 03-23-2003 08:18 PM


All times are GMT -5. The time now is 01:49 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration