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