Solved

Replace line breaks with a space with using Select.. Into Outfile

Posted on 2008-10-20
5
549 Views
Last Modified: 2012-05-05
I am running a query to download a csv. Some of the fields might contain line breaks, which makes the program reading the file think that the record has ended, messing up the column count. Can I replace the breaks with a space or make it ignore the breaks?
SELECT 

					p.prodid,

					r.id,

					r.rganum,

					p.partnum,

					p.description,

					p.mfgmonth,

					p.mfgyear,

					p.produse,

					p.lot,

					p.amount,

					p.unitMeasure

			INTO OUTFILE '$file2' 

			FIELDS TERMINATED BY ';' 

			LINES TERMINATED BY '\n' 

			FROM 

				ns_rgaproduct p,

				ns_rga r

			WHERE 

				r.status = 'approved' 

				and r.decdate >= '$exportdate'

				and r.id = p.rgaid

Open in new window

0
Comment
Question by:nisupport
  • 3
  • 2
5 Comments
 
LVL 82

Accepted Solution

by:
hielo earned 500 total points
Comment Utility
try:
SELECT 
                              p.prodid,
                              r.id,
                              r.rganum,
                              p.partnum,
                              REPLACE(`p`.`description`,'\n',''),
                              p.mfgmonth,
                              p.mfgyear,
                              p.produse,
                              p.lot,
                              p.amount,
                              p.unitMeasure
                  INTO OUTFILE '$file2' 
                  FIELDS TERMINATED BY ';' 
                  LINES TERMINATED BY '\n' 
                  FROM 
                        ns_rgaproduct p,
                        ns_rga r
                  WHERE 
                        r.status = 'approved' 
                        and r.decdate >= '$exportdate'
                        and r.id = p.rgaid

Open in new window

0
 

Author Comment

by:nisupport
Comment Utility
I am still getting the same results. Is there another character for a break to replace? I have tried <br>.
0
 
LVL 82

Expert Comment

by:hielo
Comment Utility
Other than '\n', you can try:
'\r\n'

NOTE:You need to use the replace on every field that may potentially have the newline.
0
 

Author Comment

by:nisupport
Comment Utility
Figured it out needed to put '\r\n' for the line break.
0
 
LVL 82

Expert Comment

by:hielo
Comment Utility
glad to help
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now