Hi All,
I'm using advanced Excel plugin to extract user data from the uploaded file, but when I run the "Worksheet_Select" server action, I'm receiving the following error: 'The given key was not present in the dictionary'. It is occurring sometimes when I do copy/paste some records into the same file.
Attached the file which I'm having issues with, it was written manually up to the phone number field and the last four columns are copied from the same file. Let me know if anyone have idea to resolve this issue.
Hi Sonali
The Advanced Excel component uses the EPPlus library which in turn leverages the OpenXML document standard from Microsoft. If WPS doesn't respect that standard, then it will break the underlying XML of the document and EPPlus will not be able to read it.
I couldn't find any component to read WPS files in the Forge, and I also couldn't find an SDK for WPS documents.
Hope this helps.
Hanno
Hi @Sonali Murugan
This is common issue wherever name mismatch happens this error will occur, Below the same issues from another scenario,
https://www.outsystems.com/forums/discussion/81918/the-given-key-was-not-present-in-the-dictionary-in-my-table-sorting/
Thanks
Murugan S S
Hi,Can you please check with this excel,1.Open the file, check the actual sheet name, and match it with what you're passing in OutSystems 2.Try renaming the sheet name Thanks,sahana
Hello @Sonali Murugan ,
Try changing the name of your sheet from the default one. I tried with a random name and it worked.
Also make sure to map the right values in the input fields, for your case I just used Index 1.
I attached the updated XLSX as well.
Hi @Mihai Melencu
I tried the same by changing the sheet name but it is not working for me. I'm still getting the same error.
Could you share your OML file so we can identify the issue?
@Sonali Murugan according to some results from Google that error seems to come up when the sheet name contains a space.
I couldn't spot any obvious issues with the document you shared, so I'm not sure that the issue is with the file.
Are you able to share the .oml as Mihai mentioned?
Hi @Hanno, Attached OML for your reference. It doesn't happen all the time but it happens when you copy/paste some column data.
Hi @Hanno & @Mihai Melencu,
Sorry, It was confidential so I cannot share it. But I just passed only the binary file. Attached a screenshots for your reference.
Can you try removing the WorksheetName in the Worksheet_Select. Use just the index.
Hi @Mihai Melencu , I tried by removing the sheet name but still it is not working. Attached sample OML for your reference.
Hi, you attached the excel file, not a sample OML. The issue might be with the input parameter of your binary data; make sure that input is mapped correctly and references the right resource. Also, you could try using the binary directly:
Resources.UserData_xlsx.Content
Hi @Mihai Melencu Sorry wrongly uploaded, I attached again in this thread.
Also I tried passing 'Resources.UserData_xlsx.Content' file directly but this also not working.
Hi, the sheet name still seems to be the issue. I changed the sheet name from Data to UserData and I don't receive any error. Can you please do the same and check?
Hi @Mihai Melencu , I tried but still the same issue persist.
Do we have any other plugin which works same as advanced excel? Also can you share your OML I can check once.
Many thanks,
Hi Solani,
I noticed that you used WPS to save your Excel file, and I believe the error is due to that. When I saved the Excel file you provided using Microsoft Excel, the action worked correctly. After some investigation, I found that EPPlus does not support files saved by WPS. You can find more information here.
Do you have access to Microsoft Excel, or could you try using another program to save your file?
Hi @Beatriz Sabino In this case, the extraction will be based on the file uploaded by the user in the front-end. Therefore, the user may use WPS or Microsoft Excel to upload.
So do we have an option to extract data from both WPS Excel and Microsoft Excel?
Hi @Hanno, Thanks for your information.
I created an Excel file in Microsoft Excel and copy/pasted the records and columns, and it extracted the data successfully, and this is happening only with WPS Excel files. I'll find out some other way.
Many thanks.