SQLite Forum

Importing delimited file
Login

Importing delimited file

(1) By anonymous on 2020-04-29 23:08:33 [link] [source]

I am trying to import a tab delimited file that contains 518548 rows into an SQLite table that has 7 columns using the .import command. The command seems to run fine without any errors but only the first 145741 rows are imported. If I recreate the import file and SQLite table with only 2 columns but the same number of rows, then all the rows are imported. Is there a size limit for import files?

(2) By TripeHound on 2020-04-30 00:14:25 in reply to 1 [link] [source]

What happens if you try from (or only) row 145742? Is there anything "odd" about that row?

(3) By Larry Brasfield (LarryBrasfield) on 2020-04-30 00:30:17 in reply to 1 [link] [source]

I think you'll need to show a bit more of what you are trying to do.

I just wrote this simple-minded Perl program, mkbigger.pl, to create mondo tsv files:

my $count = shift;
my $head = <>;
my $row = <>;
my @cols = split("\t", $row);
print $head;
for (my $i = 1; $i <= $count; ++$i){
  @cols[0] = sprintf("%d", $i);
  print join("\t", @cols);
}

then had this SQLite3 shell session:

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table Biggy7 (id integer primary key autoincrement, a text, b text, c real, d real, e blob, f blob);
sqlite> insert into Biggy7 (a,b,c,d,e,f) values ('a','b',3e8,1.38e-23,x'01',x'02');
sqlite> .mode tabs
sqlite> .headers on
sqlite> .once small.tsv
sqlite> .select * from Biggy7;
sqlite> .q

Then, at the OS shell:

> perl mkbigger.pl 600000 < small.tsv > vbiggy.tsv
> sqlite3

Then, in SQLite shell again:

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode tabs
sqlite> .import vbiggy.txt Bigger
Error: cannot open "vbiggy.txt"
sqlite> .import vbiggy.tsv Bigger
sqlite> .headers on
sqlite> select count(*) as rowCount from Bigger;
rowCount
600000
sqlite>

No problem arose with this. Of course, I was not taxing any machine memory limits with this, or working the filesystem much.

You might count the rows you did get in, then go look at your data and see if something special is there, just past the rows that were .import'ed alright.

(4) By anonymous on 2020-04-30 14:16:30 in reply to 3 [link] [source]

I recreated the tab delimited file and changed the order of the rows. The data in the file is the same, just the order of the rows is different. I ran the .import on the new file with the same results (only 145,742 rows imported). Seems like that proves it's not an issue with the import file other than possibly the size of the file itself (27,006 KB). I am doing this with SQLite 3.7.14.1 and am wondering if this is an issue with that version.

(5) By Larry Brasfield (LarryBrasfield) on 2020-04-30 17:17:50 in reply to 4 [link] [source]

That version is most of 8 years old. There have been improvements to the .import command since then, although none that appear to involve line limits. Maybe it's time to grab a current version.

(6) By anonymous on 2020-04-30 22:25:58 in reply to 5 [source]

I am using that version in conjunction with another product and I don't think they support more recent versions but I will check. For what it's worth, I created the tab delimited file with the original 7 columns but left all but two blank and all the rows import. That leads me to believe the problem has something to do with the size of the delimited file, not the number of columns. Thank you for your help!