How to create DT by array List

Help Activities
, , ,
1

Hi to All,
I have a problem with dynamically creating tables from a larger “parent” table.

  • The job is to read data from a DT.
  • Detect the number of months tracked. (Month_Start_1B) [February (02), March (03), April (04)]
  • Create tables divided for each month.

Thanks to the help of others, with my current flow I can trace the list of months in the file.
I don’t know how to create the tables, with rows from each month.

Sequence.xaml (6,6 KB)
Input.xlsx (769,9 KB)

Can anyone help me with this? thanks a lot…

3

Datatable.xaml (9.9 KB)

4

Ho Eric… thanks for your tip…

I get something weird … I think.
Precise: (given that I don’t know if it can be done).

My goal would like to obtain as many tables for how many months traced, noting the months from the file.

My goal would like to obtain as many tables for how many months traced, detecting the various months, directly from the file.
Then, insert it into each month’s database.
INSERT TABLE for February (with February record list)
INSERT TABLE for March …
INSERT TABLE for April …

I don’t know if there is a way.

I was thinking of something similar after splitting into tables.

image
image495×563 14.1 KB

image
image464×558 13.6 KB

The only thing that comes to mind “easy”, is to create many filters for all the months of the year, check if there are rows in the month, if> 1, create the table.
I was hoping there was a more elegant way. Here it is.

But it is also true that I have never worked with a project of this type, with many lines to be reduced to smaller flows, so maybe there is another way … I don’t know. :expressionless:

5

In my flow, I have used “Month_Start_1B” to check the month and put it in the List
I have used For each List to split the different months into a different datatable and store them in a dictionary.
If you want to insert into database you can use List to loop and don’t have any checking
Datatable.xaml (11.6 KB)

6

image
What activitie is it?

7

Hello,

Unfortunately, I’m not able to attach files.

image
image450×770 14.9 KB

After initializing the connection and reading range activity, you can simply go for each row and insert each row into the table described in the 4th column.

Where table name = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(Cint(row(4)))
It will convert number to month name.

To convert row to datatable you can clone the structure of original datatable and add data row.

And after For Each Row activity just close the db connection.

Hope it’ll help you.

8

for each row

9

Hey,

Find Sequence attached below.
Sequence.xaml (7.4 KB)

If you have any questions, let me know.

10

Hi Yuriy… WoW!
This is very good!!

This is basically what I need.
If you can, I need some advice.
As I said, I have never worked on such large volumes. (over time, they will increase I think)
I wonder if it makes sense to work on a single table, and not on split tables for each month.
I explain:

  • Master table filter, month by month. (as you suggested).
  • Upload of data, one month at a time, on a fixed table. ex: Month_DT
    ex: I identify the February lines, load the lines on Month_DT >> start the dispositive operations on SAP, at the end, truncate Month_DT and load the March data … then again SAP dispositive operations … etc.

But I don’t know if it’s a wise choice.
What do you think?
Thanks again very much. Truly TOP solution!

11

You can create dictionary of data tables where keys are months’ names.
Use the same ‘System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(Cint(row(4)))’ for keys in dictionary.

And instead of inserting each row directly to the database, insert data row to data table (with add data row activity) in dictionary and after for each row activity iterate for each key in dictionary and insert datatable (as value of key-value pair) in database.

12

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