On Fri, 25 Oct 2013 12:11:42 -0700
"Joe.Fisher" <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
