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;

Answer 3

I think the simplest way is work on the txt file to convert it to a csv file. Then you can import it directly in Sqlite3 or by a programming language.

sqlite> .mode csv table_name
sqlite> .import file_name.csv table_name

Answer 4

You can use a BufferedReader for that. the code could look like:

InputStream in = context.getResources().openRawResource( R.raw.your_txt_file );
BufferedReader reader = new BufferedReader( new InputStreamReader( in ) );

String line = null;
while( null != ( line = reader.readLine() ) ){
  doStuffWithLine( line );
}

reader.close();

Answer 5

Yes, reading a .txt file line by line in a SQLite script is possible. But you'll need to use an extension. Specifically, sqlean-fileio can do the job.

Its fileio_scan(path) function reads the file specified by path line by line without loading the whole file into memory.

For example:

$ echo 'one' > data.txt
$ echo 'two' >> data.txt
$ echo 'three' >> data.txt

create table data(id integer primary key, txt text);

insert into data(txt)
select value from fileio_scan('data.txt');

select * from data;

┌────┬───────┐
│ id │  txt  │
├────┼───────┤
│ 1  │ one   │
│ 2  │ two   │
│ 3  │ three │
└────┴───────┘

That's it!

Answer 6

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

Yes.

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

There we go:

Pseudo-code algorithm:

1. Open the file.
2. Read line by line and insert new row in the database.
3. Close resources and commit transactions.

1) Open the file

InputStream instream = new FileInputStream("myfilename.txt");
InputStreamReader inputreader = new InputStreamReader(instream);
BufferedReader buffreader = new BufferedReader(inputreader);

2) Read line by line and insert new row in database

List<String> nameList = new ArrayList<>();
String line;
do {
    line = buffreader.readLine();
    if (line != null){
        nameList.add(line);
    }
} while (line != null);

Now you should insert all names in database:

storeNamesInDB(nameList);

Where

private void storeNamesInDB(nameList){
    String sql = "INSERT INTO table (col1) VALUES (?)";
    db.beginTransaction();

    SQLiteStatement stmt = db.compileStatement(sql);
    for (int i = 0; i < nameList.size(); i++) {
        stmt.bindString(1, values.get(i));
        stmt.execute();
        stmt.clearBindings();
    }

    db.setTransactionSuccessful();
    db.endTransaction();
}

3) Close resources

Don't forget to close resources:

instream.close();
inputreader.close();

DISCLAIMER! You shouldn't copy&paste this code. Replace each var name and some instructions with someone that make sense in your project. This is just an idea.