Transpose/Rotate a txt/csv File and Write it to a new txt/csv File

(0) Share

Report

Posted on by schaan

I'd like to read, transpose and write a txt/csv file. At the end, the file format doesn't change.

The input file might look like this:

Head0,Row1,Row2,Row3,Row4,Row5
Head1,A1,B1,C1,D1,E1
Head2,A2,B2,C2,D2,E2
Head3,a,b,c,d,e

... and the output file should then look like that:

Head0,Head1,Head2,Head3
Row1,A1,A2,a
Row2,B1,B2,b
Row3,C1,C2,c
Row4,D1,D2,d
Row5,E1,E2,e

For the first steps in the flow I've defined variables for

the whole file content "dateiinhaltx"
the delimiter character ","
the new line character for recognizing each new line of the file "newLine"
the array for splitting eachDataRow of the file
another array for later filling the new rows "set new row" (here I'm not sure how it should work)

Anyway, now I'm stucking in splitting each recognized data row with the given delimiter and writing the new file with the transposed data.

Can anybody help me with that?

Kind regards

The flow looks as following so far:

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (0)

Jcook 7,781 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Hello,

The Set variable inside your loop might be the issue.

You cannot use a Set Variable inside a loop unless you set the loops concurrency to 1.

This can be done by clicking the 3 dots on the loop shape, go to settings, turn on concurrency, and set to 1. This will in turn only run 1 item at a time. Which may impact performance.

Couple of workarounds.
1) have you tried using the ‘Append to Array’ shape instead of set?
2) use a compose shape, these are unaffected by concurrency in loops

Thanks

--Josh

If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others

Was this reply helpful? Yes No
schaan 7 on at

Like (0)

Report
Copy link

Link copied!

Hello Jcook

Thank you for your answer. I see the first problem that I've had before. Anyway, I'm pretty new in programming with power automate and I try to implement your given ideas. Therefore, I've still some issues.

First of all in the "apply to each" shape I'd like to split the array "eachDataRow" according the given delimiter. After that I'd like to fill/write the first splitted row in a array/variable (?) as the first column, then the second row as the second column and so further.

What do I have to do for that? May I use for this the compose shape as you suggested?
I'm often not sure about the syntax that I have to use 😉

Regards Thomas / Schaan
----

Was this reply helpful? Yes No
schaan 7 on at

Like (0)

Report
Copy link

Link copied!

I'm now pretty close to the solution I want. Have a look to the flow steps below:

As "Output1" I get: "Head0,Row1,Row2,Row3,Row4,Row5\r","Head1,A1,B1,C1,D1,E1\r","Head2,A2,B2,C2,D2,E2\r","Head3,a,b,c,d,e"
and as "Output2" for each apply step:
1st "Head0","Row1","Row2","Row3","Row4","Row5\r"
2nd "Head1","A1","B1","C1","D1","E1\r"
3rd "Head2","A2","B2","C2","D2","E2\r"
4th "Head3","a","b","c","d","e"

The last open steps I'm asking for are:
a) How can I remove the control caracters "\r" ?
b) How can I write each line above as columns in a new csv? (1. column = 1st line; 2. column = 2nd line; etc.)

Kind regards
Thomas / schaan

Was this reply helpful? Yes No
schaan 7 on at

Like (0)

Report
Copy link

Link copied!

Sorry guys

I give up at the moment. Nobody seems to have a solution vor transposing a table within a flow construct.

Since there aren't any possibilities for dynamic arrays there is in my opinion and know-how so far no chance for transposing a table. Therefore, I tried to build a two dimensonal array with the data I read row by row and after that each row (array item) I splitted by the predefined delimiter.

After all of these, I wanted to transform this two dimensonal array in the matter of transposing the input at the beginning.

Again, at the moment I don't think, that this is possible within a flow construction at all.

Cheers Schaan

Was this reply helpful? Yes No