cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NeerajYadav
Microsoft
Microsoft

How to write data in csv in given format through Microsoft Power Automate Desktop

I have data in csv file. there are two column with some values at A and B location as shown in image 1, Now just i want to write the values of other rows at C location in csv file using Power Automate desktop. The output format is given in image 2.

So i request you please help in development.

 

Image 1 (Inputdata)

 

InputImage.png

Image2 (Required Output)

OutputImage.png

Thanks& Regards,

Neeraj Yadav

1 ACCEPTED SOLUTION

Accepted Solutions
ZS440
Resolver I
Resolver I

Hi, @NeerajYadav 

This is quite an interesting assignment so I thought about it.
I should use Power Query to process the data, but I will not use Excel, and use only PAD and CSV to solve the problem.
Datatable is created only by manipulating variables and written to CSV.
Therefore, pivot the Datatable twice and format it.

Screenshot 2021-03-22 014000.jpg
It can be used for a larger number of data, so give it a try.

Input.CSV is placed on the desktop.

 

 

Screenshot 2021-03-22 011508.jpg

System.GetEnvironmentVariable Name: $'''USERNAME''' Value=> EnvironmentVariableValue
File.ReadCSV CSVFile: $'''C:\\Users\\%EnvironmentVariableValue%\\Desktop\\Input.csv''' Encoding: File.CSVEncoding.UTF8 TrimFields: True FirstLineContainsColumnNames: False ColumnsSeparator: File.CSVColumnsSeparator.SystemDefault CSVTable=> CSVTable
Variables.RetrieveDataTableColumnIntoList DataTable: CSVTable ColumnNameOrIndex: 0 ColumnAsList=> ColumnAsList
SET KeyCount TO (ColumnAsList.Count - 1) / 8
SET Table1 TO {ColumnAsList[:8] }
Variables.RetrieveDataTableColumnIntoList DataTable: CSVTable ColumnNameOrIndex: 1 ColumnAsList=> ColumnAsList2
SET DataCount TO 0
LOOP LoopIndex FROM 8 TO ColumnAsList.Count STEP 9
    SET Data TO ColumnAsList2[DataCount:LoopIndex]
    Variables.IncreaseVariable Value: DataCount IncrementValue: 9 IncreasedValue=> DataCount
    SET Table1 TO Table1 + Data
END
Variables.RetrieveDataTableColumnIntoList DataTable: Table1 ColumnNameOrIndex: 0 ColumnAsList=> ColumnAsList3
SET Table2 TO {ColumnAsList3 }
LOOP LoopIndex2 FROM 1 TO Table1.Columns.Count - 1 STEP 1
    Variables.RetrieveDataTableColumnIntoList DataTable: Table1 ColumnNameOrIndex: LoopIndex2 ColumnAsList=> ColumnAsList4
    SET Table2 TO Table2 + ColumnAsList4
END
File.WriteCSV VariableToWrite: Table2 CSVFile: $'''C:\\Users\\%EnvironmentVariableValue%\\Desktop\\Output.csv''' CsvFileEncoding: File.CSVEncoding.UTF8 IncludeColumnNames: False IfFileExists: File.IfFileExists.Overwrite ColumnsSeparator: File.CSVColumnsSeparator.SystemDefault

View solution in original post

6 REPLIES 6
fraenK
Memorable Member
Memorable Member

You could do the following:

  1. Read CSV file as data table: https://docs.microsoft.com/en-us/power-automate/desktop-flows/actions-reference/file#readfromcsvfile
  2. Loop through the rows of the data table and set the value of the column fields in the new table to the value from current row number + x * 9: https://docs.microsoft.com/en-us/power-automate/desktop-flows/variable-data-types#advanced-data-type...
  3. Write data table back to CSV file: https://docs.microsoft.com/en-us/power-automate/desktop-flows/actions-reference/file#writetocsvfile
NeerajYadav
Microsoft
Microsoft

How to set the value of the column fields in the new table to the value from current row number.

Please see this documentation on how to initialize a data table and how to access it: https://docs.microsoft.com/en-us/power-automate/desktop-flows/variable-data-types#advanced-data-type...

 

You can use set variable with %VariableName[RowNumber][ColumnNumber]% to assign the new value. 

sakula1996
Responsive Resident
Responsive Resident

Hi @NeerajYadav 
Please check the screenshot for the logic:

You can also copy and paste the code in PAD development canvas. It should convert the code to GUI elements.

 

 

Excel.LaunchAndOpen Path: $'''C:\\Users\\SAkula\\Downloads\\Book1.xlsx''' Visible: True ReadOnly: False LoadAddInsAndMacros: False Instance=> ExcelInstance
Excel.Advanced.GetFirstFreeRowOnColumn Instance: ExcelInstance Column: $'''A''' FirstFreeRowOnColumn=> FirstFreeRowOnColumn
SET ColumnCounter TO 2
LOOP WHILE (ColumnCounter) < (FirstFreeRowOnColumn)
Excel.WriteCell Instance: ExcelInstance Value: ColumnCounter Column: 3 Row: ColumnCounter
Variables.IncreaseVariable Value: ColumnCounter IncrementValue: 1 IncreasedValue=> ColumnCounter
END

 

 

sakula1996_1-1616180557152.png

 

 

Hope this helps 🙂

 

ZS440
Resolver I
Resolver I

Hi, @NeerajYadav 

This is quite an interesting assignment so I thought about it.
I should use Power Query to process the data, but I will not use Excel, and use only PAD and CSV to solve the problem.
Datatable is created only by manipulating variables and written to CSV.
Therefore, pivot the Datatable twice and format it.

Screenshot 2021-03-22 014000.jpg
It can be used for a larger number of data, so give it a try.

Input.CSV is placed on the desktop.

 

 

Screenshot 2021-03-22 011508.jpg

System.GetEnvironmentVariable Name: $'''USERNAME''' Value=> EnvironmentVariableValue
File.ReadCSV CSVFile: $'''C:\\Users\\%EnvironmentVariableValue%\\Desktop\\Input.csv''' Encoding: File.CSVEncoding.UTF8 TrimFields: True FirstLineContainsColumnNames: False ColumnsSeparator: File.CSVColumnsSeparator.SystemDefault CSVTable=> CSVTable
Variables.RetrieveDataTableColumnIntoList DataTable: CSVTable ColumnNameOrIndex: 0 ColumnAsList=> ColumnAsList
SET KeyCount TO (ColumnAsList.Count - 1) / 8
SET Table1 TO {ColumnAsList[:8] }
Variables.RetrieveDataTableColumnIntoList DataTable: CSVTable ColumnNameOrIndex: 1 ColumnAsList=> ColumnAsList2
SET DataCount TO 0
LOOP LoopIndex FROM 8 TO ColumnAsList.Count STEP 9
    SET Data TO ColumnAsList2[DataCount:LoopIndex]
    Variables.IncreaseVariable Value: DataCount IncrementValue: 9 IncreasedValue=> DataCount
    SET Table1 TO Table1 + Data
END
Variables.RetrieveDataTableColumnIntoList DataTable: Table1 ColumnNameOrIndex: 0 ColumnAsList=> ColumnAsList3
SET Table2 TO {ColumnAsList3 }
LOOP LoopIndex2 FROM 1 TO Table1.Columns.Count - 1 STEP 1
    Variables.RetrieveDataTableColumnIntoList DataTable: Table1 ColumnNameOrIndex: LoopIndex2 ColumnAsList=> ColumnAsList4
    SET Table2 TO Table2 + ColumnAsList4
END
File.WriteCSV VariableToWrite: Table2 CSVFile: $'''C:\\Users\\%EnvironmentVariableValue%\\Desktop\\Output.csv''' CsvFileEncoding: File.CSVEncoding.UTF8 IncludeColumnNames: False IfFileExists: File.IfFileExists.Overwrite ColumnsSeparator: File.CSVColumnsSeparator.SystemDefault

View solution in original post

Thank you so much.

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Users online (47,912)