I have text file which has only one string which is enclosed by "" in each line, while i am loading this file to table but it is not recognizing \r\n as new line character. If i have two columns in one line ("",integer) it's working fine.
Text file having data like:
"gmail.com"
"facebook.com"
"yahoo.com"
"goindigo.com"
"snapdeel.com"
"flipkart.com"
"ebay.com"
"godady.com"
"twiter.com"
"gmail.com"
"facebook.com"
"yahoo.com"
"goindigo.com"
"snapdeel.com"
"flipkart.com"
I am trying to load this file into Table using Query
Load Query: load data local infile 'file.LOG' into table table_name fields terminated by ' ' enclosed by '"' (column);
here terminated by field is "\t"
It's loading into table like:
INSERT INTO table_name VALUES ('\"gmail.com\"\r\n\"facebook.com\"\r\n\"yahoo.com\"\r\n\"goindigo.com\"\r\n\"snapdeel.com\"\r\n\"flipkart.com\"\r\n\"ebay.com\"\r\n\"godady.com\"\r\n\"twiter.com\"\r\n\"gmail.com\"\r\n\"facebook.com\"\r\n\"yahoo.com\"\r\n\"goindigo.com\"\r\n\"snapdeel.com\"\r\n\"flipkart.com\"\r\n\');
Entire file loading as a single line.
I found that if enclosed by(") followed by new line LOAD DATA INFILE query will not identifies the new line character by default we have to mention LINES TERMINATED BY field then only it will identifies the new line character.