Xlsx-utils not working

I am trying to use the xlsx-utils plugin on TW 5.2.2

I have simplified things down to a workbooks with just onw sheet and one row on that sheet, with one column.

Whenever I start the import of the xlsx file I get an error in a yellow box:

xlsx-utils - 9th October 2022 at 2:01pm (count: 80)

Missing title field for {“title”:""}

Now you would think I had something missing from my set-up of the import parameters, here is my configuration:

In the workbook the sheet is named “property” and the column is “Building”.

Any clues? Thanks.

XLsX-utils is by its nature complex, and to help we would really need it installed and the same or similar test data and your workflow.

To simplify things I would go to what I know well and install JSON mangler in a standalone wiki, export from your XLSX a column delimited sheet as a CSV file, import it to the JSON Mangler wiki and use its tools to import each row as a tiddler within a plugin. Then transfer this to the wiki that wants the data.

Regards

Hi @myfta it’s not immediately obvious what’s wrong (but it’s a long time since I worked on the XLSX Utilities plugin). Could you kindly post another screenshot of the configuration, but this time also click the edit buttons on the workbook and sheet, so that we can see the full configuration. Also, just to be clear, is there some text in the single cell of the worksheet? Many thanks.

a really simple one, but do you have data below the column title?

e.g.

it could be trying to import data but not finding it, if there is no data

PS i realise you’ve probably editing the each row section to show the set field data, but i am trying this out with a fresh demo and noticed the import would not work for me from a saved copy of https://tiddlywiki.com/editions/xlsx-utils/ until i saved and reloaded the wiki after saving changes in the import specifications (i think this is not your issue, though!)

@jeremyruston Here is the complete config.

@makiaea this is the workbook, much the same as yours:

Capture2

looks good. just to check, you successfully ran the demo import included in https://tiddlywiki.com/editions/xlsx-utils/ ? the one with the states and presidents? that’s the one i successfully tested with just now.

also, what version of excel are you using? it’s possible from your screenshot that you are using an older version of excel (prior even to excel 2016)? excel might be set in your options to produce xls files instead of xlsx files (i believe the default xlsx-utils requires xlsx files?)

@makiaea the states and presidents works from the demo site. However, if I create a new import specification for my simple test workbook it fails on import with the same error message:

Missing title field for {“title”:""}

I definitely have an xlsx file (I have changed the file extension here from xlsx to json so I can upload it, but I guess you can change it back to xlsx).
So you should be able to test it?
nth_TEST.json (7.9 KB)

@makiaea I think you may be onto something with the xslx file format. I opened the xslx file in LibreOffice and saved it as a new xlsx file (new file name rather than overwriting). It imports into xlsx-utils without the error!

The file had originally been created in Excel but the new version was slightly smaller at 5208 bytes compared to 8127 bytes.

So it seems that not all xlsx files are alike, and specifically there seems to be a issue with the format of the Microsoft one.

@makiaea @jeremyruston
My hope of resolving (or working round) this issue using LibreOffice was short-lived since once I started to add more data to the Excel file, albeit in LibreOffice, it too started to fail on import in exactly the same way. It is like the routine is not reading that data in the cells in the Excel file format.

@makiaea @jeremyruston
A lot more testing and it seems the problem with Xlsx-utils is that when Excel saves a workbook it often adds multiple blank rows below your data. It seems to happen if while Excel is open a cell is elected below the data area even though no entry is made in that cell or row. I discovered this by savig the worksheet as a CSV and opening it in a text editor and multiple blank rows were revealed.

So when saving a workbook you need to ensure that there are no empty rows by the following method. Select all the rows below your last valid data row and right-click - Delete (Clear contents is not sufficient). Otherwise Xlsx-utils would need to be modified to ignore rows below the last valid row of data.

The checkbox in the configuration of the import specification “Skip this tiddler when field blank” also seems to fix this problem , but that might have been intended for data with with spaces between each valid data row.

Remember my suggestion for spreadsheet originating table content, going via CSV, this should bypass a lot of the complexity of trying to use open or proprietary spreadsheet formats.

Here I obtained a spreadsheet and exported a worksheet as CSV, I then dropped it on TiddlyWiki.com and I had to edit it to have a type pf text/csvand this is what you see;

This is already quite usable, depending on your needs, but you can write filters to retrieve each line and split on delimiters and use the data in the table.

However if you use the JSON Mangle Plugin you can create a tiddler from each row, just don’t use fieldnames of default fields, and remember you are addressing shadow tiddlers.

  • This allows you to then to manipulate each row using standard tiddlywiki methods

As I said before;

Thank you, I started exploring this alternative solution when I discovered the blank row issue in the way that Excel was saving the workbook.