Removing zero-length strings from dataTable

Hey there,

I am facing an unusual issue while trying to process an .xlsx file with UiPath Studio.
In the image below there is a sample of my initial file.
image

Column C has blank values, there is NO data in those cells. For searching all of the blank cells in my excel file I used CTRL + A, then CTRL + G, clicked “Special”, Selected “Blanks” then pressed OK.
I use a read range (Excel.Activities) to read the file data and to pass it to a dataTable variable:
image

After this step, if I use a Write Range activity in order to create another .xlsx file EVERY blank space will get coverted to a zero-length string which means the previously empty cells apear empty but they actually contain a string with 0 characters. This zero-length string is equal to the “” value. As a refference, the " " value is 1 empty space (spacebar press).
Here is the output file(column D was manually entered to check the Column C values):
image

How can I bypass this issue? I tried a little workaroud by replacing each “” string with Nothing but the result is the same.

PS: “preserve format” does nothing
SAMPLE FILE:
sample file2.xls (32 KB)
Workflow file:
Test excel blanks.xaml (11.8 KB)

Thank you for your help.

What issue you are facing with this blank column data?

The output excel file will be used by another system/app. That system returns an error whenever detects a zero-length string and requires blanks.

Can you share screenshot of your error message?

This is the error message sent to me by the database guys:

[-1071607780] There was an error with output column “CodPostalC” (4627) on output “Excel Source Output” (9). The column status returned was: “The value could not be converted because of a potential loss of data.”.

have check on if following will help for testing the blank / space value

isNothing(row(index)) OrElse String.IsNullOrWhitespace(row(index).toString.Trim)

Unfortunately this doesn’t work.
I have attached a sample file(.xlsx) in the initial post

just show us what you had currently implemented and what is not working.

Kindly note: as this check line is a standard oneliner running successfully in many projects

Solved this issue by creating a VBA macro and deleting those zero-length strings directly into Excel.

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