Step to transform column data in CSV using logic app

Question

Step to transform column data in CSV using logic app

Srinivas Mehenderker Nath 86

I have a CSV file created in the logic app based on a table. I would like to transform the data on certain columns to pre-determined values in the same CSV.

Kindly advise how to implement this step in the logic app.

MayankBargali-MSFT 70,986 Reputation points Moderator

2023-01-24T06:19:12.4433333+00:00

Hi @Srinivas Mehenderker Nath

Following up to see if the answer below helps. Do let us know if you have any queries.

Please 'Accept as answer' and ‘Upvote’ if it helped so that it can help others in the community looking for help on similar topics.

1 answer

Your answer

MayankBargali-MSFT 70,986 Reputation points Moderator

2023-01-24T06:19:12.4433333+00:00

Hi @Srinivas Mehenderker Nath

Following up to see if the answer below helps. Do let us know if you have any queries.

Please 'Accept as answer' and ‘Upvote’ if it helped so that it can help others in the community looking for help on similar topics.

Answer 1

Deepthi Murali 21

Hi,

This can be done by Create CSV Table action. Create CSV Table action expects its input as JSON array. Please follow the below steps:

Covert input CSV to JSON Array e.g

   [
     {
       "Count": 1,
       "Product": "Apples"
     },
     {
       "Count": 2,
       "Product": "oranges"
     },
     {
       "Count": 5,
       "Product": "Pear"
     }
   ]

Add a Create CSV Table action and set From as the JSON Array and Columns : Custom.
For each row, in the expression editor, enter this expression that specifies the array property value that you want, and select OK. item()?['<array-property-name>'] e.g : item()?['Product']
if the column needs to be set to a predetermined value, specify that value ('Fruit' in the below example)
And the output will be as below Apples,1,Fruit oranges,2,Fruit Pear,5,Fruit

Srinivas Mehenderker Nath 86 Reputation points

2023-01-20T11:03:52.2766667+00:00

Thanks, for a specific column I would like to set various values conditionally.

For ex : If value is 1, it should be Text1, if value is 2, it should be Text2,
Does it allow multiple conditions as below?
item()?['Product']
Deepthi Murali 21 Reputation points

2023-01-20T14:02:01.93+00:00

Yes, This is allowed. We can handle that using an expression with if and equals as below :

if(equals(item()?['Product'],'Apples'),'AP',if(equals(item()?['Product'],'oranges'),'OR','OTHERS'))

And the output will be as follows:

Apples,1,Fruit,AP

oranges,2,Fruit,OR

Pear,5,Fruit,OTHERS
Srinivas Mehenderker Nath 86 Reputation points

2023-02-21T10:31:06.4+00:00

I have tried Custom columns, but I am unable to get the individual elements within the JSON result as above. Did I miss something?

There is resultset and QueryResults available, but I am unable to get the json field values..
Srinivas Mehenderker Nath 86 Reputation points

2023-02-21T12:29:53.3466667+00:00

Thanks DeeptI, I am looking for an expression as below where the column should be assigned a custom value as below :
if(equals(item['COL1'],'1'),'VAL1') or if(equals(item['COL2'],'2'),'VAL2')

It says expression is invalid, please advise how the above condition can be given in expression.
MayankBargali-MSFT 70,986 Reputation points Moderator

2023-02-22T04:40:13.5766667+00:00

@Srinivas Mehenderker Nath Depending on column name and type it would differ whether your condition would work or not. Deepthi answer covers the details based on the sample that was used at their end. It differs based on input and what value you are passing to your Create CSV table.

I wish to engage with you offline for a closer look and provide a quick and specialized assistance, please send an email with subject line “Attn:Mayank” to AzCommunity[at]Microsoft[dot]com referencing this thread along with your availability (in UTC) so I can schedule the call to assist you.
MayankBargali-MSFT 70,986 Reputation points Moderator

2023-02-23T08:40:25.92+00:00

@Srinivas Mehenderker Nath Following up to see if are still facing the issue and need any assistance. In case if you are still facing the issue then please refer to my previous comment so I can connect with you offline to review your workflow.

syed baber 0

Hi @MayankBargali-MSFT ,

I have the below JSON as input to Created CSV table step:

[

{

"Posting Date": "31/08/2024",

"Reference ID": "1",

"Transaction Type": "INVOICE",

"Transaction Name": "Sale",

"Invoice Status": "sent",

"Item Type": "Premium",

"Client AL ID": "AC000014882",

"Client Internal ID": "573",

"Client External ID": "",

"Client Last Name": "OS Shi",

"Client First Name": "Norithn",

"NDIS Number": "",

"Client Custom 1": "",

"Client Custom 2": "",

"Debtor Al Care ID": "AC000014882",

"Debtor Internal ID": "573",

"Debtor External ID": "",

"Debtor Last Name": "OShield",

"Debtor First Name": "Norithn",

"Debtor Address": "1030 Mountain Creek QLD",

"Debtor Phone": "0334999932",

"Debtor Type": "",

"Debtor Relationship": "Self",

"Debtor Contact Custom 1": "",

"Debtor Contact Custom 2": "",

"Date": "1/08/2024",

"Funding Program": "Private- FFS",

"Funder Custom 1": "",

"Funder Custom 2": "",

"Description": "Meals - Client Contribution",

"Service Custom 1": "",

"Service Custom 2": "",

"Invoice Label 1": "",

"Invoice Label 2": "",

"Visit Start Time": "",

"Visit End Time": "",

"Employee AlayaCare ID": "",

"Employee Internal ID": "",

"Employee External ID": "",

"Employee Last Name": "",

"Employee First Name": "",

"Rate": "5",

"Rate ID": "PRIVFFS_9",

"Quantity": "10",

"Bill Duration": "",

"Amount": "50",

"Tax Name": "NA",

"Tax Number": "NA",

"Tax Amount": "0",

"Total": "50",

"Cancellation": "",

"Cost Centre": "[ Cost Centre not found ]",

"GL Number": "4160",

"GL Receivables": "8120",

"Client Group": "SUN - Zone 3",

"Bill Code": "MEALS_PRIV_FFS",

"Bill Code Description": "Meal Expense",

"Visit ID": "990",

"Visit Tag": "",

"Unit\r": "Per rate\r"

},

I am trying to concatenate GL Number and Cost Centre columns separated by hyphen under one column i.e. LedgerAccount as shown below, but it is not working:

User's image

The output is showing as follows:

LedgerAccount,TransDate

item()?['GL Number'] - item()?['Cost Centre'] ,item()?['Posting Date']

The values are not getting picked up as you can see. Can you please let me know what I am missing here ?

Thanks,

Share via

Step to transform column data in CSV using logic app

1 answer

Your answer