Insert data from files into SQLite database

Question

I have an SQLite database on this form:

Table1

Column1 | Column 2 | Column 3 | Column 4

I want to populate this database with data stored in some hundred .out files in this form, where every file has millions of rows:

value1;value2;value3;value4;
2value1;2value2;2value3;2value4;
... etc

Is there a fast way to populate the database with these data? One way would be to read in the data line for line in python and insert, however there probably should be a faster way to just input the whole file?

Bash, SQLite, Python preferrably

Take a look at pythonhosted.org/querycsv where the csv is translated into sqlite to provide sql interface. — Ali SAID OMAR
– Ali SAID OMAR, Commented Jul 8, 2015 at 10:20

user5092827 · Accepted Answer · 2015-07-08 10:30:45Z

7

SQLite has a .import command.

.import FILE TABLE Import data from FILE into TABLE

You can use it like this (shell).

for f in *.out
do
    sqlite3 -separator ';' my.db ".import $f Table1"
done

answered Jul 8, 2015 at 10:30

user5092827

Sign up to request clarification or add additional context in comments.

2 Comments

bjornasm Over a year ago

Is it a problem that every line ends with a ; ? I get "expected 7 columns but found 8 - extras ignored". Do you think this slows down the process in any way?

user5092827 Over a year ago

I don't know. You could strip the trailing ; first and try.

Collectives™ on Stack Overflow

Insert data from files into SQLite database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related