Adding column headers after splitting text to columns

I am working on a data transformation task where I need to transpose data from rows to columns. How can I programmatically add column headers under each transposed dataset and name them sequentially as “Invoice 1”, “Invoice 2”, and so on?

Please refer to the example below:
This is how the initial data would look like

After transposing the data using text to column activity, it would look like this

Could you provide step-by-step instructions or a code snippet on how to add the appropriate column names under each dataset like this?

Thanks

@hellos,

Use write cell activity.

Thanks,
Ashok :slightly_smiling_face:

Hi

Inside For Each Row activity split the values using comma as delimiter and if u want to write in same excel use write cell activity. Those split value will be in Array so use For Each and then inside that use write cell.

how to make it iterate through and write a header for each of the number of unempty columns there are? {in the form invoice 1, invoice 2, …, invoice n for n as the number of unempty columns without a header}

thanks

Hi @Yoichi

Do you have any advice or perhaps some code examples that could help approach this effectively?

Looking forward to your reply

Thanks

HI,

How about the following sample?

Sample
Sample20240710-3.zip (9.9 KB)

Regards,

Hi,

Thank you for your response. Unfortunately, I’m unable to download the zip file due to restrictions on my end. Could you please assist me by providing the code for each of the assign activities?

Your help would be greatly appreciated.

Regards,

HI,

Hope the following will help you.

strInvoices = dt.Rows(0)("Invoices").ToString

count = strInvoices.Count(Function(c) c=","c)

arritems = strInvoices.Split(","c,StringSplitOptions.RemoveEmptyEntries).Select(Function(s) s.Trim).ToArray

dt.Rows(0)("Invoice "+(idx+1).ToString) = currentItem

Regards,