Conditional formatting

[E.T.S]

Hello,

I have an Excel file, and one pf the sheets is read and converted into a csv. The empty rows are auto filled and I would like to remove these by adding conditional formatting to the Excel file so that the cells are always blank - that way when I convert to csv they remain blank. But I am unsure how to configure Excel to do this?

[supermanPunch (Arpan)]

@E.T.S

and one pf the sheets is read and converted into a csv. The empty rows are auto filled and I would like to remove these by adding conditional formatting to the Excel file so that the cells are always blank

Hi @E.T.S ,

Is some parts of the Task already automated ? If so, Could you let us know How is the Conversion to CSV happening ?

[E.T.S]

Hi,

Thank you for your reply - I am using a write to csv activity.

[supermanPunch (Arpan)]

@E.T.S

I am using a write to csv activity.

Hi @E.T.S ,

This would mean that you are having the Data in the form of a Datatable. We could Filter/Remove the rows which are not needed and then update the Datatable, so that the CSV will not have those rows which are not required.

[E.T.S]

Hi

I have tried this but the csv output still has the auto filled cells

[supermanPunch (Arpan)]

@E.T.S

I have tried this but the csv output still has the auto filled cells

@E.T.S ,

Could you provide us or show us a Sample data ? We would also like to know what was done.

[E.T.S]

Can’t show the exact data but I have tried:

1. Read range → filter data table → write to csv
2. Read range → delete range → write to csv

[supermanPunch (Arpan)]

@E.T.S

Read range → filter data table → write to csv

@E.T.S ,

For the Highlighted one, Could you show us the configuration done in Filter Datatable activity ?

For Removing Empty rows, we could also try with the Linq instead of Filter Datatable :

YourDT = YourDT.AsEnumerable.Where(Function(x)Not (x.ItemArray.All(Function(i)String.IsNullOrWhiteSpace(i.ToString)))).CopyToDatatable

For Handling errors when all rows are empty, CopyToDatatable method gives out an Error and we could handle it by implementing it like shown below :

[FirstAid] Handling of The source contains no DataRows exception

This FirstAid Tutorial will describe how a the source contains no DataRows EXCEPTION can be handled. Introduction Let’s have a look at the following filter data table scenario: Name CCode Tom US Charlotte FR Seema IN Jean FR Assignment: Filter all rows on a particular Country Code like FR, UK, ES, UK… Ensure that all CCode data column values are trimmed Ensure that the Filter check is case insensitive we can implement it e.g. with the help of a LINQ statement: dtData.As…

[E.T.S]

The filter data table did not work as I could not configure it correctly - apologies for the confusion.

I have attempted to use the above code however I am still getting auto filled cells in the csv format:

[supermanPunch (Arpan)]

@E.T.S

I have attempted to use the above code however I am still getting auto filled cells in the csv format:

@E.T.S ,

Could you let us know the Configurations done on Read Range activity and Write To CSV activity ?

Is it the case that you are Reading the Excel sheet without Add Headers enabled and Writing to the CSV using Add Headers enabled ? If so, Could you uncheck Add Headers when using Write CSV activity and check ?

[E.T.S]

After unchecking add headers in the write csv file I’m having the same issue - I’ve tried this with both checking and unchecking the add headers in the read excel file activity

[supermanPunch (Arpan)]

@E.T.S ,

In that case, could you try to provide us with a Test data, a Sample Excel data which reflects the same format as the original but with Dummy data, where the same issue occurs when tested in your environment.

So that we can check with that data in our Environment and let you know if there is any change in the methods needed.

Do note that no screenshots of the Implementation were shares, so it is a bit hard for Debugging.

Although, you could also do the Debugging yourself and identify the point of Error :

Understanding the 6 Debugging Panels of UiPath in the easiest way possible!

One of the very important topic of UiPath in Debugging is understanding the different debugging panels and when to use which panel during debugging. In this video, we will understand all the 6 debugging panels of UiPath in the easiest way possible. Video Link: Understanding the 6 Debugging Panels of UiPath in the easiest way possible! - YouTube

[E.T.S]

The test data i.e. the Excel the looks like this:

The issue is that the read range auto fills the columns so it ends up like this:

[supermanPunch (Arpan)]

@E.T.S ,

I believe you would require to use Read Range activity without Add Headers enabled and then use Write CSV activity without Add Headers enabled.

Did you check with the above combination ?

[E.T.S]

I did but unfortunately it yielded the same result

[supermanPunch (Arpan)]

@E.T.S ,

It did work for my case :

You could check the Datatable value in the Debug Panel, it should appear in the below way, the row having Abc is recognised as a row and not as a column/header row.

Also notice, the Include Headers is Unchecked in Write CSV activity.

[E.T.S]

Please would you be able to share the xaml file - also I can see the auto filled column names in the immediate panels were they removed afterwards as the removal of these is what I am trying to achieve

Also should mention that when I put a delete range activity after the read range the data table doesn’t have those auto filled columns but when I convert to csv the auto filled columns return

[supermanPunch (Arpan)]

@E.T.S ,

Check the below Workflow :
Excel_ConvertToCsv.zip (8.6 KB)

First Check the Sample.xlsx file, it should be the similar data format that you have, Next Execute the workflow and Check the created Output.csv file.