How to Enter Special Character In MySQL using LOAD DATA INFILE Command

Question

Good day,

I have a Comma Separated Value File and I'm Inserting this on MYSQL Database using the Data Infile Command..

But I have trouble entering 'Ñ' characters in my database using this Command.

I think the Data Infile Command doesn't read special characters like this.

Can someone enlighten me on how to this.

Thank you so much for your help.

These are my Codes.

<?php
 require 'config.php';

$sql1 = "TRUNCATE TABLE roster";
$result=$conn->prepare($sql1);
$result->execute();

$sql2 = "LOAD DATA INFILE 'NCR_ROSTER.csv' INTO TABLE roster FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES 
(family_id, enrollment_type, batch, family_status, person_id, first_name, 
middle_name, last_name, ext_name, grantee, relation, gender, ispregnant,
birthday, age, member_status, occupation, highest_educ_attained, 
attending_school, school_facility_id, school_facility_name, school_facility_address,
current_grade_level, for_educ_monitoring, reason_for_not_attending_school, attending_health_center, 
health_facility_id, health_facility_name, health_facility_address, for_health_monitoring,
reason_for_not_attending_health, region, province, municipality, barangay, purok, sitio)";
$result=$conn->prepare($sql2);
$result->execute();

echo "Roster data successfully imported to database!!";

 ?>

Answer 1

I got the answer to my problem..

I just needed to change my database Collate type to UTF-8..

In able to read/buffer Unicode Characters..

This is what I did..

ALTER table 'table_name' DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

And now its working..

Thank you guys..