Load Data Infile

[Nickosssss]

Hello there,
i am trying to insert some data from a txt file but doesnt seem to work

the table is :
CREATE TABLE student
(
stud_id INT UNSIGNED NOT NULL,
PRIMARY KEY (stud_id),

stud_fname VARCHAR(25) NOT NULL,
stud_lname VARCHAR(75) NOT NULL,
email VARCHAR(25) NOT NULL,
status ENUM ('O', 'R') NOT NULL,
level ENUM ('A', 'B', 'C') NOT NULL

)ENGINE = InnoDB;

the command i give to mysql is :

load data infile "some.txt" into table student (stud_id, stud......);

but i think the problem is with the way i write the some.txt syntax....

''2222222 '\t' Nick '\t' Gem '\t' hot '\t' O '\t' A '\t' '\n'

should it look like that?

thank u

[bpat1434]

Well, have you read the mySQL manual about this syntax? If not, I would.

LOAD DATA INFILE Syntax

~Brett

[Nickosssss]

well thanks but this is the site i took the syntax from but still does not work...do u might have any file.txt sample to show me?

thank you

[bpat1434]

Did you read everything? Like the fact that you can define how the line terminates, how they are seperated?

LOAD DATA INFILE 'some.txt' INTO TABLE student (stud_id, stud_fname, stud_lname, email, status, level) FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '\"\"' ESCAPED BY '\\' LINES STARTING BY '%%' TERMINATED BY '\n';

some.txt
%%"22222"\t"Nick"\t"Gem"\t"[e-mail]"\t"O"\t"a"
%%"22223"\t"Josh"\t"Gold"\t"[e-mail]"\t"O"\t"a"

~Brett

[Nickosssss]

well i didnt see the %% symbol. I guess ur must work fine....thx u a lot mate...i am going to try it soon

🙂