Run Query does not read #N/A Values

Hi

I am trying to read a column from an excel sheet using database activities, the output does not read #N/A Values
This is an example of my input data in text format
image
Output for datatable is like A,B,A,B but #N/A not reflected in that

My query : “SELECT [Column Name] FROM [Sheet Name$]”

Please help me with this

The #N/A value in your Excel data is probably treated as an error, and this might be the reason UiPath is not able to read it. You can avoid it by using “IFERROR” function of excel sheet for those cells.

  1. Open your Excel.
  2. Select the cell which may contain the error.
  3. In the formula bar write =IFERROR([Your Formula],"#N/A")
  4. Press Enter.

Now instead of giving an error result, Excel will return “#N/A” as a string if there is an error.

You should also update your query to read the value as string. This will tell UiPath that the value is a string and needs to be read, even if it is “#N/A”.

Then you retry to read the column from the Excel sheet with the updated data and query.

However, if this does not work another solution you could try is to use Excel activities in UiPath Studio to read the entire sheet, and then use the .ToString method in an Assign activity to convert the column values to string.

If you want to get the data in the same format (e.g., A,B,A,B), you could try using the .Select method on the data table to create an array of rows where the value in the column is not null, and then use String.Join to concatenate the values.

For example:

dt.AsEnumerable().Where(Function(row) not String.IsNullOrWhiteSpace(row.Field(Of String)("ColumnName"))).Select(Function(row) row.Field(Of String)("ColumnName")).ToArray()

This will create an array of strings (omitting the null or empty), which you can then join into a single string:

String.Join(",", yourArray)
1 Like

Hi @marian.platonov

It works, and thanks a lot.

Specifically, I am using database activities because the data has a huge volume.

Regards,
Km8

1 Like

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