Improve Excel ingest documentation for troubleshooting

[amberleahey]

Hi! We have had questions about excel data upload failures and the existing documentation here isn't very helpful for troubleshooting unfortuantely
https://guides.dataverse.org/en/latest/user/tabulardataingest/excel.html

It would be helpful if there was a template or list of conditions provided here for end users to troubleshoot why their data failed to load. An example of an acceptable spreadsheet structure would also be great, even visuals.

Is this something that can be developed to support users? I'm not familiar with all the conditions and exceptions for Excel data upload but perhaps this is something you have mapped out?

Thanks in advance,

[pdurbin]

@amberleahey I definitely agree the documentation could be improved.

As far as examples, sure, they could probably go in the guides. Another place to fine example files is the "sample data" repo. @philippconzett contributed an Excel file in IQSS/dataverse-sample-data@f3ef7ee that ingests fine and looks like this:

So maybe the guides could show a few examples and suggest the "sample data" repo for more. Something like that.

[amberleahey]

Yes that would be wonderful, thanks , its a common problem we see, would be good to address this more concretely with researchers if we want them to upload their data in this way.

[shlake]

Excel files with multiple sheets get ingested successfully (?) (I haven't had one to fail) BUT only the 1st sheet is used to create the new ".tab" file. The other sheets are lost in the tabular ingest.

BUT at least the original .xlsx file can be downloaded (via GUI) and still has all the multi sheets.

Problem with downloading the "file" via Download URL - only the ".tab" file is downloaded (via the link shown on the file page), which in this case is not the complete file.

I try to teach my researchers NOT to use multiple sheets, but hey - excel let's them do it, so they do.

[adam3smith]

I don't know if this is the best place to add additional failure conditions, but since I didn't find a better one:
Line breaks within cells (which are legal in Excel) break ingest. This may be our most common reason for failure.

[pdurbin]

@adam3smith thanks, yes, it's fine to mention more cases here. That one also happens to have a dedicated issue:

• Ingest: line breaks in Excel cause ingest to fail Ingest: line breaks in Excel cause ingest to fail  #6874

@shlake thanks for mentioning the multiple sheets issue. This limitation should definitely be mentioned in the guides.

Finally, I just noticed that https://guides.dataverse.org/en/5.5/user/tabulardataingest/excel.html has very little content (this is the point @amberleahey was making at the top of this issue). If anyone out there would like to volunteer to get a pull request started, I think any rewrite, however minor, would be a huge improvement. It sounds like there's a lot of real-world experience out there!

[BPeuch]

Hello @shlake, do you know if the multiple-tab problem you mentioned has already been documented in a dedicated issue here? I thought it had been but I can't find it.

[shlake]

Hi @BPeuch I do not think that the multiple-tab (multiple-sheet) problem has its own issue.

I added this particular problem after @pdurbin confirmed in the Google Groups: https://groups.google.com/g/dataverse-community/c/XSuWTOK9JW4/m/TgtCQqAMAAAJ?hl=en

[dvictori]

I've just stumbled on this multiple sheet issue. I believe using multiple excel sheets is a very common practice and asking the researchers to split their files is not an easy task. How about, during the ingest procedure, if an excel file has multiple sheets, it be broken into multiple tab files? So myData.xlsx with two sheets would become myData.sheet1.tab and myData.sheet2.tab?

[pdurbin]

@dvictori I like that idea but of course it needs to go through our design process. And for that it would be nice to have a dedicated issue (a feature request). Do you mind creating one? This issue is more about improving the documentation. (Improvements on https://guides.dataverse.org/en/5.9/user/tabulardataingest/excel.html are still welcome! The source file is at https://github.com/IQSS/dataverse/blob/develop/doc/sphinx-guides/source/user/tabulardataingest/excel.rst .)

[adam3smith]

One thing to think about of with the proposal to automatically split XLSX files is that going from one to many files is a significant conceptual departure of how ingest currently works, with some potentially thorny issues, e.g., how to handle API calls and PIDs.

[dvictori]

I just created a feature request. Since I'm just an user, I have no idea how thorny an issue this is. But I do realize it's a big change. So thanks for listening

• Feature Request/Idea: How to handle excel files with multiple sheets #8518

[pdurbin]

Just a quick thank you to @kaitlinnewson for the following pull request that doesn't close this issue but greatly improves the docs on Excel:

• Excel ingest docs #8541

If anyone wants to work on the docs some more, please pull the latest from the develop branch to pick up these changes.