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 II
Resolver II

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

8 REPLIES 8
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 II
Resolver II

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

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

 

CleanShot 2022-07-30 at 20.25.28@2x.png

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

 

%Datatable[2:4]%

CleanShot 2022-07-30 at 20.29.10@2x.png

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Top Kudoed Authors
Users online (3,110)