14

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!

surfmuggle
  • 5,527
  • 7
  • 48
  • 77
Sardonic
  • 441
  • 3
  • 8
  • 19

4 Answers4

29

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.

hflzh
  • 634
  • 5
  • 12
5

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'"
gelbermann
  • 15
  • 1
  • 9
user3573558
  • 192
  • 1
  • 1
  • 1
    Links are broken - appears to be available from [here](https://github.com/tobimensch/termsql) now. Though I don't understand why this is preferable to just doing `create table ... ; .import ...` as described below, and then running as a script `sqlite3 myDB.db "".read to_table.sql"` – JohnSG Apr 06 '16 at 19:41
5

The function readfile reads the content of a file and returns that content as a BLOB. This function is available when using the command-line shell. For example:

sqlite> CREATE TABLE files(name TEXT, content TEXT);
sqlite> INSERT INTO files(name,content) VALUES('a.txt',readfile('a.txt'));

The documentation for this function and writefile is available at sqlite.org/cli.html.

surfmuggle
  • 5,527
  • 7
  • 48
  • 77
larpico
  • 116
  • 1
  • 3
  • Note that the readfile(X) and writefile(X,Y) functions are extension functions and are not built into the core SQLite library. – Cory Trese Jul 17 '17 at 19:47
0

While readfile function gets the job done, it only works in SQLite command-line interface (sqlite.exe).

If you are not using the CLI, you can still read a file into a table with the help of sqlean-fileio extension using fileio_read(path) function.

You can even load a bunch of files with a single SQL query using fileio_ls(path) function.

For example:

$ echo 'hello world' > hello.txt
$ echo 'sqlite is awesome' > awesome.txt

create table files(name text, content text);

insert into files(name, content)
select substr(name, 3), fileio_read(name)
from fileio_ls('.')
where name like '%.txt';

select * from files;

┌─────────────┬───────────────────┐
│    name     │      content      │
├─────────────┼───────────────────┤
│ awesome.txt │ sqlite is awesome │
│ hello.txt   │ hello world       │
└─────────────┴───────────────────┘
Anton Zhiyanov
  • 134
  • 1
  • 3