Solved

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

Posted on 2008-10-20
5
558 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
ID: 22758120
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
ID: 22759129
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
ID: 22759331
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
ID: 22759371
Figured it out needed to put '\r\n' for the line break.
0
 
LVL 82

Expert Comment

by:hielo
ID: 22759720
glad to help
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

685 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