menu

Questions & Answers

No SQL Query results after successful CSV import in mysql using LOAD DATA LOCAL INFILE

I am facing a strange problem after successfully importing the contents of a .csv file in mysql db. The data from the csv file is imported into the db table successfully but no query results are returned if i run any SQL query with a Condition on on the table. I am able to run the query :

select * from mst_question

but no results are returned if a condition is specified and the condition is met

select * from mst_question where qtype='single'

The table is having rows where the column qtype contains the condition text "single" but no results are returned.

Strangely, if i edit the column "qtype" contents in the table and replace the test "single" by typing "single" the row is returned... for every row i edit !!!

My .csv file :

que_id,test_id,que_desc,ans1,ans2,ans3,ans4,true_ans,qtype
,11,In which year is the HTML specification supposed to be complete and finalized?,2012,2015,2020,2022,D,single
,11,Which of the following doctypes was introduced by HTML5?,<!doctype xhtml>,<!doctype html>,"<!doctype html PUBLIC ""-//W3C//DTD HTML 5.0 Transitional//EN"">","<!doctype html5 PUBLIC ""-//W3C//DTD HTML 5.0 Transitional//EN"">",B,single
,11,How do you stop crawlers from following links to sites you don't want to be associated with?,"<a href=""#"" rel=""nofollow""> ","<a href=""#"" rel=""dontgo""> ","<a href=""#"" rel=""nogo""> ","<a href=""#"" rel=""noassociation"">",A,single
,11,Which tag is used to define a section of the page that has content that is related but not critical to the main content in HTML5?,<article> ,<sidesection> ,<aside> ,<section> ,C,single
,11,The <article> is used to contain a main article. What is the tag used to break it into sections?,<article> ,<time> ,<aside> ,<section> ,D,single

My LOAD DATA LOCAL INFILE syntax :

LOAD DATA LOCAL INFILE 'quest.csv' INTO TABLE mst_question FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY """" IGNORE 1 LINES

Output after the LOAD DATA LOCAL INFILE is executed :

(5 row(s)affected)
(0 ms taken)

My SQL Query ( which gives results ) :

select * from mst_question

Result :

(5 row(s)returned)
(0 ms taken)

My SQL Query with simple condition ( which gives NO results ) :

select * from mst_question where qtype='single'

Result :

(0 row(s)returned)
(0 ms taken)

What am i doing wrong ????

Cant find it.... Pls advise...

Comments:
2023-01-19 23:01:09
What are the results from 'select * from mst_question'. If we could see the data, that would make it easier. I suspect there is some whitespace or hidden char (perhaps line returns) in the table after the import.
2023-01-19 23:01:09
It looks like you may have some issues with how some of the csv strings are quoted.
2023-01-19 23:01:09
Form what you're describing I'm essentially certain your db is storing a value subtly different from 'single' where you're looking for it. Could you run select * from mst_question where qtype <> 'single'; and post the output?
2023-01-19 23:01:10
@cms_mgr this returns all 5 rows !!
2023-01-19 23:01:10
@Sandy505 so it should. Alvaro's answer below looks like a good explanation to me so try that out to see if it helps with your original query.
Answers(2) :

My guess is that your file has Windows line feeds:

...0,2022,D,single\r\n

You haven't specified the LINES TERMINATED BY '\r\n' clause so MySQL probably defaults to Unix style (\n) so it actually imports single\r into your column.

You can inspect exact column contents with HEX().

Otherwise: Export data and see file content. In last column you see \r.

Open SQL file in a text editor, and look at the end of each inserted row. Example:

INSERT INTO `student` (`number`, `name`, `color`) VALUES
('1', 'David', 'Blue\r'),
('2', 'James', 'Yellow\r'),

See \r.