{"id":5631,"date":"2016-02-15T11:51:19","date_gmt":"2016-02-15T09:51:19","guid":{"rendered":"http:\/\/help.superhosting.bg\/en\/?p=5631"},"modified":"2022-04-02T12:17:29","modified_gmt":"2022-04-02T09:17:29","slug":"mysql-import-txt-load-data-local-infile","status":"publish","type":"ht_kb","link":"https:\/\/help.superhosting.bg\/en\/mysql-import-txt-load-data-local-infile.html","title":{"rendered":"How to Import Data From Text File Using MySQL Query"},"content":{"rendered":"\n

If you need to import data from text file (.txt, .csv, …) to a database table, you should use LOAD DATA LOCAL INFILE<\/a><\/strong> instead of LOAD DATA INFILE.<\/p>\n\n\n\n

For example:<\/strong><\/em><\/p>\n\n\n\n

LOAD DATA LOCAL INFILE '\/home\/cpaneluser\/data.txt' INTO TABLE Data;<\/code><\/pre>\n\n\n\n
LOAD DATA LOCAL INFILE ‘file-path<\/strong>‘ INTO TABLE table-name<\/strong>;<\/p>\n\n\n\n
You need to upload the text file to your hosting account and then add the path to the query.<\/p>\n\n\n\n
Note<\/strong>: Line terminators can differ depending on the operation system where the file has been created.<\/p>\n\n\n\n
In Linux a newline is signified with the following characters: \\n<\/strong><\/p>\n\n\n\n
In Windows a newline might be signified with \\r\\n<\/strong> or only \\r<\/strong> (WordPad).<\/p>\n\n\n\n
You need to know that difference if you need to specify newlines in your SQL import query.<\/p>\n\n\n\n
For example:<\/strong><\/em><\/p>\n\n\n\n
$query=\"FIELDS TERMINATED BY ',' LINES STARTING BY 'xxx' LINES TERMINATED BY '\\r\\n'\";<\/code><\/pre>\n\n\n\n
If the text file is created on Windows OS, you might have to use LINES TERMINATED BY ‘\\r\\n’ as a line terminator.<\/p>\n","protected":false},"excerpt":{"rendered":"
If you need to import data from text file (.txt, .csv, …) to a database table, you should use LOAD DATA LOCAL INFILE instead of LOAD DATA INFILE.<\/p>\n","protected":false},"author":11,"comment_status":"closed","ping_status":"open","template":"","format":"standard","meta":{"inline_featured_image":false,"_lmt_disableupdate":"","_lmt_disable":"no","footnotes":""},"ht-kb-category":[154],"ht-kb-tag":[],"class_list":["post-5631","ht_kb","type-ht_kb","status-publish","format-standard","hentry","ht_kb_category-mysql"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t