Add fixed width format support

[jqnatividad]

Though not as common, a lot of legacy systems (mainframes, etc.) and even modern data feeds use fixed width format.

See https://stackoverflow.com/questions/7666780/why-are-fixed-width-file-formats-still-in-use and
https://news.ycombinator.com/item?id=38733617

[jqnatividad]

Create two commands:

tofwf - converts CSV to Fixed Width Format
fwf - reads a FWF file and converts it to CSV

tofwf will work in two modes:

• with a colspec file, specifying the widths of each column
• without a colspec file, qsv will infer and optionally create a colspec file. It will infer a colspec by running stats on the CSV and getting the max length of each column to create the col widths

fwf will work in two modes:

• with a colspec file
• without a colspec file. Without a colspec file, it will use the width of the column header to infer the width of each column

[terefang]

how about a colspec option, and header space-trimmed taken from the first line ?

$ qsv fwf --colspec '20,25,24,12,6,8,13' ...

$ qsv tofwf --colspec '20,25,24,12,6,8,13' ...

[terefang]

would you consider also the following use case (stellar catalogues):

--colspec 'A8,A2,I2,F4.1,F6.3,F5.1,I6,A12,A69'
--fieldspec `Name,Comp,min,DEm,pm,pmPA,HD,DM,Comment'

the above should be recognizable enough

[jqnatividad]

@terefang, adding --colspec and --fieldspec as CLI options is a good call. Will add it to the spec :)

[terefang]

looking at some of the fixed width data files i have ...

it might be beneficial to have an --header-lines N option.

how would this work ?

• if N == 0 – there are no column headers prefixed to the data ... need a fieldspec here.
• if N == 1 – the column names are entirely contained in the first line
• if N > 1 – the column names are spread across multiple lines (trim and concatenate ?)

[terefang]

the second example is even more complex:

• there are a total of N header lines.
• the first K lines need to be skipped
• then the next L lines are the column headers
• it may be that (N > K + L) – so some lines might need to be skipped again before data starts.

thinking about it this also relates to behead, eg behead should be capable to skip multiple header-lines in one run.

[jqnatividad]

Hi @terefang ,
Would you have sample colspec and fixed width data files for the various scenarios above I can use for testing?

Even if it has dummy data, it'd make it easier to stand up FWF support in qsv. I can also use them in CI to ensure it works as expected.

[terefang]

i think a good example is to use an existing debian package that contains the data.

• https://packages.debian.org/bookworm/gliese
• https://salsa.debian.org/debian/yale/-/tree/master/orig-data

the definitions for col/fieldspec are in the readme file while the actual data is in the .dat file

i will try to hunt down other freely available data for the other scenarios.

[terefang]

hmm ... i have found an even more strange record format, i had to handle in the past.

something similar to the second use-case (skip 1 line, header on 2nd line, skip another line), but the record format is more wierd as it adds the twist that the actual data records are delimited by an empty line and has extra spacings around columns.

dont know if you would want to handle this case also ?

i would give the colspec as: A13,S2,A13,S2,A8,S2,T23,S2,T23,S2,A30,S2,A8,S2,F7.1,S2,F7.1,S2,A30

Report of CVEs
id             source         status    created                  updated                  description                     severity  exploit  impact  reference                     
-------------  -------------  --------  -----------------------  -----------------------  ------------------------------  --------  -------  ------  ------------------------------
CVE-1999-0095  [email protected]  Modified  1988-10-01T04:00:00.000  2019-06-11T20:29:00.263  The debug command in Sendmail   HIGH      10.0     10.0    {"url":"https://seclists.org/fu
                                                                                          is enabled, allowing attackers                             lldisclosure/2019/Jun/16","sou
                                                                                           to execute commands as root.                              rce":"[email protected]"},{"url":"
                                                                                                                                                     https://www.openwall.com/lists/
                                                                                                                                                     oss-security/2019/06/05/4","so
                                                                                                                                                     urce":"[email protected]"},{"url":
                                                                                                                                                     "https://www.openwall.com/lists
                                                                                                                                                     /oss-security/2019/06/06/1","s
                                                                                                                                                     ource":"[email protected]"},{"url"
                                                                                                                                                     :"https://www.osvdb.org/195","s
                                                                                                                                                     ource":"[email protected]"},{"url"
                                                                                                                                                     :"https://www.securityfocus.com
                                                                                                                                                     /bid/1","source":"[email protected]
                                                                                                                                                     g"}                           

CVE-1999-0082  [email protected]  Analyzed  1988-11-11T05:00:00.000  2008-09-09T12:33:40.853  CWD ~root command in ftpd allo  HIGH      10.0     10.0    {"url":"https://www.alw.nih.gov
                                                                                          ws root access.                                            /Security/Docs/admin-guide-to-
                                                                                                                                                     cracking.101.html","source":"c
                                                                                                                                                     [email protected]"}                

CVE-1999-1471  [email protected]  Analyzed  1989-01-01T05:00:00.000  2008-09-05T20:19:36.257  Buffer overflow in passwd in B  HIGH      3.9      10.0    {"url":"https://www.cert.org/ad
                                                                                          SD based operating systems 4.3                             visories/CA-1989-01.html","sou
                                                                                           and earlier allows local user                             rce":"[email protected]","tags":["
                                                                                          s to gain root privileges by s                             Patch","Third Party Advisory",
                                                                                          pecifying a long shell or GECO                             "US Government Resource"]},{"u
                                                                                          S field.                                                   rl":"https://www.iss.net/securi
                                                                                                                                                     ty_center/static/7152.php","so
                                                                                                                                                     urce":"[email protected]"},{"url":
                                                                                                                                                     "https://www.securityfocus.com/
                                                                                                                                                     bid/4","source":"[email protected]
                                                                                                                                                     ","tags":["Patch","Vendor Advi
                                                                                                                                                     sory"]}                       

CVE-1999-1122  [email protected]  Modified  1989-07-26T04:00:00.000  2018-05-03T01:29:04.817  Vulnerability in restore in Su  MEDIUM    3.9      6.4     {"url":"https://www.cert.org/ad
                                                                                          nOS 4.0.3 and earlier allows l                             visories/CA-1989-02.html","sou
                                                                                          ocal users to gain privileges.                             rce":"[email protected]","tags":["
                                                                                                                                                     Patch","Third Party Advisory",
                                                                                                                                                     "US Government Resource"]},{"u
                                                                                                                                                     rl":"https://www.ciac.org/ciac/
                                                                                                                                                     bulletins/ciac-08.shtml","sour
                                                                                                                                                     ce":"[email protected]"},{"url":"h
                                                                                                                                                     ttp:https://www.securityfocus.com/bi
                                                                                                                                                     d/3","source":"[email protected]"}
                                                                                                                                                     ,{"url":"https://exchange.xfor
                                                                                                                                                     ce.ibmcloud.com/vulnerabilitie
                                                                                                                                                     s/6695","source":"[email protected]
                                                                                                                                                     g"}                           

CVE-1999-1467  [email protected]  Modified  1989-10-26T04:00:00.000  2017-12-19T02:29:08.393  Vulnerability in rcp on SunOS   HIGH      10.0     10.0    {"url":"https://www.cert.org/ad
                                                                                          4.0.x allows remote attackers                              visories/CA-1989-07.html","sou
                                                                                          from trusted hosts to execute                              rce":"[email protected]","tags":["
                                                                                          arbitrary commands as root, po                             Patch","Third Party Advisory",
                                                                                          ssibly related to the configur                             "US Government Resource"]},{"u
                                                                                          ation of the nobody user.                                  rl":"https://www.securityfocus.
                                                                                                                                                     com/bid/5","source":"cve@mitre
                                                                                                                                                     .org","tags":["Patch","Vendor 
                                                                                                                                                     Advisory"]},{"url":"https://ex
                                                                                                                                                     change.xforce.ibmcloud.com/vul
                                                                                                                                                     nerabilities/3165","source":"c
                                                                                                                                                     [email protected]"}