On Fri, 25 Oct 2013 12:11:42 -0700
"Joe.Fisher" <joe.fis...@tanguaylab.com> wrote:

> Is it possible to import data by column number without using any
> delimiters?
> 
> We have massive text based data files where each record of the data
> is segmented and consistent (Col 1-3, 4-9, 10-12, etc.).

awk is your friend.  

Here's an undelimited line:

$ jot -c -s '' 50 65 97
ABBCDDEFFGHHIIJKKLMMNOOPQQRSSTUUVWWXYYZZ[\\]^^_``a

Here's that line delimited with tabs into substrings as you describe: 

$ jot -c -s '' 50 65 97 | awk 'BEGIN{ OFS="\t";} \
        {print substr($0,1,3), substr($0,4,5), substr($0,10,2) }' 
ABB     CDDEF   GH

Let's make a little table: 

$ sqlite3 /tmp/db 'create table T(a,b,c);'

and populate it, and select from it

$ jot -c -s ''  50 65 97 \
| awk 'BEGIN{ OFS="\t";} \
  {print substr($0,1,3), substr($0,4,5), substr($0,10,2) }' \
| sqlite3 -separator ' ' /tmp/db '.import /dev/stdin t' && \
sqlite3 -column -header  /tmp/db 'select * from t;'
a           b           c         
----------  ----------  ----------
ABB         CDDEF       GH        

Rather than hard-coding the offsets for very long lines, I'd be tempted
to make a table of offsets and lengths (and maybe names and types), and
generate both the DDL and the awk script from it.  

--jkl

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to