Load contents in text files to sqlite table?

Question

I have simple text files that contain just normal texts.

I was wondering if there is a way to load the text contents to table in sqlite.

So maybe I could create table myTable(nameOfText TEXT, contents TEXT);

And then put the name of the text into the first column and contents to the second column.

If putting in the name of the file is hard, loading the content into one column table is just as fine.

Any suggestion would be appreciated.

Thank you !!

Answer 1

Say you have a file text.txt of CSV format:

name1,content1
name2,content2

Try the commands below to import the data in test.txt into your table

D:\test>sqlite3 test.db
SQLite version 3.6.23
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table myTable(nameOfText TEXT, contents TEXT);
sqlite> .separator ","
sqlite> .import test.txt myTable
sqlite> select * from myTable;
name1,content1
name2,content2
sqlite>

Change the separator used in your text file with .separator command before you import the data.

Answer 2

termsql is a tool that can convert text from a file or the output of a program (stdin) on-the-fly into a sqlite database.

termsql -c nameOfText,contents -i input.txt -o myDB.db 

This will create a table with the columns nameOfText and contents. For each line in input.txt one row will be inserted into myDB.db.

You didn't tell us about the delimiter nameOfText and the context are separated by. By default termsql assumes whitespace is the delimiter. But should it be ',' for example, then you would do something like this:

termsql -d ',' -c nameOfText,contents -i input.txt -o myDB.db

You can get termsql here: https://github.com/tobimensch/termsql

Termsql has other usecases, too. You can do SQL statements on the new database all in one command. The following example would create your database and return the nameOfText column on the command line for all rows where the contents column contains the string 'test'.

termsql -d ',' -c nameOfText,contents -i input.txt -o myDB.db "select nameOfText from tbl where contents like '%test'"