how to handle the "\r\n" in csv which exists in the mysql.

hi,

how to handle the "\r\n" in csv which exists in the data.

command to generate the csv is as follows

SELECT
    "id","name","remarks"
union
select
id,name,remarks
FROM
    test_data INTO OUTFILE 'C:\\temp_data\\backup.csv'
CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Table

CREATE TABLE `test_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `remarks` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8

Data in the database when i copy the row content from the table in mysql.

'5', 'test1_name', 'xxx\r\n·yyyyy:H250mm\r\n zzzzzzz '

Output in csv
"id","name","remarks"
"5","test1_name","xxx\
yyyyy:H250mm\
zzzzzzz "

how to remove the "\" coming in the data, i want the whole row should come in 1 line?
RockingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Terry WoodsIT GuruCommented:
Try this. It replaces '\r\n' with a space character.

SELECT
    "id","name","remarks"
union
select
id,name,replace(remarks,'\r\n', ' ')
FROM
    test_data INTO OUTFILE 'C:\\temp_data\\backup.csv'
CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Open in new window


Note that you will likely get a faster response in future if you put 500pts on the question.
0
RockingAuthor Commented:
Ok,Is there any max limit no of points in the forum user wise.Or the points are unlimited?
btw i have increased the points :)

Now if i import this csv then there will be  issues?

say user has provided remarks like this
xyz
yyy
zzz

Now in the csv file when i import this file it would be
xyz yyy zzz

and same file import in the database record would be
xyz yyy zzz

When i display on screen it would be like
xxx yyy zzz

Thing i can do is replace again the "" with "\n" but deliberately user has provided space in remarks it would result in "\n" which again is not good.

Pls. suggest
0
Terry WoodsIT GuruCommented:
500pts is the maximum.

If you're planning on importing the csv file back into a database and would like to retain the original line breaks, then the solution in my previous post can't restore the line breaks during the import.

Can you please provide some more explanation as to what your end goals are? So far, it seems you want to have an export to csv that
a) is without line breaks, and
b) regains the line breaks when imported into a database

Why do you have the requirement for using a csv format without line breaks? Would an alternative export format be ok?

If you swapped the line breaks for something like <br>, then swapped them back during the import then would that be ok? (it would mean that any occurrences of <br> in the source data would be converted to linebreaks). If not <br> then, you'd need to choose something else that was not in the source data, or somehow work around the requirement to use a csv file the way you are using it.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

RockingAuthor Commented:
yes alternative solution is ok

this is the aim of import csv
a) is without line breaks, and
b) regains the line breaks when imported into a database

In my output csv the extra "/" is coming for the data in new line

Output in csv
"id","name","remarks"
"5","test1_name","xxx\
yyyyy:H250mm\
zzzzzzz "

how to remove this if this gets removed then i hope i can achieve the target
0
Terry WoodsIT GuruCommented:
Why do you need it without linebreaks while in .csv form? Is it for opening in Excel or something like that?
0
RockingAuthor Commented:
yes opening in excel
0
Ray PaseurCommented:
All of the PHP functions are documented in the online man pages.  Have a look at trim() which can normalize the lines, removing any unwanted characters at the ends.

You might want to get some PHP learning under your belt if you're going to be doing this sort of stuff.  This article will help with that.  A month of directed study will put you a year ahead of trial-and-error learning, I promise!
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
Why did you give a marked-down grade to the answers here?  You got the exact solution!  Please see the grading guidelines.  We are entitled to an explanation when you mark a grade down like this.
http://support.experts-exchange.com/customer/portal/articles/481419
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.