Output of Line Breaks into CSV
-
- Posts: 293
- Joined: Mon Apr 22, 2013 8:49 am
- Location: Dallas, TX
- Contact:
Output of Line Breaks into CSV
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?
Any ideas on how I can avoid having these line breaks appear in a text file?
Re: Output of Line Breaks into CSV
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?
-
- Posts: 293
- Joined: Mon Apr 22, 2013 8:49 am
- Location: Dallas, TX
- Contact:
Re: Output of Line Breaks into CSV
So in CommLog notes, are returns "\r\n" or something else?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?
-
- Posts: 293
- Joined: Mon Apr 22, 2013 8:49 am
- Location: Dallas, TX
- Contact:
Re: Output of Line Breaks into CSV
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.
Re: Output of Line Breaks into CSV
Yes commlog notes should usually be "\r\n" for line breaks. Which ascii hex would be 0d and 0a. Hex 20 is a space.KevinRossen wrote:So in CommLog notes, are returns "\r\n" or something else?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?
Re: Output of Line Breaks into CSV
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:
Which will work for both places that store new lines as "\r\n" and those that only use "\n".
Code: Select all
REPLACE(REPLACE(commlog.Note, CONCAT(CHAR(13),CHAR(10)),CHAR(10)),CHAR(10),' ')
-
- Posts: 293
- Joined: Mon Apr 22, 2013 8:49 am
- Location: Dallas, TX
- Contact:
Re: Output of Line Breaks into CSV
Perfect! Thanks!!!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:Which will work for both places that store new lines as "\r\n" and those that only use "\n".Code: Select all
REPLACE(REPLACE(commlog.Note, CONCAT(CHAR(13),CHAR(10)),CHAR(10)),CHAR(10),' ')