-
LOAD DATA into Mysql
Hi all,
I am new in mysql , I want to load file into table , but always got error. wha tis wrong??
File 'c\pet.txt' not found (Errcode: 2)
mysql> LOAD DATA LOCAL INFILE '~/pets.txt' INTO TABLE pet;
ERROR:
File '~\pets.txt' not found (Errcode: 2)
mysql>
Thank you
________
SIXTH-GENERATION TAURUS
Last edited by sql; 03-06-2011 at 01:42 AM.
-
Give it a try with the physical path like..
LOAD DATA LOCAL INFILE 'c:/pets.txt' INTO TABLE pet;
Adding this line would be helpfull
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
(in this case, we have : "val1","val2",...)
-
Actually: c:\pets.txt is the properly formatted path
-
Thank's Rawhide, but...
I don't know if it is a bug from MYSQL, the properly format path won't work.
If I m wong please, let me know.
-
Same error?
Are you sure that you have the file name and path correct?
-
Hi all,
Thank you for the reply. I got error like
mysql> show tables;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| pet |
+---------------------+
1 row in set (0.00 sec)
mysql> LOAD DATA LOCAL INFILE 'c:\pet.txt' into table pet;
ERROR:
File 'cet.txt' not found (Errcode: 2)
mysql> LOAD DATA LOCAL INFILE 'c:\pet.txt' into table pet; lines TERMINATED BY
'\r\n';
ERROR:
File 'cet.txt' not found (Errcode: 2)
-
Hi all;
I got it at Win xp should be
mysql> LOAD DATA LOCAL INFILE "C:/pet.txt" into table pet;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 12
but I am not sure about the form in text file , my txt file is Whistler, Gwen, bird, \N, 1997-12-09, \N
Fluffy, Harold, cat, f, 1993-02-04, \N
but after I load them into databae I got
mysql> select* from pet;
+----------------------+-------+---------+------+-------+-------+
| name | owner | species | sex | birth | death |
+----------------------+-------+---------+------+-------+-------+
| Whistler, Gwen, bi | NULL | NULL | NULL | NULL | NULL |
| Fluffy, Harold, ca | NULL | NULL | NULL | NULL | NULL |
+----------------------+-------+---------+------+-------+-------+
what form I should use in my text file??
Thank you
________
OG KUSH PICTURES
Last edited by sql; 03-06-2011 at 01:42 AM.
-
Could you send the pet's description? (the table)
-
Like OracleDB said above, you need to include: FIELDS TERMINATED BY ','
-
Hi all,
the only line is correct is direct insert into table , the recode load from txt file is miss, why??
mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.02 sec)
mysql> select* from pet;
+----------------------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------------------+-------+---------+------+------------+-------+
| Whistler, Gwen, bi | NULL | NULL | NULL | NULL | NULL |
| Fluffy, Harold, ca | NULL | NULL | NULL | NULL | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
| Whistler, Gwen, bi | NULL | NULL | NULL | NULL | NULL |
| Fluffy, Harold, ca | NULL | NULL | NULL | NULL | NULL |
| Whistler, Gwen, bi | NULL | NULL | NULL | NULL | NULL |
| Fluffy, Harold, ca | NULL | NULL | NULL | NULL | NULL |
| Whistler, Gwen, bi | NULL | NULL | NULL | NULL | NULL |
| Fluffy, Harold, ca | NULL | NULL | NULL | NULL | NULL |
| Whistler, Gwen, bi | NULL | NULL | NULL | NULL | NULL |
| Fluffy, Harold, ca | NULL | NULL | NULL | NULL | NULL |
+----------------------+-------+---------+------+------------+-------+
11 rows in set (0.03 sec)
________
Bmw e39
Last edited by sql; 03-06-2011 at 01:42 AM.
-
You need to
1)modify the column SEX, because in the "pet.txt" the sex field contains 2-char valus (/N)
2) complete the command LOAD DATA... with the line(FIELDS TERMINATED BY ',').
Give it a try with:
alter table pet modify sex char(2);
LOAD DATA LOCAL INFILE "C:/pet.txt" into table pet
FIELDS TERMINATED BY ',';
-
Hi friends,
Thank you, it work now. by the way can we load file into MS sql ?
Thank you
________
Vaporizer
Last edited by sql; 03-06-2011 at 01:42 AM.
-
Sure can, you can use BCP (command line utility), DTS (graphical interface utility), or create an ODBC connection to the file and insert it with a query. The simplest way would be to right click on the pets table in Enterprise Manager, select All Tasks, click on Import, and follow the DTS wizard.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|