File Path and Parameter at the same time

1

Is it possible to use a parameter and file path at the same time for importing and exporting?
Similar to Power Query. For example, in the import file name field:

{FILE PATH PARAMETER} & "data\dims\dim_customer.csv"

This way, we won't need to make a distinct parameter for each import/export file for team member or device. I have tried this in EM, but haven't had any success.

2

Typically, you would use a calculated parameter - it's a parameter that is not entered by the user, but instead calculated based on other parameter(s) using an expression.

image
image984×618 54.9 KB

For example, the path of the output file can be calculated automatically from the input file path.

It's usually a good idea to use the combinepaths() function to concatenate two paths into one as it will take care of trailing backslashes.

{OUTPUT PATH} = combinepaths({FILE PATH PARAMETER}, "data\dims\dim_customer.csv")
3

Perfect thank you!

4

One more question. How do I pass the name of the current table in the exported file name?

Assume:

  1. {FILE PATH PARAMETER} = f:\main\transforms
  2. {CURRENT TABLE} = metadata('current table')
  3. Actual current table = 'dim_customer'

I tried 2 methods:

  1. {OUTPUT PATH} = combinepath({FILE PATH PARAMETER}, {CURRENT TABLE})
  2. {OUTPUT PATH} = {FILE PATH PARAMETER} & " \ " & {CURRENT TABLE})

Note: I need to add the preceding and trailing space for \ for some reason for it to show up in this response

  1. produced a file name in f:\main called "transforms.csv
  2. produced a file name in the correct directory (f:\main\transforms) but only called ".csv"

What am I missing? Do I need to add a column to each table and use "first value of column" option?

5

Answering my own question ... The new [EXPORT] column works. Is there a better way to do this?

6

That's not going to work because parameters are evaluated before workflow starts execution, and remain constant (immutable). Therefore, {CURRENT TABLE} is always empty, because there is no current table when parameters are evaluated.

The only way how you can catch the current table name is to create a column with the table's name (you can also use the "System information" action for that).

However, it still doesn't feel right to me. Table names are constant and don't change during workflow execution. So why have a parameter or column that captures the current table name? If it's because a certain chain of actions repeats in several places, then the idiomatic way is to put the repeating chain of actions into a separate module, and then call this (re-usable) module with the required parameters (such as table name).