Batch Import with Dynamic Select

[JunePark]

Hi,

I'm trying to import multiple Files/Sheets.

Since they Have different Schemas,  I want the user to select the column numbers for specific fields.

I'm starting off the batch macro as below.

This works fine for most cases.

However, if some sheets does not have the specific column(for example, the vendor info column), I'm not sure how to handle this. (Of course, the macro results in errors if I input a 0 or non numeric value such as "N/A")

I can use a detour tool if I know how many columns can have N/A values, but I want to make the macro a bit more dynamic.

Does anyone have a solution for this??

Thanks.

[Jonathan-Sherman · Accepted solution]

Hi @JunePark,

I would create your (1,2,3,4,5,6,7) part of your Dynamic Select IN statement outside (using a summarise tool) and use a batch macro to pass the value into a dynamic select tool formula.

You may notice i'm filtering to remove 0s - this is to stop you bringing in any columns you don't want to or don't have (by using 0).

This concatenation creates the IN() list of field numbers to keep:

This list is passed into the batch macro to dynamically select the field numbers you wanted:

If this solves your issue please mark the answer as correct, if not let me know! I've attached a packaged workflow containing all of the macros and dependencies used.

If you've got any questions feel free to ask!

Regards,
Jonathan

[JunePark]

Hi @Jonathan-Sherman ,

This was so insightful, thank you.

I have one more question, and I'll try to describe elaborately as possible.

I've come across another problem that the dynamic select tool does not consider the order.

For example, I want to import 3 columns from each file : Date, Order Number, Order Amount.

File 1 : 1,4,5

Date

Customer Name

Customer Code

Order Number

Order Amount

File 2 : 1,6,4

Date

Customer Name

Customer Phone#

Order Amount

Customer Code

Order Number

If I set the columns which I prefer to input(1,4,5 and 1,6,4) as above,

since "in" function does not consider the order of the field numbers,

the 'Order Number' field and 'Order Amount' column will be aligned.

Do you have any solutions for this?

Thanks again, and hope you have a wonderful day.

[Jonathan-Sherman]

Hi @JunePark,

You could change the workflow slightly to bring each column in separately and join together after:

Example 1: Bringing in columns 1,2,3,4

Example 2: Bringing in Columns 6,5,1,3

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[JunePark]

@Jonathan-Sherman 

Thanks for your effort.

It must have took long to build an example, I really appreciate it.

Was great help!