SQLite Forum

How to import multi-line feeds?

How to import multi-line feeds?

(1) By anonymous on 2021-05-20 17:25:36 [link]

I want to `.import` a file where some fields contain line feeds (LF) encoded as some unique string pattern.

I know with SQL one can use the `|| char(10) ||` trick.

But, how to do the same with the `.import` shell command?

Is there some way to tell the SQLite3 shell to interpret a certain char sequence as LF during `.import`?  For example, interpret `\n` as LF?

(2) By anonymous on 2021-05-20 17:58:34 in reply to 1 [link]

You can use the SQL `REPLACE` function, perhaps (although, depending on what exactly you will want to do, it might not be good enough, since it does not support e.g. replacing two backslashes with a single slash and replacing other escapes at the same time without interference).

If you want to customize the import handling, one thing that I have done is to create a view and attach triggers to it, and then to import into that view instead of directly into the table that you want to import data into. (If needed, you can also create another table to store temporary state during importing if the format you are importing requires saving the state between records of the input file.)

(3) By Donald Griggs (dfgriggs) on 2021-05-20 18:16:30 in reply to 1 [link]

I'm not aware of a method to set an arbitrary hex value for a field or record separator (though I could swear that strings were acceptable as separators in some ancient version of the CLI -- maybe I just dream in sqlite.)

I don't know if

    .mode  ascii

would be of any use to you. 
It's described as:  "Columns/rows delimited by 0x1F and 0x1E"

A hack might be to use the CLI's file functions to read the import file into a single blob field in a temporary 1-row table, perform a REPLACE or two, then write it back out as your new import file.


Someone here may have a better answer.

(4) By Larry Brasfield (larrybr) on 2021-05-20 18:20:01 in reply to 1

You can use blob literals to get arbitrary content past the SQLite shell's input parser, then use CAST ( your_blob_literal AS TEXT ) to make it into an arbitrary text value.