Skip to main content

Notifications

Convert CSV files to Excel workbooks

ManishSolanki Profile Picture Posted by ManishSolanki 15,075

Use Case:

To reduce efforts spend in writing code or expressions for complex logic, we can leverage AI custom prompt. In this sample, I have covered the steps to create custom AI prompt that converts csv data to JSON array. Using that custom AI prompt, we can convert CSV attachments in emails to JSON and save it in SharePoint library.

 

Scenario:

We will take an example of an incoming email with csv file as attachment. The flow will convert the attached csv file to an array of JSON objects.

 

Input csv file email attachment looks like:

Index,Customer Id,First Name,Last Name,Company,City
1,DD37Cf93aecA6Dc,Sheryl,Baxter,Rasmussen Group,East Leonard
2,1Ef7b82A4CAAD10,Preston,Lozano,Vega-Gentry,East, Jimmychester
3,6F94879bDAfE5a6,Roy,Berry,Murillo-Perry,Isabelborough
4,5Cef8BFA16c5e3c,Linda,Olsen,Dominguez, Mcmillan and Donovan,Bensonview
5,053d585Ab6b3159,Joanna,Bender,Martin, Lang and Andrade,West Priscilla
6,2d08FB17EE273F4,Aimee,Downs,Steele Group,Chavezborough
7,EA4d384DfDbBf77,Darren,Peck,Lester, Woodard and Mitchell,Lake Ana
8,0e04AFde9f225dE,Brett,Mullen,Sanford, Davenport and Giles,Kimport
9,C2dE4dEEc489ae0,Sheryl,Meyers,Browning-Simon,Robersonstad
10,8C2811a503C7c5a,Michelle,Gallagher,Beck-Hendrix,Elaineberg

 

Solution:

First, let's create a custom AI prompt that will be used for converting csv to JSON

 

AI Prompt:

Navigate to "AI hub->Prompt->Create text with GPT using a prompt". Enter the prompt name as 'Convert CSV to JSON':

ManishSolanki_0-1718530556291.png

Add an input parameter that stores the input csv data of an email attachment:

ManishSolanki_1-1718530648940.png

Choose the output as 'JSON':

ManishSolanki_2-1718530689518.png

Select 'GPT 4' in the model drop down:

ManishSolanki_3-1718530724900.png

Enter the below instructions in the prompt textbox:

Convert below CSV data to JSON object 'result' with 'header' object contains string of comma separated header values and 'rows' object with JSON array:

ManishSolanki_4-1718530797621.png

 

Next, click 'Insert' button to place the parameter at the end of the instruction:

ManishSolanki_5-1718530872126.png

 

Test AI prompt:

Enter the below data in 'csvcontent' parameter:

Game Number, Game Length
1, 30
2, 29
3, 31
4, 16
5, 24
6, 29

Click 'Test prompt' button to test the AI model.

ManishSolanki_6-1718531360536.png

Finally, click 'Save custom prompt' button to save the prompt.

 

Power Automate Flow

 

1. Next, create an automated cloud flow and choose the trigger as "When a new email arrives (V3)". Set 'Include attachment' to 'Yes'. Configure filters like email subject, sender, recipients etc. as per the need:

ManishSolanki_7-1718531559301.png

 

2. Add "Create text with GPT using a prompt" action and select 'Convert CSV to JSON' from the drop down. Dynamically pass the value of attachment content. When you select the content property, the flow will automatically add loop (For each) action:

ManishSolanki_8-1718531652402.png

 

2.1. Within loop block, add "Compose" action to get the response text from custom AI prompt output. Enter an expression to get the response text in JSON object. Expression needs to be added in the Function box:

ManishSolanki_9-1718531919750.png

json(body('Create_text_with_GPT_using_a_prompt')?['responsev2']?['predictionOutput']?['text'])

 

2.2 Now to create an excel file in SharePoint add "Create file" action. Select the Site URL & document library. Enter expression in both 'File Name' & 'Content' parameter:

ManishSolanki_12-1718532260905.png

concat(utcNow('yyyyMMdd_hhmmss'),'.xlxs')

Expression used for 'File Content' parameter:

string('')

ManishSolanki_13-1718532382297.png

 

2.3 To create table in the sheet, add 'Create table' action. Select Site URL & document library from the drop down. Pass the value of ID from the output of "Create file" action to 'File' parameter:

ManishSolanki_14-1718532566035.png

Enter the table name as plain text and 'Sheet1!A1:F1' in 'Table Range' parameter. Enter an expression for the column names. We will get single string of column names from the output of custom AI action using an expression. 

ManishSolanki_15-1718532790994.png

outputs('Compose')?['result/header']

 

2.4 Post creating table in the sheet, add 'Apply to each' action to iterate the JSON array returned by AI builder action. Enter an expression to get the JSON object in an array:

ManishSolanki_16-1718533068049.png

outputs('Compose')?['result/rows']

 

2.4.1 Inside "Apply to each" block, add "Add a row in a table" action to enter record in excel file. Select the Site URL & document library. Pass the ID from the output of "Create file" action in the 'File' parameter:

ManishSolanki_17-1718533267013.png

In 'Table', pass the value of name property from the output of "Create table" action:

ManishSolanki_0-1718533478749.png

Enter an expression for the 'Row' parameter to get the JSON object:

ManishSolanki_1-1718533575563.png

items('Apply_to_each')

 

The overall structure of the flow looks like:

ManishSolanki_2-1718533664356.png

 

 

Output:

Output excel file created in SharePoint library:

ManishSolanki_3-1718533819027.png

 

 

Conclusion:

Using custom AI prompt, we can transform csv data to JSON array and saves lot of efforts in writing code in the form of expressions. Custom AI prompt can also be used for solving custom complex problem by providing appropriate instructions.

 

Note: I wouldn't recommend using this sample for large csv file due to limitations of data returned by AI builder action.

 

 

 

 

Categories:

AI Builder Power Automate

Comments