How to read formatted file in sqlite?

Question

Is it possible to read formatted file in SQLite? I have a file with rows like this (two rows below):

   1921.300       .      .    <  0.030     .         .     .       .     .     550        1.6   1 Mrr1922  Jm   5     
   1973.220    158.     3.       0.240    0.002      .     .      1.5   0.5    620        5.1   1 Lab1974  S    4

and description like this:

term       columns   format   description

date       008-017   f10.5    Observation date, in years.                  
tflag      019-019   a1       Flag for theta (position angle) measure.
.....................
etc.

I need to read this file into my SQLite table.

This may be a duplicate of a [previous question](http://stackoverflow.com/questions/1045910/how-can-i-import-load-a-sql-or-csv-file-into-sqlite), but your file seems to have fields with fixed width instead of delimiters (I say that because of the dot positions and less than sign). — Paulo Almeida, Jul 13 '13 at 17:22

score 0 · Answer 1 · answered Aug 04 '13 at 22:59

0

I think what you probably want to do is convert your file to comma- or tab-separated text (say by loading into Excel then exporting). Then in sqlite3:

.mode tabs
.import myfile.txt

answered Aug 04 '13 at 22:59

Jonathan Warden

2,492
1
17
11

Thanks @Jonathan Warden but I have missing values in the file. – drastega Aug 11 '13 at 17:50
Sqlite will still be able to import TSV/CSV with missing values. As long as every line has the same # of tabs/commas. For example, a line that just contains ",,," is totally legal, as long as all other lines in the file have three commas (e.g. four fields) – Jonathan Warden Aug 12 '13 at 22:20
Unfortunately in my case every line has the different # of tabs/commas. – drastega Aug 13 '13 at 12:01

How to read formatted file in sqlite?

1 Answers1