# Database Discussions > MySQL >  LOAD DATA into Mysql

## sql

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

----------


## oraclable

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",...)

----------


## Rawhide

Actually: c:\pets.txt is the properly formatted path

----------


## oraclable

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.

----------


## Rawhide

Same error?

Are you sure that you have the file name and path correct?

----------


## sql

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 'c :Stick Out Tongue: et.txt' not found (Errcode: 2)
mysql>  LOAD DATA LOCAL INFILE 'c:\pet.txt' into table pet; lines TERMINATED BY
'\r\n';
ERROR:
File 'c :Stick Out Tongue: et.txt' not found (Errcode: 2)

----------


## sql

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

----------


## oraclable

Could you send the pet's description? (the table)

----------


## Rawhide

Like OracleDB said above, you need to include:  FIELDS TERMINATED BY ','

----------


## sql

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

----------


## oraclable

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 ',';

----------


## sql

Hi friends,
 Thank you, it work now. by the way can we load file into MS sql ?
Thank you
________
Vaporizer

----------


## Rawhide

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.

----------

