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
