Intro:
Welcome to the inaugural entry of “Bite-Sized Wizardry,” a series conjured for the citizen developers who are ready to weave their own digital spells. In today’s potion of knowledge, we delve into the mystical world of arrays – the foundational grimoires of data objects. Whether you’re a seasoned sorcerer of syntax or a novice in the arcane arts of code, mastering the manipulation of rows and columns is essential.
CSV data setup:
| DOCUMENT NUMBER | SOURCE DOCUMENT TYPE | REQUISITION NUMBER | INPUT DATE | TOTAL AMOUNT | DEPARTMENT NUMBER | DEPARTMENT NAME | COST CENTER | COST CENTER NAME | DOCUMENT STATUS CODE | Status code | VOUCHED AMOUNT | Vendor ID | VENDOR NAME 1 | VENDOR CITY | VENDOR STATE |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 21003077 | P | 4777 | 04/07/2021 | 143.90 | 7910 | FLEET | 7910 | CENTRAL GARAGE | 3 | Canceled | 143.90 | VN0041 | ALL STAR AUTOMOTIVE | DENHAM SPRINGS | LA |
| 21002561 | P | 4018 | 03/23/2021 | 429.27 | 7910 | FLEET | 7910 | CENTRAL GARAGE | 0 | Open | 429.27 | VN0357 | ITA TRUCK SALES & SERVICE LLC | LAFAYETTE | LA |
| 21005726 | P | 8350 | 07/06/2021 | 178.40 | 5070 | POLICE | 5070 | CRIMINAL INVESTIGATION | 2 | Rejected | 168.40 | VN0052 | AMAZON CAPITAL SERVICES INC | SEATTLE | WA |
Transforming data input CSV data into array is super useful and this blog we will be exploring how to drop or add rows or columns
Tip 1:
Pick a CSV file in power automate the data of the file would be captured as a base64 encoding. The first step is to unpack the base64 and convert to string which could be readable
base64tostring(triggerBody()['file']['contentBytes'])
Tip 2:
Most of the CSV file would have CRLF (Carriage Return/Line Feed) "\n" "\r\n" at the end of every record. Converting this to array would be leveraging split function. Here CLRF is a compose variable
split(outputs('CSVTable'),outputs('CLRF'))
This would convert each row in the CSV file as a string in an array
Tip 3:
Most of the CSV files do have header that highlights the data elements. For data manipulation we might have to drop the header row.
skip(outputs('CSVarray'),1)
Tip 4:
If the CSV file from the source system has a trailer record in the file which has give meta data of no of records we might have to drop them as well while processing. We could use combination of reverse and skip function. First we reverse the order of an array and then remove the first element from that reversed array
skip(reverse(outputs('CSVarray')),1)
Tip 5:
If I want to filter rows with a specific column value we would leverage the filter function
@and(
not(equals(item(), '')),
equals(split(item(),',')?[10],'Open')
)
This part splits the current item by commas using split(item(),','), then checks if the 11th element (index 10, as indexing starts at 0) is equal to the string ‘Open’.
Top comments (0)