Error while reading excel file using Workbook Read Range activity

praveen_satya · July 16, 2019, 10:08am

Hi all,

I am facing an error while trying to read an excel report downloaded from SAP having 71 Columns using Workbook Read Range.

Source: Read Range
Error Message: Row number must be between 1 and 1048576
Exception Type: System.IndexOutOfRangeException

An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is:
System.IndexOutOfRangeException: Row number must be between 1 and 1048576
at ClosedXML.Excel.XLWorksheet.Row(Int32 row, Boolean pingCells) in C:\Projects\ClosedXML\ClosedXML\Excel\XLWorksheet.cs:line 1368
at ClosedXML.Excel.XLWorkbook.LoadRows(Stylesheet s, NumberingFormats numberingFormats, Fills fills, Borders borders, Fonts fonts, XLWorksheet ws, SharedStringItem sharedStrings, Dictionary2 sharedFormulasR1C1, Dictionary2 styleList, Row row) in C:\Projects\ClosedXML\ClosedXML\Excel\XLWorkbook_Load.cs:line 1329
at ClosedXML.Excel.XLWorkbook.LoadSpreadsheetDocument(SpreadsheetDocument dSpreadsheet) in C:\Projects\ClosedXML\ClosedXML\Excel\XLWorkbook_Load.cs:line 227
at ClosedXML.Excel.XLWorkbook.LoadSheets(String fileName) in C:\Projects\ClosedXML\ClosedXML\Excel\XLWorkbook_Load.cs:line 45
at UiPath.Excel.WorkbookFile…ctor(String workbookPath, String password, Boolean createNew)
at UiPath.Excel.Activities.WorkbookActivity1.ConstructWorkbook(String path, String password, Boolean createNew) at UiPath.Excel.Activities.WorkbookActivity1.BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, Object state)
at System.Activities.AsyncCodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.ActivityInstance.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)
![ReadRangeError|690x245]![ReadRangeError|690x245]

If anyone has found any solution for this error, Please share in it comments.
I want to use only workbook read range.

Regards,
Satya.

lakshman · July 16, 2019, 9:20am

@praveen_satya

Could you please help me with screenshots of that workbook read range Activity and it’s properties once. Will check and update you.

praveen_satya · July 16, 2019, 9:24am

lakshman · July 16, 2019, 9:28am

@praveen_satya

It’s looks ok. How many no. of rows that Excel file has ?

I guess it’s contains many no. of rows and because of that you are getting this error. Could you please specify some range and once try it.

AshwinS2 · July 16, 2019, 9:33am

Hi @praveen_satya

Try to give the range as “A1: A+datatable.rows.count.ToString+”

Thanks
Ashwin.S

Palaniyappan · July 16, 2019, 9:41am

Hi
Welcome to uipath community
Fine
Kindly uninstall the EXCEL package in the Design Tab -> Manage Packages -> Project dependencies ->uninstall the excel package and again reinstall by going to Official tab -> search for Excel.Activities and install it
–use a read range activity and mention the range as “” no space in between and the sheetname and workbook path same as how its mentioned now

Kindly try this and let know for any queries or clarfication
Cheers @praveen_satya

praveen_satya · July 16, 2019, 9:51am

Hi all,

Ashwini - I could not able to read the data to get datatable. I have given the exact range as well still the same error.

Lakshman- I believe Rows count is not a problem here, i am getting the same error for files with data of 10000 rows and 10 rows as well.

praveen_satya · July 16, 2019, 10:03am

Hi Palaniyappan,

I did the same still the same issue.

I can read the same file with Excel Application Scope Read range but problem here is when i run in VDI, bot is in reading mode for hours (Ex: 10 hrs) and It is not even throwing any error after 10 hrs.
So that’s why i want to read data using Workbook Read Range.

I have even tried to read excel file as database. I could able to get datatable but that is returning me null values.

vishnuvarthanp · July 16, 2019, 11:31am

HI Praveen

Are u getting the same error irrespective of the files u r reading ?
can you make the range as blank and try

praveen_satya · July 16, 2019, 12:02pm

Hi Vishnu

I couldn’t able to read the reports which i have downloaded from SAP. Normal Excel files i could able to read with workbook Read Range.

Range Property - I have tried with all possible ways with blank(""),given the exact range and taken dynamically as well. No Luck same error.

I couldn’t figure out what could be the error. Error message shows Index out of Range Exception.

praveen_satya · July 17, 2019, 12:53pm

Hi Guys,

I found a solution by copying the data from downloaded Report to a new Workbook using VB script and then Reading that new Workbook file. It is working like that.

May be Data got corrupted when it got downloaded from SAP.

Juan_Esteban_Valencia · December 14, 2020, 8:47pm

Hello @praveen_satya , could you help me by explaining how to use the vb script or more specifically how the code would be for this to work, I am having this same problem when downloading excel files from SAP