in2csv: Importing messy data

[hseg]

I have multiple spreadsheets with a few metadata rows before the actual data. This trips up csvkit's automatic header detection. I'm processing them in bulk, so I can't rely on pre-counting the number of metadata lines for each.
In particular, this means that in2csv's behaviour where it automatically fills in the first line expecting it to be the header is counterproductive here -- I'm forced to invoke it as in2csv -H | tail -n+2.
Can in2csv be given an --ignore-missing-headers option or somesuch? I'll be piping it into xan or miller for further cleanup, so I don't need a cleverer handling method than that.

[jpmckinney]

If the input is CSV, then, by setting --no-inference (-I) and setting --sniff-limit 0 (-y0), you can skip the header logic:

$ printf 'foo,bar\n1,2,3' | in2csv -f csv -y0 -I
foo,bar
1,2,3

What is your input?

[hseg]

My input is my credit card's monthly statement. Unfortunately, they haven't
figured out that spreadsheets can have multiple sheets, so they've formatted it
as

Expenses summary,,,,,,
Account #### issued ####-##-##,,,,,,
Total NIS,Total USD,Total EUR,Cashback,,,
...
,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,
Local expenses,,,,,,
Account #### issued ####-##-##,,,,,,
CC name,amount,date,,,,
...
,,,,,,,,,,,,,
,,,,,,,,,,,,,
Foreign expenses,,,,,,
Account #### issued ####-##-##,,,,,,
CC name,amount,date,currency,,,
...
,,,,,,,,,,,,,
,,,,,,,,,,,,,
Details for local expenses
Account #### issued ####-##-##
CC Name,bill date,date,payee,amount,id,...
...

In particular, I at first need the bare minimum of an xls2csv converter that doesn't expect the result to be clean data. My workflow then has me splitting the output using csplit into the appropriate tables.

Though on further thought, it might be worth either using tools that can skip metadata lines (eg by prepending those lines with # and passing either --skip-comments or --pass-comments, as appropriate, to miller) or moving this metadata to the filename within the workflow.

Still, this does not avoid my need for in2csv to try not to be too clever (the one bit of cleverness I do need, which in2csv has but eg xan fails at (cf medialab/xan#758), is using the formatting information to convert the input))

[hseg]

Rereading my comment, I haven't clearly signalled that my input is a single-sheet xls spreadsheet.

[jpmckinney]

Can you upload a sample XLS that reproduces the issue?