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

[MELuisCruz (Luis Cruz)]

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)

[jack.chan]

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”

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

result

[MELuisCruz (Luis Cruz)]

Tested!

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

Thanks a lot, and have a nice night.