New lines in User Query export to XLS
New lines in User Query export to XLS
When exporting the results of a custom query for the procedure notes, I do not seem to be getting the lines breaks that are in the note. I can check and uncheck the "Enable Line Wrap" in the query editor and see that there are line breaks in the notes, but when I export the notes, the line breaks are gone. Thoughts? Is there a way to ensure that the line breaks are included in the export?
-
ishubham2101
- Posts: 9
- Joined: Thu Sep 10, 2026 11:23 am
Re: New lines in User Query export to XLS
The export cannot carry them, and that is deliberate rather than a bug.
The User Query export writes one line per row. A newline sitting inside a note field would end that line early, and everything after it would land in a new row with its columns shifted left. You can see that failure in t=7715 on this forum, where somebody's columns were overlapping for exactly that reason. So the export strips the line breaks instead, and the note arrives in Excel as one run-on paragraph.
What works is to carry a marker through the export and turn it back into a real line break once it is in Excel. Two steps, about ten seconds.
First, in the query, swap the line breaks for something that survives:
Nest all three. Windows text uses \r\n, older Mac text uses a bare \r, and replacing only \n leaves a stray carriage return behind that some importers still treat as a line ending.
Second, in Excel, select the note column and open Find and Replace. In Find what, type ~~. Click into Replace with and press Ctrl+J. Nothing visible appears, it looks like an empty box, but that is the line break character. Replace All, then turn on Wrap Text for that column and the notes come back looking the way they do in Open Dental.
Pick a marker that cannot appear in your notes. Two tildes is usually safe. A pipe is not, people use it in notes more than you would expect.
If you are exporting to CSV rather than tab separated, watch for commas inside names and notes as well. Same class of problem, same fix.
The User Query export writes one line per row. A newline sitting inside a note field would end that line early, and everything after it would land in a new row with its columns shifted left. You can see that failure in t=7715 on this forum, where somebody's columns were overlapping for exactly that reason. So the export strips the line breaks instead, and the note arrives in Excel as one run-on paragraph.
What works is to carry a marker through the export and turn it back into a real line break once it is in Excel. Two steps, about ten seconds.
First, in the query, swap the line breaks for something that survives:
Code: Select all
REPLACE(REPLACE(REPLACE(pl.ClaimNote, '\r\n', '~~'), '\n', '~~'), '\r', '~~') AS noteSecond, in Excel, select the note column and open Find and Replace. In Find what, type ~~. Click into Replace with and press Ctrl+J. Nothing visible appears, it looks like an empty box, but that is the line break character. Replace All, then turn on Wrap Text for that column and the notes come back looking the way they do in Open Dental.
Pick a marker that cannot appear in your notes. Two tildes is usually safe. A pipe is not, people use it in notes more than you would expect.
If you are exporting to CSV rather than tab separated, watch for commas inside names and notes as well. Same class of problem, same fix.