How can you split a DataTable into its columns and without hard coding variables?

Help Activities
, , ,
1

Hi everyone, Luis here!

I was wondering if you can help me with this one, I have a problem that I have designed in the attachment:

  1. I have a DataTable (named dtInputDataTable, with 3 columns and 10 rows).

  2. Then, I used 3 Filter Data Table activities (one per column) and declared 3 DataTable variables.

  3. Finally, I used an Excel Application Scope activity, and used 3 Write Range activities, writing each column from the original Data Table.

Those are my problems:

  • How can I split the dtInputDataTable into their columns, without hard coding the Filter Data Table activity?

  • How can I “paste” each column in specific cells in Excel, without hard coding the Write Range activity?

I will appreciate your help because my original problem consists of a DataTable with 15 columns and more than 3000 rows.

Regards.

SplitDataTable.zip (11.8 KB)

2

heres a solution that will loop over each column and write them to every 2 columns in excel without any hardcoding
test.xaml (14.5 KB)

when you open the sequence, change this path back to your path “My files\Demo.xlsx”
image

Steps:

  1. loop over dtInputDataTableax.Columns
    Start loop:
  2. filter dtInputDataTableax to tempDt , outputing the current column only
  3. convert columnNumberToWrite to columnLetterToWrite (e.g if columnNumberToWrite = 1, columnLetterToWrite = A, if columnNumber = 3, columnLEtter = C)
  4. assign columnNumberToWrite = columnNumberToWrite +2 (if you want to write to every 3 columns instead of 2 just change 2 to 3
  5. write range

log
image

result

image
image466×629 7.42 KB

3

Tested!

Hi Jack, I find your explanation useful and your solution is elegant.

Thanks a lot, and have a nice night. :robot:

1 Like
4

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.