Jiansen Lu
SQL Script to Load an external csv file in tables
November 19th 2010 07:49
Repost by Dr.Code:
1. Suppose we have a table users:
CREATE TABLE `users` ( `id` INT UNSIGNED NOT NULL
AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 20 ) NOT NULL ) ENGINE = MYISAM;
2. We have a csv file 'c:\Users\jiansen\jiansen_dir \mysqlfile.csv' as:
Tom,1996-12-29
Jerry,1985-12-29
3. We use the following command to insert the csv file in table users in SQL:
LOAD DATA INFILE 'c:/Users/jiansen/jiansen_dir /mysqlfile.csv' INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
4. If we use SQL server, we use:
BULK INSERT users
FROM 'c:\Users\jiansen\jiansen_dir \mysqlfile.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = 'n'
)
1. Suppose we have a table users:
CREATE TABLE `users` ( `id` INT UNSIGNED NOT NULL
AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 20 ) NOT NULL ) ENGINE = MYISAM;
2. We have a csv file 'c:\Users\jiansen\jiansen_dir \mysqlfile.csv' as:
Tom,1996-12-29
Jerry,1985-12-29
3. We use the following command to insert the csv file in table users in SQL:
LOAD DATA INFILE 'c:/Users/jiansen/jiansen_dir /mysqlfile.csv' INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
4. If we use SQL server, we use:
BULK INSERT users
FROM 'c:\Users\jiansen\jiansen_dir \mysqlfile.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = 'n'
)
| 17 |
| Vote |
Add To: 
subscribe to this blog
