sqlite3 shell on Windows : extra carriage returns when using output redirection

Question

Demonstrate the issue

Let's use nvim to create a text file:

> nvim C:\temp\in-file.txt

The contents of the text file:

> Get-Content C:\temp\in-file.txt
abc
bcd
cde

Now let's create a simple database that can store this content:

> sqlite3 C:\temp\test-1.db
-- Loading resources from C:\Users\dharm/.sqliterc
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
sqlite> CREATE TABLE test_table (id INTEGER PRIMARY KEY, content TEXT NOT NULL);

Let's insert the text file content:

sqlite> INSERT INTO test_table (content) VALUES (readfile('c:\temp\in-file.txt'));
sqlite> SELECT * FROM test_table;
+----+---------+
| id | content |
+----+---------+
| 1  | abc     |
|    | bcd     |
|    | cde     |
+----+---------+

OK, looks good.

Now let's use sqlite3 to output the content to the console:

> sqlite3 C:\temp\test-1.db -cmd '.mode list' "SELECT content FROM test_table WHERE id = 1;"
-- Loading resources from C:\Users\dharm/.sqliterc
abc
bcd
cde

And now let's redirect that output to a file:

> sqlite3 C:\temp\test-1.db -cmd '.mode list' "SELECT content FROM test_table WHERE id = 1;" > C:\temp\out-5.txt
-- Loading resources from C:\Users\dharm/.sqliterc

Note that the file has extra blank lines:

> Get-Content C:\temp\out-5.txt
abc

bcd

cde

Two carriage returns per line

The Format-Hex command shows that each line has 0D 0D 0A. I.e. it has two carriage returns.

> Format-Hex C:\temp\out-5.txt

   Label: C:\temp\out-5.txt

          Offset Bytes                                           Ascii
                 00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F
          ------ ----------------------------------------------- -----
0000000000000000 61 62 63 0D 0D 0A 62 63 64 0D 0D 0A 63 64 65 0D abc���bcd���cde�
0000000000000010 0D 0A 0D 0A                                     ����

Workaround: `writefile` in `sqlite3`:

Now, I can get this to work if I use writefile in sqlite3 instead of output redirection:

> sqlite3 C:\temp\test-1.db -cmd '.mode list' "SELECT writefile('c:\temp\out-5.txt', content) FROM test_table WHERE id = 1;"
-- Loading resources from C:\Users\dharm/.sqliterc
15
> Get-Content C:\temp\out-5.txt
abc
bcd
cde

Question

Is there a way to get this to work using output redirection instead of writefile from sqlite3?

I can use the writefile approach. But I'm curious if there's a way to get the output redirection approach to work.

PowerShell reads the output line by line and if sqlite3 outputs a line with a \r then the output pwsh has is something like @("line1`r", "line2`r", "line3`r"). When that is redirected to a file with something like > it is joined together with `r`n. What you can try is to trim the ends like sqlite3 ... | ForEach-Object TrimEnd | Set-Content $path. This may have a side effect of triming any trailing spaces if that's an important scenario you care about. — Jordan Borean
– Jordan Borean, Commented Oct 24 at 1:14

mklement0 · Accepted Answer · 2025-10-28 01:35:55Z

JosefZ's answer shows you how to solve the problem after the fact, on the PowerShell side - but note that the solution assumes that the stored multiline text doesn't itself contain empty lines (as they would be removed too).

The root cause of the problem appears to be a SQLite CLI (sqlite3.exe) bug on Windows: When listing a TEXT column value containing a multiline string via sqlite3.exe, up to at least v3.50.4:

It attempts to transform Unix-format LF-only (\n) newlines to Windows-format CRLF (\r\n) ones (so as to provide platform-appropriate output), ...
... but fails to account for strings that already have CRLF newlines (in which case no transformation is needed at all); that is, it seemingly invariably replaces \n characters with \r\n sequences - even those that are already preceded by \r.
- As a result, a multiline string stored with CRLF newlines is unexpectedly listed (output to stdout) with \r\r\n sequences, i.e. with an extraneous CR preceding each CRLF newline.

As for the manifestations of the bug:

When printing to the console (terminal), this problem is masked, because the extra CR simply resets the cursor position on the current console output row and is therefore effectively invisible.
When redirecting to a file using >, the redirection operator, the problem becomes apparent, but the behavior depends on the PowerShell edition:
- In PowerShell (Core) 7, in v7.4+ (older versions are now out of support), where applying > to an external program such as sqlite3.exe now captures the raw byte output, the erroneous \r\r\n sequences are saved as-is to the target file, which is what you saw.
- In Windows PowerShell (the legacy, ships-with-Windows, Windows-only edition of PowerShell whose latest and final version is 5.1; also in now-obsolete PowerShell 7 versions up to v7.3.x) PowerShell acts as an intermediary and parses external-program output into .NET strings line by line, which are then relayed as such - separated with CRLF newlines on Windows - to the target file.^[1]
  Because PowerShell accepts even a CR in isolation as a newline when splitting multiline text into lines, it parses the extraneous CR as an empty line, which resulted in the extra, empty lines you saw in the output file; in effect, each \r\n sequence in the input string becomes two such sequences (\r\n\r\n).
- It follows from the above that in both editions using Get-Content to read and output the target file's content surfaces the problem, given that Get-Content too outputs a file's content line by line (again recognizing a CR in isolation as a newline); however, if you were to use the -Raw switch in PowerShell 7 (v7.4+), so as to output the file content as a whole, the problem would again be masked.

Workaround:

As an alternative to your own writefile() workaround, the simplest workaround is to ensure that multiline strings stored in your database columns use only Unix-format LF-only (\n) newlines.

Ideally, create the files from whose content the multiline TEXT columns are to be populated with LF-only newlines to begin with, given that the readfile() function seemingly copies the file's bytes as-is to the target column.
- In interactive file creation, make sure that your editor uses LF-only newlines when saving.
To transform an existing file containing CRLF newlines to LF-only newlines, use something like the following (since the file is updated in-place, make a backup copy first, to be safe; beware of potential character-encoding problems, given that Set-Content uses its default encoding, with no knowledge of the input file's original encoding):^[2]
```
(Get-Content -Raw in-file.txt) -replace "`r`n", "`n" |
  Set-Content -NoNewLine in-file.txt
```

^{[1] Note that even PowerShell 7.4+ still exhibits this behavior when using the pipeline (rather than >) to relay output to a PowerShell-native command; this means that ... > out.txt can behave differently than ... | Set-Content out.txt.

See this answer for more information.}

^{[2] See the bottom section of this answer for more information.}

JosefZ · Accepted Answer · 2025-10-24 16:12:38Z

PowerShell reads the output line by line and if sqlite3 outputs a line with a \r then the pwsh output is something like @('abc', '', 'bcd', '', 'cde', '', '').

Proof:

$sql_dbf = 'C:\temp\test-1.db'
$sql_cmd = '.mode list'
$sql_arg = "SELECT content FROM test_table WHERE id = 1;"

$sql_out = sqlite3.exe $sql_dbf -cmd $sql_cmd $sql_arg
$sql_out.Count                                          # 7
($sql_out | ForEach-Object {$_.Length}) -join ', '      # 3, 0, 3, 0, 3, 0, 0

Fix: remove empty lines from sqlite3 output as follows:

$sql_out = sqlite3.exe $sql_dbf -cmd $sql_cmd $sql_arg | Where-Object {$_}
($sql_out | ForEach-Object {$_.Length}) -join ', '      # 3, 3, 3

Redirecting to a file:

sqlite3.exe $sql_dbf -cmd $sql_cmd $sql_arg | Where-Object {$_} > C:\temp\out-5.txt
Format-Hex -Path C:\temp\out-5.txt

   Label: C:\temp\out-5.txt

          Offset Bytes                                           Ascii
                 00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F
          ------ ----------------------------------------------- -----
0000000000000000 61 62 63 0D 0A 62 63 64 0D 0A 63 64 65 0D 0A    abc��bcd��cde��

Collectives™ on Stack Overflow

sqlite3 shell on Windows : extra carriage returns when using output redirection

Demonstrate the issue

Two carriage returns per line

Workaround: `writefile` in `sqlite3`:

Question

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Demonstrate the issue

Two carriage returns per line

Workaround: writefile in sqlite3:

Question

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Workaround: `writefile` in `sqlite3`: