excel command: parse excel "error" values

[abcvjp]

Hi. I really love qsv especially the 'excel' command, but there is a small behavior of that command that doesn't fit our requirement

Is your feature request related to a problem? Please describe.
The "error" values in excel file are converted unexpectedlly. E.g.: #ERROR! -> Error, #DIV/0! -> Div0, #NULL! -> Null, ...

Describe the solution you'd like
Keep the original value or convert to a constant specified value

[ondohotola]

I think this is a very good idea

[jqnatividad]

qsv excel just returns the CellErrorTypes returned by the calamine Excel-parser crate:

https://github.com/tafia/calamine/blob/953d80ee66d80ecbeabf14670841ea6cc0e1b006/src/lib.rs#L96-L132

I can certainly add an --error-format option to use a constant value, but I will have to investigate if I can get the unparsed cell as a string from calamine.

If I can retrieve the unparsed cell as a string, I'm thinking of using three special values for the --error-format option 1) "{error-prefix:}" with a default value of "!"; 2) "{error-type}" and 3) "{error-origvalue}"
The error-prefix is prepended to the unparsed cell string causing the error and if specified, the {error-type} will be inserted in the specified position per the --error-format template. e.g.

If a cell's formula is causing a division by zero error and --error-format is set to "ERROR {error-type}: {error-origvalue}" it will return:

!ERROR Div0: 100/0

If --error-format is set to '{error-prefix:!!!ERROR!!!}-{error-type}: "{error-origvalue}"', it will return:

!!!ERROR!!!-Div0: "100/0"

If --error-format is simply set to "{error-origvalue}, it will return:

!100/0

If the --error-format is not set, it will return the error type with the "!" prefix if an error is encountered, i.e.:

!Div0

WDYT?

[abcvjp]

Yes, it would be very good if we are able to format errors with type and original value. For me, I just need a prefix for distinguishing the error cell from the normal cell

[jqnatividad]

Hi @abcvjp ,
012aa3f improves excel error handling as it now returns the expected value, not the debug value - i.e. #ERROR! -> #ERROR!, #DIV/0! -> #DIV/0!, #NULL! -> #NULL!, etc,

In the meantime, i'm asking calamine's maintainer if its possible to get the "original value" (tafia/calamine#416)
If so, I'll implement the --error-format option.

[jqnatividad]

Closing this as its been fixed with 012aa3f.

Created #1689 for the --error-format enhancement.

[jqnatividad]

@abcvjp, @ondohotola - with #1721 merged, excel now has a simplified version of the --error-format option.

Please let me know how it works for ya'll.

Interestingly, it seems fetching the formula text only works on macOS per the GH action runner tests for Ubuntu Linux and Windows. Would be interested to see if you experience the same behavior on your computers.