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
Solved! Go to 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
You could do the following:
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.
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 🙂
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
Thank you so much.
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?
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]%