43

Does anybody have any tips on utilities that can be used to bulk load data that is stored in delimited text files into an SQLite database?

Ideally something that can be called as a stand-alone program from a script etc.

A group I work with has an Oracle Database that's going to dump a bunch of data out to file and then load that data into an SQLite database for use on a mobile device and are looking for the easiest way to implement that sort of scenario.

Mat Nadrofsky
  • 8,289
  • 8
  • 49
  • 73

2 Answers2

73

Check out the sqite .import command - it does exacty this.
You can set the separator with the .separator command

sqlite3 myDatabase
create table myTable (a, b, c);
.separator ','
.import  myFile  myTable
Martin Beckett
  • 94,801
  • 28
  • 188
  • 263
  • 9
    wow. this is a much better choice. I will delete my response in shame...+1 – MikeJ Mar 30 '09 at 15:57
  • 2
    wanted to comment that I thought the "in shame" comment was pretty funny ;) – Brady Moritz Nov 23 '10 at 15:24
  • 2
    one important caveat: this does *not* handle more complex CSV's: fields must not be wrapped in double quotes, and you can't try to escape your separator by a backslash if you need it as valid field content. – Tom De Leu Nov 19 '13 at 20:04
  • @TomDeLeu `.mode csv` will allow importing CSV – M.M Jul 06 '21 at 21:33
1

Why do you want a text file?

Just use Java which does have easily available libraries for Oracle and SQLite access. Connect to both databases and just select from one db and insert into another with no additional complexity of CSV, which is not a very well defined format and will give you problems with character encoding, quotes, comas/tabs or semicolons, newlines etc. in your data.

Tometzky
  • 22,573
  • 5
  • 59
  • 73
  • You bet. They want one because that's the path they were thinking down. I'm going to suggest this approach (as it would be the smarter one...) and see what comes of it. They might not have an experienced Java guy, in that case, I'll just learn it and do it. – Mat Nadrofsky Mar 30 '09 at 17:30
  • 2
    There's no reason it has to be Java, any other language that can use both Oracle and SQLite databases can do this job. This is pretty much any major language. – Chad Birch Mar 30 '09 at 19:14
  • Since sqlite locks on writes it can be nice to load from a file. Is there a bulk load option directly from the Java interface (or other major languages?) without writing to a file? – qwerty9967 Feb 14 '13 at 13:58
  • Why write code when the SQLite database offers an efficient import tool? – m0j0 Oct 19 '15 at 15:41
  • @qwerty9967 You only have to wrap your inserts in a transaction and they will be thousands of times faster - effectively a bulk operation. – Jørgen Fogh Jul 21 '16 at 08:22