How to read a .txt file line by line with a SQLite script?

Question

I am working on a project with a database. This database is very simple. There is only one table with 2 columns : id (int) and text (string). To fill this base I want to create a .sql script file. (this database isn't created inside an android project because I want an already filled database to insert in my android project)

I want my script to create the table and then read a .txt file with a string value (for text column) on each row. For each row, it should insert the string value into the table.

I am not very familiar with SQLite and SQL in general. I already found a way to auto-increment the id using an iterator (but I dind't test it yet), but I couldn't found how to read a .txt file line by line.

• So my question is : Is it possible to read a .txt file line by line in a SQLite script ?

• And if it is, could you please tell me how to do it.

Answer 1

Here's a solution in pure sqlite

CREATE TEMP TABLE input (value STRING);
INSERT INTO input VALUES (TRIM(readfile('input.txt'), char(10)));

CREATE TABLE lines (s STRING);
WITH RECURSIVE
    nn (s, rest)
AS (
    SELECT
        (SELECT SUBSTR(input.value, 0, INSTR(input.value, char(10))) FROM input),
        (SELECT SUBSTR(input.value, INSTR(input.value, char(10)) + 1) FROM input)
    UNION ALL
    SELECT
        CASE INSTR(nn.rest, char(10))
            WHEN 0 THEN nn.rest
            ELSE SUBSTR(nn.rest, 0, INSTR(nn.rest, char(10)))
        END,
        CASE INSTR(nn.rest, char(10))
            WHEN 0 THEN ''
            ELSE SUBSTR(nn.rest, INSTR(nn.rest, char(10)) + 1)
        END
    FROM nn
    WHERE LENGTH(nn.rest) > 0
)
INSERT INTO lines (s)
SELECT nn.s FROM nn;

DROP TABLE input;

A few subtleties here:

• sqlite does not have a \n escape so you have to use char(10)
• this doesn't work well for mixed newlines or \r\n newlines (though you can adjust some + 1s to + 2s and char(10) to char(13) || char(10)
• most of the magic is in the recursive union in the middle which nibbles off a line at a time

note that I'm using this approach to solve advent of code -- https://github.com/anthonywritescode/aoc2020

Answer 2

SQLite is an embedded database; it is designed to be used together with some 'real' programming language. There are no functions to access and parse text files.

You have to write your own script in whatever language you like, or use some existing tool.
If there is a character that is guaranteed not to occurr in the text file, you can use the sqlite3 command-line shell and a temporary, one-column table for importing:

CREATE TEMP TABLE i(txt);
.separator ~
.import MyFile.txt i
INSERT INTO TheRealTable(text) SELECT txt FROM i;  -- assumes id is autoincrementing
DROP TABLE i;