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?
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.
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:
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?
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:
mysql>LOAD DATA LOCAL INFILE '/path/breakpoint.txt' into table breakpoint;
to:
mysql>\. /path/breakpoint.txt
or:
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)
hello Radoulov
when i used this command
\. /path/breakpoint.txt it gives an error
Failed to open file '/path/breakpoint.txt
and for the next command you have mention please give me an example i m not getting it.
what i have to write after -u and -p?
please guide me
Yes, the file must exists and has to be readable ...
username and password
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
PS:I have posted this already but I think u didn't see it.
Earlier, you said that my code was giving you an error. You didn't say what the error was.
You also didn't answer my question
is this:
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',);
the contents of this file:
path/breakpoint.txt
YES this is the content of that file.
sorry
Hi.
I've just installed mySql to test this.
You cannot use LOAD DATA with insert statements.
If you did what I suggested originally and change the breakpoint file to a delimted file it would work...
id name fractionlength
0 NULL NULL
0 NULL NULL
0 NULL NULL
0 NULL NULL
0 NULL NULL
0 NULL NULL
1 '1AL-1' 0
2 '1AL-2' 0
3 '1AL-3' 0
4 '1AL-4' 0
5 '1AL-5' 0
6 '1AL-1' 0
The first six records are using insert statements, the next 6 from changing your breakpoint.txt file to this:
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,'1AL-1',0.00
Update:
Incidentally I think mySQL is pretty dumb for not giving an error when you tried to use insert statements!
Apart from changing it to delimited file Is there any other way?I have around 300 files to do:(
thanks
I think I gave you two other methods ...
Yes.
Don't use LOAD DATA. You already have the insert statements just run the sql as radoulov suggested.