I am very new to UPath and have searched the forum and can’t quite find the issue I am trying to solve.
I want to run a process to delete all blank rows in every file (Excel) in a folder. Right now my sequence flow is:
Assign–Directory.GetFiles
For Each–currentfile = files (files is a vafriable of string
—this is where I need to figure out how to do the remove blank rows on each file
I attempt to assign this new a new data table variable so I can then filter out the blank rows, but this won’t work because I am assigning a data table to data rows, so I need to figure out the right option.
I hope I’ve asked this question properly. Thank you
Try these steps:
1: Assign to get files into arr_Files (Array of String)
Directory.GetFiles(yourFolderPath, “*.xls?”)
2: Insert ‘For each’ on arr_Files
All the remaining steps are within the For Each activity.
3: Add a Workbook “Read Range” using the path as ‘CurrentFile’ and Sheet name as “Sheet1”.
If the sheet name is dynamic then we need to use the Excel Application scope (additional steps required).
4: Save the Datatable Output from the Read Range as “dt_Temp”
5: Insert another assign with the following values:
Left assign:
dt_Temp
Right Assign:
dt_Temp.AsEnumerable().Where(function(row) Not String.IsNullOrEmpty(row(INSERTxCOLUMN).ToString())).CopyToDataTable
Note: Update the text INSERTxCOLUMN with an common column name like this: “CustomerNumber” or use you can insert just the column index 0,.
6: Insert a System “write range” using the path as ‘CurrentFile’ (red) and Sheet name (blue ) as “Cleaned” or whatever you want. Datatable (green) insert dt_Temp.
