mysql load csv，txt

-- MYSQL   LOAD DATA INFILE命令可以把csv平面文件中的数据导入到数据库中。

-- linux下：

LOAD DATA INFILE '/home/test/dump/ip_location.csv'
 INTO TABLE ip_location
 CHARACTER SET utf8
 FIELDS TERMINATED BY ',' ENCLOSED BY '"';
-- CHARACTER SET ：mysql字符集，一定要加上，免去乱码麻烦
-- INTO TABLE ：导入到哪个表
-- FIELDS TERMINATED BY ：以什么作为分割符
--  ENCLOSED BY ：被什么包围



-- windows：

LOAD DATA INFILE "d:/insert_data.csv"
 REPLACE INTO TABLE DEMO
 CHARACTER SET gb2312
 FIELDS TERMINATED BY "," ENCLOSED BY ""
 LINES TERMINATED BY "\r\n";
-- LINES TERMINATED BY：这个与linux不同，以什么作为一行的结尾。
-- txt  或是linux
TRUNCATE TABLE adx_analysis.creative_category_imp_cli_tmp ;
LOAD DATA LOCAL INFILE 'D:\\analyst20151231.txt' INTO TABLE  adx_analysis.creative_category_imp_cli_tmp CHARACTER SET utf8; 

 LOAD DATA INFILE语法
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char' ]
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...)]



	
	
	
-- 2017-06-14 ===================================================================================

FLUSH LOGS;
DROP TABLE IF EXISTS svr.cctv201615 ;
CREATE TABLE svr.cctv201615 (

col1 VARCHAR(128) ,                  
col2 VARCHAR(128) ,                  
col3 VARCHAR(128) ,                  
col4 VARCHAR(128) ,                  
col5 VARCHAR(128) ,                  
col6 VARCHAR(128) ,         
id INT AUTO_INCREMENT PRIMARY KEY ,         
col7 VARCHAR(128) ,                  
col8 VARCHAR(128) ,                  
col9 VARCHAR(128) ,                  
col10 VARCHAR(128) ,     `dt_update` TIMESTAMP  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,                
col11 VARCHAR(128) ,                 
col12 VARCHAR(128) ,                 
col13 VARCHAR(128) ,                 
col14 VARCHAR(128) ) ;

LOAD DATA LOCAL INFILE "D:/excel/cctv201612.txt"  
REPLACE INTO TABLE svr.cctv201615  
CHARACTER SET utf8  
FIELDS TERMINATED BY "|" ENCLOSED BY ""  
LINES TERMINATED BY "\n"   
IGNORE 1 LINES
(col1 ,col2 ,col3 ,col4 ,col5 ,col6 ,col7 ,col8 ,col9 ,col10,col11,col12,col13,col14 )
;

SELECT * FROM svr.cctv201615 ;

FLUSH LOGS;

TRUNCATE table hx.user_property_index_data_log;

LOAD DATA LOCAL INFILE '/Users/tata/Downloads/chenenhui20180320102301.csv'

INTO TABLE hx.user_property_index_data_log

CHARACTER SET utf8

FIELDS TERMINATED BY ',' #VENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 LINES (property_index_id,user_id,vl)

set create_date = now()

;

 

 

 

# 从工具中下载后将文件载入2018-05-02

LOAD DATA LOCAL INFILE '/Downloads/job_id=6828422_dir/job_id=6828422ac.csv'
INTO TABLE dbo.tmp_operation_factTest
CHARACTER SET gbk
FIELDS TERMINATED BY ',' ENCLOSED BY ""
LINES TERMINATED BY '\n' #'\r\nz
#IGNORE 1 LINES (col1 ,col2 ,col3 ,col4 ,col5 ,col6 ,col7 ,col8 ,col9 ,col10,col11,col12,col13,col14 )
;

TRUNCATE TABLE `pro_company`;

LOAD DATA LOCAL INFILE  "C:/Users/admin/Desktop/com.csv"
INTO TABLE pro_company   CHARACTER SET gbk
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(insure_company_code, com_code, com_name,nub,com_name_standard,com_code_standard);
 
SELECT * FROM `pro_company`;