Hi, I want to read all excel data (using sheet-to-json), when I run the example, the json file looks fine. But when I use my own excel file, the json cell labeling does not look right. Please see the picture for details.
The first one is the string, "This is the excel file:" and it missed the next label, then, "_1:"
May I know how to convert the column and row name in json back to normal in node-red?
I can not change the excel format 'coz it is from another system. Is there any information about the cell numbering?
Is that really an Excel file (.xls, ...) or is it a CSV file? (.csv, .tsv)
Either way, it is a poor format that's for sure.
If it is CSV, then use the csv node, skip the first line & make the next row the column headers.
I've not used the spreadsheet-in node so I don't know whether that has any way to do something similar but I'm guessing not. So short of walking the whole JSON object to change the column names yourself (or wrangling JSONata to do it for you), I think you are rather stuck.
If this is Excel and you are using a reasonably up-to-date version, you will likely find it easier to manipulate the origin data using PowerQuery to get more sensible output.
Unfortunately, unlike Python, JavaScript doesn't really have such good libraries for processing tabular data. Having had a lot of data to manipulate at work recently, I was myself looking for some updated tools but all of the JavaScript ones I found were poorly documented, didn't work properly or both. In the end, I stuck with PowerQuery though I did get some useful results from Python via the NumPy, etc. in a Jupyter notebook.
