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...
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().