Hi there,
Starting with version 3.52.0 (2026-03-06), SQLite's CLI introduced an overhaul
of its query result formatting via the new ~.mode~ dot-command documented at
<https://sqlite.org/climode.html>. For CSV output mode, the line ending is now
always ~\r\n~ (CRLF), regardless of the platform or the ~.crlf~ setting:
#+begin_quote
For CSV output, the line ending is always "\\r\\n" regardless of the .crlf
setting, due to requirements of RFC-4180.
#+end_quote
When ~org-babel-execute:sqlite~ runs a SQLite query with CSV output, the output
buffer receives lines terminated with ~\r\n~. The function
~org-table-convert-region~ splits on newlines (~\n~) but does not strip the
preceding carriage return (~\r~). This leaves ~\r~ characters at the end of
each field value in the resulting table. When this table is displayed in an Org
buffer, the ~\r~ characters are shown as ~^M~.
* Example
#+name: tbltest
| a |
| b |
#+begin_src sqlite :db ":memory:" :colnames no :var sometable=tbltest :header on
.import $sometable mytable
SELECT * FROM mytable;
#+end_src
#+results:
| a%d |
| b%d |
* A Patch
#+begin_src diff
--- a/ob-sqlite.el
+++ b/ob-sqlite.el
@@ -102,6 +102,11 @@ This function is called by `org-babel-execute-src-block'."
(cons "db" (or db ""))))
;; body of the code block
(org-babel-expand-body:sqlite body params)))
+ ;; Strip \r characters. SQLite 3.52+ CSV mode always outputs CRLF
+ ;; line endings per RFC 4180 (see https://sqlite.org/climode.html).
+ (goto-char (point-min))
+ (while (search-forward "\r" nil t)
+ (replace-match "" "" t))
(org-babel-result-cond result-params
(buffer-string)
(if (equal (point-min) (point-max))
#+end_src
This solves the issue for me, but may not be the most principled solution.
Emacs : GNU Emacs 31.1 (build 1, x86_64-pc-linux-gnu, GTK+ Version 3.24.52,
cairo version 1.18.4)
Package: Org mode version 9.8.10 (9.8.10-80c431)
Cheers,
Martin
--
_pgp: https://keybase.io/martinralbrecht
_www: https://malb.io
_prn: he/him or they/them
