Loading a text file into MySQL and joining it with a table - Line delimiter may cause an issue

A post after a long time :)

Today, we will learn how to load a data file or text file directly into a MySQL table.

Step1: We create a table in using MySQL workbench

drop table response_values;

create table response_values(
respondent_serial varchar(10),
response_ID decimal(16,0),
D13new varchar(255),
Load_Date varchar(1),
primary key (respondent_serial)
);

Step 2: We run the below command to load the file into the table

load data local infile 'C:\\Data files\\Raw_Data_2013.txt' into table response_values
fields terminated by '|'
lines terminated by '\n'
(respondent_serial, response_ID,D13new, Load_date);

Step 3 : We now join the data of this file with another table using a Left Outer Join. MySQL follows the standard format of SQL. 

Tips: As a best practice, don't keep the joining columns at the very end of the text file. If that is unavoidable, append an additional technical column as delimiter.

Example :    

select RV.*, TD.*
from response_values as Rv left outer join TotFeed_Data as Td
on upper(trim(RV.D13new)) = upper(trim(td.actor_link) )
where d13new is not null
;

The Upper() function in MySQL converts the string into Upper-case.

Feel free to send any questions. Like us if you are helped.