4 ms·

Thats interesting, is there any way to add "custom delimiter templates" (or alternative tools)? I have some files where each line contains different kind of inf

Thats interesting, is there any way to add "custom delimiter templates" (or alternative tools)? I have some files where each line contains different kind of informations, but those are separated by their index on the row, so for example, customer name is the letter index 3 to letter index 10.

alexgarcia-xyz 4y ago
Hmm, so you have lines that have fields in fixed indexed positions? Maybe something line this: $ cat hn.txt 11alex a 12brian b 12craig c $ cat test.sql .header on .mode box .load dist/lines0 select substr(line, 1, 2) as id, substr(line, 3, 7) as name, substr(line, 11, 2) as age from lines_read('hn.txt') $ sqlite3x :memory: '.read test.sql' ┌────┬─────────┬─────┐ │ id │ name │ age │ ├────┼─────────┼─────┤ │ 11 │ alex │ a │ │ 12 │ brian │ b │ │ 12 │ craig │ c │ └────┴─────────┴─────┘ Unfortunately SQLite's substr function is a bit awkward, but if each field has constant indicies it may work
wodenokoto 4y ago
These I believe are what is called fixed width files. If you use pandas, there is a reader for those `pd.read_fwf()` I’m sure there are many more languages that support this, it’s just the only one where I had a need.
- bicijay 4y ago
  Exactly that, all banks here in Brazil are exporting transactions with this kind of format (Its called CNAB file over here and they are probably using COBOL systems). You have different kinds of CNAB files, CNAB 200 where you have lines with 200 characters, CNAB 400... Inside those files you have multiple chunks and every line contains information about a transaction. So if you wanna get the transaction amount you would look for all characters between character n10 and n20 for example. (Those positions are fixed and follow the bank template). We had success parsing it with python/node, but i think spinning up a sqllite instance, reading straight from the file and doing some calculations would be much faster.
  - muxator 4y ago
    Just thinking out loud and assuming you only have this extension and want to do a quick poc: you could try to delegate the preliminary line parsing to awk (which is very powerful and works in a streaming fashion). CNAB file -> awk -> select from line_read(). Awk could, for example, transform the fixed-width file in a delimited one (I believe this extension does not strictly require json-formatted inputs). You vould also perform pre-filtering of the input data on awk side (see the "/ /" syntax). If this extension supports reading from stdin, you could build a traditional unix command pipe, otherwise you would have to glue together the two commands with a named pipe. Depending on your sensibility, this could feel ugly or very elegant. I am confident it would be very fast, not memory bound, and can be made strict and correct.
    - MobiusHorizons 4y ago
      SQLite also has support for csv (even without this extension) so if you can process your cnab into csv that may be the easiest most supported way to go.

alexgarcia-xyz 4y ago

Hmm, so you have lines that have fields in fixed indexed positions? Maybe something line this: $ cat hn.txt 11alex a 12brian b 12craig c $ cat test.sql .header on .mode box .load dist/lines0 select substr(line, 1, 2) as id, substr(line, 3, 7) as name, substr(line, 11, 2) as age from lines_read('hn.txt') $ sqlite3x :memory: '.read test.sql' ┌────┬─────────┬─────┐ │ id │ name │ age │ ├────┼─────────┼─────┤ │ 11 │ alex │ a │ │ 12 │ brian │ b │ │ 12 │ craig │ c │ └────┴─────────┴─────┘ Unfortunately SQLite's substr function is a bit awkward, but if each field has constant indicies it may work

wodenokoto 4y ago

These I believe are what is called fixed width files. If you use pandas, there is a reader for those `pd.read_fwf()` I’m sure there are many more languages that support this, it’s just the only one where I had a need.

bicijay 4y ago
Exactly that, all banks here in Brazil are exporting transactions with this kind of format (Its called CNAB file over here and they are probably using COBOL systems). You have different kinds of CNAB files, CNAB 200 where you have lines with 200 characters, CNAB 400... Inside those files you have multiple chunks and every line contains information about a transaction. So if you wanna get the transaction amount you would look for all characters between character n10 and n20 for example. (Those positions are fixed and follow the bank template). We had success parsing it with python/node, but i think spinning up a sqllite instance, reading straight from the file and doing some calculations would be much faster.
- muxator 4y ago
  Just thinking out loud and assuming you only have this extension and want to do a quick poc: you could try to delegate the preliminary line parsing to awk (which is very powerful and works in a streaming fashion). CNAB file -> awk -> select from line_read(). Awk could, for example, transform the fixed-width file in a delimited one (I believe this extension does not strictly require json-formatted inputs). You vould also perform pre-filtering of the input data on awk side (see the "/ /" syntax). If this extension supports reading from stdin, you could build a traditional unix command pipe, otherwise you would have to glue together the two commands with a named pipe. Depending on your sensibility, this could feel ugly or very elegant. I am confident it would be very fast, not memory bound, and can be made strict and correct.
  - MobiusHorizons 4y ago
    SQLite also has support for csv (even without this extension) so if you can process your cnab into csv that may be the easiest most supported way to go.