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

[NeerajYadav]

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

[fraenK]

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]

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

[fraenK]

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]

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 🙂

[ZS440 · Accepted solution]

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

[NeerajYadav]

Thank you so much.

[ccMuffins]

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?

[ZS440]

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]%