Excel Error: 'Column Index' is not set or is invalid

Help Studio
, ,
1

I have an excel file that checks the names in column A to determine the value in column B. However, I’m getting this error upon execution “Lookup Data Table: In the ‘Lookup Column category’ the value for argument ‘Column Index’ is not set or is invalid.” In the demo table I created, everything works well. But in my main sheet, it simply throws this error. Not sure what is causing it. Below is a screenshot.

2023-04-25_213940
2023-04-25_213940986×686 39 KB

2

Hi @private_matter ,

Could you perform a Debug and check and what is the Datatable value after the Error is received ?

3

@private_matter

This usually get if the name of the column not found

You check the column Name correctly in Excel also check if there any spaces to that

Also you can debug the flow and put a breakpoint after the Look up activity, so that you can see in Locals the column value as well

Thanks,
Srini

4

@supermanPunch @Srini84

Forget what I said. It’s a disaster. With 30000 rows and only 2 columns of data, it took 1 min 17 seconds to retrieve the value. And the value is actually on the 7th row on top. I have almost 200K names. No thank you, but the activity is not up to the task to handle large databases. Will have to look for an alternative method.

5

@private_matter ,

Was this Checked in Debug Mode or Run Mode ?

Also, Let us know if the Look Up Datatable activity was taking too much time as you mentioned.

6

Both debug file and run file.

7

You can try @supermanPunch tip which is reading the excel first as data table and then use Look Up Datatable activity.
Other alternative for bigger files is using Excel as datasource and execute SQL Query?(How to use Excel as datasource and execute SQL Query?)

You can find many topics around this method :wink:
(20) Session 4-Select Query to Excel As Database in UiPath | Excel As DB Automation UiPath - YouTube

I hope this will help you to solve the issue. If so, kindly please mark this as a solution.

Cheers,
Kamil

8

Hi @private_matter

Give a try with the linq query to filter column 1 with a value and then take column 2

GenderType= If(GenderCheckerDataTable.AsEnumerable().Where(Function(r) r(0).ToString.Trim.Equals(“Aaberg”)).Count<>0,GenderCheckerDataTable.AsEnumerable().Where(Function(r) r(0).ToString.Trim.Equals(“Aaberg”))(0)(1).ToString,“No value”)

So this will assign a blue to GenderType variable if required value is found else assign No value to the variable

Try this and let me know if it works for you

Thanks and Regards
Nived N

9

@private_matter
since it is taking so long due to having so many rows, you can convert this excel xlsx to csv file

then use read text file activity to read the whole csv file as a single string, then use regex to lookup column B value based on column A. Should be much faster

10

@private_matter

Multiple ways

  1. Try using look up excel activity and check if that helps
  2. You can aso try using find/replace activity on excel directly
  3. Use excel as a database and then use select query to get only the required row using where clause
  4. Use read range and then instead of lookup use filter datatable and then get the required column value
  5. Use read range and then use linq dt.AsEnumerable.Where(function(x) x(0).ToString.Equals("Aaberg")).Select(function(x) x(1).Tostring)(0) to filter and get the required value…here better to first check count before doing (0) to know if any value is retrieved or not and then get the value

Hope this helps

Cheers

1 Like
11

@Anil_G

Finally got it working by using Lookup activity. There was no need to create a datatable which was what was causing the huge delay as its iterating for every row like a scanner.

Cheers

1 Like
12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.