Output of Line Breaks into CSV

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Output of Line Breaks into CSV

Post by KevinRossen » Mon Feb 29, 2016 1:46 pm

I run some queries that output results into text/csv that include the most recent CommLog note. What I'm having trouble with is line break. In Open Dental the line breaks are handle fine, but when I output that field into a text/csv file it causes issues with formatting. One workaround that has worked with appointment notes is to use the REPLACE function like this: REPLACE(a.Note, CHAR(10), ' '), but this doesn't work on CommLog notes. I've also tried replacing CHAR(13), too, but the line breaks still appear in a text file.

Any ideas on how I can avoid having these line breaks appear in a text file?
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

tgriswold
Posts: 122
Joined: Fri Jun 07, 2013 8:52 am

Re: Output of Line Breaks into CSV

Post by tgriswold » Mon Feb 29, 2016 2:08 pm

I don't have a lot of advice about how to fix this problem, but I have some context. Some places in the program store "\r\n" as returns, and others only store "\n". This is because in .net, richtextboxes store new lines differently than textboxes (more specifically an ODtextbox is an enhanced richtextbox). What we do for some things like middle tier is replace "\r\n" with "\n". Then replace "\n" with whatever we want. That way we know that when we do the second replacement, there won't be any "/r"s left. So in excel or w/e maybe try that?
Travis Griswold
Open Dental Software
http://www.opendental.com

KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: Output of Line Breaks into CSV

Post by KevinRossen » Mon Feb 29, 2016 2:33 pm

tgriswold wrote:I don't have a lot of advice about how to fix this problem, but I have some context. Some places in the program store "\r\n" as returns, and others only store "\n". This is because in .net, richtextboxes store new lines differently than textboxes (more specifically an ODtextbox is an enhanced richtextbox). What we do for some things like middle tier is replace "\r\n" with "\n". Then replace "\n" with whatever we want. That way we know that when we do the second replacement, there won't be any "/r"s left. So in excel or w/e maybe try that?
So in CommLog notes, are returns "\r\n" or something else?
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: Output of Line Breaks into CSV

Post by KevinRossen » Mon Feb 29, 2016 2:41 pm

I just looked at the HEX pattern and where I'm seeing these line breaks the HEX codes that are showing up are 0d 20, so I guess I should just figure out how to replace those with spaces or blanks.
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

tgriswold
Posts: 122
Joined: Fri Jun 07, 2013 8:52 am

Re: Output of Line Breaks into CSV

Post by tgriswold » Mon Feb 29, 2016 3:55 pm

KevinRossen wrote:
tgriswold wrote:I don't have a lot of advice about how to fix this problem, but I have some context. Some places in the program store "\r\n" as returns, and others only store "\n". This is because in .net, richtextboxes store new lines differently than textboxes (more specifically an ODtextbox is an enhanced richtextbox). What we do for some things like middle tier is replace "\r\n" with "\n". Then replace "\n" with whatever we want. That way we know that when we do the second replacement, there won't be any "/r"s left. So in excel or w/e maybe try that?
So in CommLog notes, are returns "\r\n" or something else?
Yes commlog notes should usually be "\r\n" for line breaks. Which ascii hex would be 0d and 0a. Hex 20 is a space.
Travis Griswold
Open Dental Software
http://www.opendental.com

tgriswold
Posts: 122
Joined: Fri Jun 07, 2013 8:52 am

Re: Output of Line Breaks into CSV

Post by tgriswold » Mon Feb 29, 2016 4:03 pm

So to do it like we do for middle tier, and assuming you want to keep it as replacing all line breaks with spaces, it'd look like:

Code: Select all

REPLACE(REPLACE(commlog.Note, CONCAT(CHAR(13),CHAR(10)),CHAR(10)),CHAR(10),' ')
Which will work for both places that store new lines as "\r\n" and those that only use "\n".
Travis Griswold
Open Dental Software
http://www.opendental.com

KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: Output of Line Breaks into CSV

Post by KevinRossen » Mon Feb 29, 2016 8:16 pm

tgriswold wrote:So to do it like we do for middle tier, and assuming you want to keep it as replacing all line breaks with spaces, it'd look like:

Code: Select all

REPLACE(REPLACE(commlog.Note, CONCAT(CHAR(13),CHAR(10)),CHAR(10)),CHAR(10),' ')
Which will work for both places that store new lines as "\r\n" and those that only use "\n".
Perfect! Thanks!!!
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

Post Reply