UNIX for Dummies Questions & Answers

hello,
when I load a data from text file all the values become NULL in the table.
Please help me with this problem.

Thanks

sheen

I'm guessing that you'll have to give us a lot more information...

i.e.

which database?
how you get the text from the file into insert statements?
basically, what you have tried - show us scripts. the text file (or part of)?
etc, etc.

hello scottn,
i have made a wheat database.which contain different tables each tables contain different data for example we say there is table called 'breakpoint' so i have converted this table into sql format like
insert into breakpoint(id,name,fractionlength) values(1,'1AL-1',);
insert into breakpoint(id,name,fractionlength) values(2,'1AL-2',);
insert into breakpoint(id,name,fractionlength) values(3,'1AL-3',);
insert into breakpoint(id,name,fractionlength) values(4,'1AL-4',);
insert into breakpoint(id,name,fractionlength) values(5,'1AL-5',);
insert into breakpoint(id,name,fractionlength) values(6,'1AS-1',);

on sql server i have created table breakpoint using command
mysql>CREATE TABLE breakpoint(id int,name char(20),fractionlength float);

now i want to load values to this tables so i uses a command
mysql>LOAD DATA LOCAL INFILE '/path/breakpoint.txt' into table breakpoint;

when i use this command instead of loading values into tables it gives NULL in each column.

Please guide me where i m going wrong.

Thanks

Hi.

is this the contents of breakpoint.txt?

Code:

insert into breakpoint(id,name,fractionlength) values(1,'1AL-1',);
insert into breakpoint(id,name,fractionlength) values(2,'1AL-2',);
insert into breakpoint(id,name,fractionlength) values(3,'1AL-3',);
insert into breakpoint(id,name,fractionlength) values(4,'1AL-4',);
insert into breakpoint(id,name,fractionlength) values(5,'1AL-5',);
insert into breakpoint(id,name,fractionlength) values(6,'1AS-1',);

LOAD DATA is for loading data in delimted format
ie.

Code:

1,'1AL-1',0.00
2,'1AL-2',0.00
3,'1AL-3'0.00
4,'1AL-4',0.00
5,'1AL-5',0.00
6,'1AS-1',0.00

I found this example on the web:

Code:

LOAD DATA LOCAL INFILE '/importfile.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3);

But if you already have the insert statements why not just run the script?

Code:

mySql> @/path/breakpoint.txt
mySql> commit;

(assuming that breakpoint.txt is the insert statements you mention.)

(PS: I'm not so familiar with mySql so sorry if the sytax is different from Oracle!)

Change this line:

Code:

mysql>LOAD DATA LOCAL INFILE '/path/breakpoint.txt' into table breakpoint;

to:

Code:

mysql>\. /path/breakpoint.txt

or:

Code:

mysql -u ... -p... <database_name> < /path/breakpoint.txt

Hello Scottn,
Well your code is giving me error.
I used LOAD DATA LOCAL INFILE command on a text file and when I want to view data in the table using command
SELECT * FROM breakpoint;

It gives result like

id alleleid type remark
0 NULL NULL NULL
0 NULL NULL NULL
0 NULL NULL NULL
0 NULL NULL NULL
0 NULL NULL NULL

I have loaded the text file then why is it giving NULL values?

this is my main question.

Thanks

Show the full command you use and the text file (or a sample of).

I don't know the semantics of mySQL. Do you have to commit this data after it's entered, or is that done automatically? (i.e. the default behaviour of SQL*Plus is to commit when you exit)