Answered

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

(0) Share

Report

Posted on by NeerajYadav Microsoft Employee

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)

Image2 (Required Output)

Thanks& Regards,

Neeraj Yadav

Categories:

Power Automate Desktop

I have the same question (0)

All responses (8)

Answers (1)

Sort by

fraenK 2,125 on at

Like (0)

Report
Copy link

Link copied!
You could do the following:
Read CSV file as data table: https://docs.microsoft.com/en-us/power-automate/desktop-flows/actions-reference/file#readfromcsvfile
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-types
Write data table back to CSV file: https://docs.microsoft.com/en-us/power-automate/desktop-flows/actions-reference/file#writetocsvfile

Was this reply helpful? Yes No
NeerajYadav Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
fraenK 2,125 on at

Like (1)

Report
Copy link

Link copied!

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-types

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

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

Hope this helps 🙂

Was this reply helpful? Yes No
Verified answer

ZS440 262 on at

Like (4)

Report
Copy link

Link copied!
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.

It can be used for a larger number of data, so give it a try.
Input.CSV is placed on the desktop.

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

Was this reply helpful? Yes No
NeerajYadav Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Thank you so much.

Was this reply helpful? Yes No
ccMuffins 7 on at

Like (0)

Report
Copy link

Link copied!

Wow. I've never seen using colon when retrieving items of table or list before.
Can you tell me what exactly it does and how did you know it plz?

Was this reply helpful? Yes No
ZS440 262 on at

Like (1)

Report
Copy link

Link copied!

I am a user from Robin Language WinAutomation.
Variables of text, list and datatable types can be sliced using index.
However, it is difficult to explain this to new users as a formal feature because it is not currently described in Docs.
It is possible to slice variables by writing as follows
%Variable[start:stop]%.
May be omitted if first or last position is specified.
Text type
SET Text TO $''''abcde''''
%Text[:3]%. abc
%Text[2:4]%. cd
%Text[3:]%. de
Similar to the Get Subtext action.

List type

SET List TO [1, 2, 3, 4, 5]
%List[:3]% %[1,2,3]%
%List[2:4]% %[3,4]%
%List[3:]% %[4,5]%

Datatable types

Variables.CreateNewDatatable InputTable: { ^['Column1', 'Column2'], [$'''a''', 0], [$'''b''', 1], [$'''c''', 2], [$'''d''', 3] } DataTable=> DataTable

%Datatable[2:4]%

Was this reply helpful? Yes No