Filter CSV Files

By using this workflow, you can automatically read a .csv file, write in the .csv files and also append data from one .csv file to another .csv file.

This example illustrates how to create an automation process that uses the Write CSV,Read CSV and Append To CSV activities. You can find these activities in the UiPath.Excel.Activities package.

To exemplify how to use these activities, we have created a sequence that includes a Write CSV, Read CSV and an Append To CSV activity.

This is how the automation process can be built:

1. Open Studio and create a new Process.

2. Drag a Sequence from the Activities panel to the Workflow Designer.

   Note: Add your .csv file to the project directory in order to be able to run the entire process from the same place.

3. Drag a Read CSV from the Activities panel in the previously created Sequence.

   • In the Properties panel, add the full path of the file you want to read the data from to the FilePath property.
   • Select the IncludeColumnNames check box. This way, when running the process, the first row in the .csv file is considered to contain the column names.
   • Create a new DataTable variable called transactionTable.
   • In the Properties panel, add the newly created variable to the DataTable property field.

4. Drag a Build Data Table activity from the Activities panel and place it below the Read CSV activity.

   • Create a new DataTable variable called transactionsFiltered.
   • In the Properties panel, add the transactionsFiltered variable to the DataTable property field.

5. Add another Build Data Table activity from the Activities panel and place it below the previous one.

   • Create a new DataTable variable called totalTable.
   • In the Properties panel, add the totalTable variable to the DataTable property field.

6. Drag a For Each Row activity below the Build Data Table activities.

   • In the Properties panel, add the transactionTable variable to the DataTable property field.

7. Drag a Write CSV activity under the For Each Row activity.

   • In the Properties panel, add the full path of the file in the FilePath property. In this case the path can be set as "TransactionsFiltered.csv".
   • In the Properties panel, add the transactionsFiltered variable to the DataTable property field.
   • Make sure to select the AddHeaders check box. By doing so, you add the column names from the DataTable to the output .csv file.

8. Add a Read CSV activity to the Workflow Designer and place it under the Write CSV activity.

   • In the Properties panel, add the full path of the file you want to read the data from, to the FilePath property. In this case you can write as a path "TransactionsFiltered.csv".
   • Check the IncludeColumnNames option. This considers the first row of the .csv file to have the column names.
   • Add the transactionsFiltered variable in the DataTable output field, from the Properties panel.

9. Add a While activity below the Read CSV.

   • In the Properties panel, set the Condition field to counter<transactionsFiltered.Rows.Count.
   • Drag a Sequence activity in the body of the While activity.
   • Inside the Sequence activity, add three Get Row Item activities.
   • All Get Row Item activities should have in the Row field the following text transactionsFiltered.Rows(counter).

10. Drag four Assign activities right after the Get Row Item activities.

    • Create four new Double variables sumCashIn, sumOnUsCheck, sumNotOnUsCheck and an Int32 variable named counter.
    • In the first Assign activity add in the To field the sumCashIn variable and in the Value field add sumCashIn+cashIn.
    • In the second Assign activity add in the To field the sumOnUsCheck variable and in the Value field add sumOnUsCheck+onUsCheck.
    • In the third Assign activity add in the To field the sumNotOnUsCheck variable and in the Value field add sumNotOnUsCheck+notOnUsCheck.
    • In the fourth Assign activity add in the To field the counter variable and in the Value field add counter + 1.

11. Add a Add Data Row activity under the While activity.

    • In the Properties panel, add {sumCashIn,sumOnUsCheck,sumNotOnUsCheck} in the ArrayRow field and the totalTable variable in the DataTable field.

12. Drag an Append To CSV activity below the Add Data Row activity.

    • Add the path of the file. In this case it is "TransactionsFiltered.csv".
    • In the DataTable field add the totalTable variable.
13. Run the process. Once the automation is completed, you have two .csv files in your project, the initial one and a newly created one that includes all the filtered data.

Download example