Adding column headers after splitting text to columns

Help StudioX
,
1

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

image
image993×129 24.5 KB

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

image
image979×136 24.6 KB

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

image
image982×142 33.7 KB

Thanks

2

@hellos,

Use write cell activity.

Thanks,
Ashok :slightly_smiling_face:

3

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.

4

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

5

Hi @Yoichi

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

Looking forward to your reply

Thanks

6

HI,

How about the following sample?

image
image782×1604 49.6 KB

Sample
Sample20240710-3.zip (9.9 KB)

Regards,

7

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,

8

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,

9

Hello, what does ‘c’ mean in split(':'c)?

10

@qa1246192877,

It’s syntax to tell the compiler that we are passing a single character to split that is ":". Whenever you have to split anything with single character you should be passing c

Thanks,
Ashok :slight_smile: