Search code examples
sqliteetl

Load contents in text files to sqlite table?

I have simple text files that contain just normal texts.

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

  • So maybe I could Create table myTable(nameOfText TEXT, contents TEXT);
  • And then put nameOfText 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!

Solution

  • 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'"