On Tuesday, 4 February, 2020 22:31, Keith Medcalf <kmedc...@dessus.com> wrote:
The vsv.c (source) file line counting is now verified and I have added a skip= parameter. Adding skip was far easier than variable separators ... Same location, file updated: http://www.dessus.com/files/vsv.c The complete collection: http://www.dessus.com/files/sqlite3extensions.zip Using tbartilde.csv containing: a|b|c|d~1|2|3|4~2|"3|5"|4|5~3|4|5|6~4|5|6|7 SQLite version 3.32.0 2020-02-05 05:21:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create virtual table t using vsv(filename='tbartilde.csv', header=on, fsep='|', rsep='~'); sqlite> .mode col sqlite> .head on sqlite> select * from t; a b c d ---------- ---------- ---------- ---------- 1 2 3 4 2 3|5 4 5 3 4 5 6 4 5 6 7 sqlite> drop table t; sqlite> create virtual table t using vsv(filename='tbartilde.csv', header=on, fsep='|', rsep='~', skip=3); sqlite> select * from t; a b c d ---------- ---------- ---------- ---------- 4 5 6 7 sqlite> drop table t; sqlite> create virtual table t using vsv(filename='tbartilde.csv', header=on, fsep='|', rsep='~', skip=4); sqlite> select * from t; sqlite> drop table t; sqlite> create virtual table t using vsv(filename='tbartilde.csv', header=on, fsep='|', rsep='~', skip=5); Error: premature end of file during skip sqlite> >On Tuesday, 4 February, 2020 05:19, Robert M. Münch ><robert.mue...@saphirion.com> wrote: > >>On 3 Dec 2019, at 16:10, Jannick wrote: > >>> would it be possible to add to the csv extension the following >>> parameter options (with syntax along the lines of): > >>> - sep=';': field separator character (different from default ',') >>> - skip=N: skip the first N lines > >>> Both options would be very helpful for information of fixed format >>> downloaded from information providers. > >>Hi, those would be very useful enhancements. > >I did some of the changes and you can get the resulting extension called >VSV (Variably Separated Values) from: > >http://www.dessus.com/files/vsv.c > >I added the facility to specify the field and record separator >characters. I did not add skip, though I may look at adding that too, >but one can simply use the OFFSET in SQL to ignore some rows at the >beginning of the file. I made some other changes also which makes this >non-compliant with the RFC. > >Data between the "field separator" markers can consist of any arbitrary >string of bytes that DOES NOT include the field or record separator >bytes. >Data between the double-quotes can consist of any arbitrary string of >bytes except that double-quotes must be escaped by doubling them. > >The added parameters are fsep=SEPERATOR and rsep=SEPERATOR for the field >and record seperators respectively. > >SEPERATOR is a single quoted string that may be in the following formats: > >'x' where x is any arbitrary byte and will be used as the separator >character. >'\x' for standard escape codes (tab = \t, vtab = \v, Formfeed = \f, >Newline = \n). >'\xhh' where hh is the hexidecimal code for the byte to use. > >defaults if not specified are fsep=',' and rsep='\n' > >so to read the following file: > >a|b|c|d~1|2|3|4~2|"3|5"|4|5~3|4|5|6~4|5|6|7 > >you can use the following commands: > >SQLite version 3.32.0 2020-02-05 02:43:27 >Enter ".help" for usage hints. >Connected to a transient in-memory database. >Use ".open FILENAME" to reopen on a persistent database. >sqlite> create virtual table tbartilde using >vsv(filename='tbartilde.csv', header=on, fsep='|', rsep='~'); >sqlite> .mode col >sqlite> .head on >sqlite> select * from tbartilde; >a b c d >---------- ---------- ---------- ---------- >1 2 3 4 >2 3|5 4 5 >3 4 5 6 >4 5 6 7 > >Might still have some line counting errors and haven't figured out how to >implement skip yet ... -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users