SQLite Forum

How to dump a table to a TSV file but escape characters properly

How to dump a table to a TSV file but escape characters properly

(1) By 6kEs4Majrd on 2021-04-03 17:05:28

I can use the following command to dump a table into TSV format. But the problem is that when any field contains tab and newline characters, the output will be messed up. Instead, it is better to escape them as '\t' and '\n', and escape '\' as '\\'.

sqlite3 -readonly -header -separator $'\t' dbfile "select * from mytable;"

As another mode, it may be better to escape all nonprintable characters.

Does anybody have a good way to do these? Thanks.

(2) By Keith Medcalf (kmedcalf) on 2021-04-03 18:12:39 in reply to 1 [link]

You "forgot" to specify that you want CSV output.  You need to add the -csv option (before the -separator option) to get text/blob items quoted.

If you wish to "diddle the data" you need to write a data diddling application.

You will note that escape characters are a function of the parser, so if you diddle the data to convert some characters to escape sequences you are advised to ensure that whatever you are using to read the result knows how to de-diddle your diddling.

The sqlite3 shell knows neither how to diddle nor how to de-diddle.

(3) By anonymous on 2021-04-03 19:06:54 in reply to 1 [link]

Another way is to write a SQLite extension function which will produce the format that you need, and then you can write e.g. `select formatted(a,b,c,d) from mytable;` or whatever you require.