CSV Alchemy: Mastering the Magic of Rows and Columns

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’.