Answered

create a csv table with multi row header

(0) Share

Report

Posted on by sandracosta

Hello,

I need to create a csv table which has a specific format / template with a 3 row header, for each column (like shown above).

StartDate	Status	Call_SID	Interaction_ID
Start Date	Response Type	Call_SID	Interaction_ID
{"ImportId":"startDate","timeZone":"Europe/London"}	{"ImportId":"status"}	{"ImportId":"Call_SID"}	{"ImportId":"Interaction_ID"}
«values»	«values»	«values»	«values»

This is a specific format we use to import legacy data.

The first three rows are considered header for my platform. As the values are only expected in the 4th row of the file.

I have managed to create the csv all right with the right data in the value cells. All except for the header that I can't manage to have in 3 different rows / cells (all I could manage was to have all strings in the same cell).

Can you please advise on a way to achieve this?

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (1)

fchopo 8,003 Moderator on at

Like (0)

Report

Hello @sandracosta

Before creating the csv table, you could add 2 new rows at the top of the table, with header data in rows 2 and 3, using, for example, a compose action or appending to an array one (if you share your flow and the format of your data, we might provide more detailed information).

Hope it helps!

Ferran

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report

You could do something like this:
Explanation:
Create a new array that is the same format as the array that is going to be made into CSV.
Add your additional header records into that array.
Union that array with your existing data and then put the result into the "Create CSV Table" Step.

Was this reply helpful? Yes No

sandracosta

11 on at

Like (0)

Report

Hello @fchopo and @fchopo

Thanks so much for your fast replies.

I tried to go in the direction you appointed but I guess I am not as proficient in Power Automate as I should have been to solve this flow. My flow only got to this:

This flow stops in the “csv table” creation with the error “The property 'columns' must be specified unless the 'from' property value is an array of objects.”. However, in the last compose output I don’t seem able to identify the columns and hence can’t specify them.

The output from the union / input to the csv table comes out like this:

[

"StartDate",

"EndDate",

"Q1",

"Q2"

[

"Start Date",

"End Date",

"De 0 a 10 indique pf como recomendaria a Galp aos seus amigos e familiares, com base na sua experiência com este serviço de atendimento (em que 0 é não recomendaria e 10 é recomendaria de certeza)",

"De 1 a 5 indique pf o seu grau de satisfação com o atendimento que acabou de ter? (em que 1 é Muito Insatisfeito e 5 é Muito Satisfeito)"

[

"{\"ImportId\":\"startDate\",\"timeZone\":\"Europe/London\"}",

"{\"ImportId\":\"endDate\",\"timeZone\":\"Europe/London\"}",

"{\"ImportId\":\"QID1\"}",

"{\"ImportId\":\"QID2\"}"

{

"Start": "44161.4674189815",

"End": "44161.4674189815",

"Q1": "9",

"Q2": "5"

{

"Start": "44161.7289467593",

"End": "44161.7289467593",

"Q1": "9",

"Q2": "5"

{

"Start": "44161.4358796296",

"End": "44161.4358796296",

"Q1": "5",

"Q2": "2"

{

"Start": "44161.3903703704",

"End": "44161.3903703704",

"Q1": "9",

"Q2": "5"

…

A problem that I had was that I wasn’t able to initiate an array type variable. I kept getting the error “The variable 'ExtraHeader' of type 'Array' cannot be initialized or updated with value of type 'String'. The variable 'ExtraHeader' only supports values of types 'Array'.” with this script.

To solve this, I composed the extra header with createArray function. And “union” it to the select of the original file. But not sure if its the best practice.

Was this reply helpful? Yes No

Verified answer

fchopo 8,003 Moderator on at

Like (1)

Report

Hello @sandracosta

I did a test on my side that it is working:

In this example, we have an excel file with 2 columns (Region, Country). The steps are:

1) Define headers (3 rows)

2) List Excel Rows

3) Select the Region and Country columns

4) Create a CSV table

5) Replace "Region,Country" string by the headers.

Hope it helps!

Ferran

Was this reply helpful? Yes No