data reader file name substitution variable

We have data files to load that have been prefixed by the Business Unit and Month Year e.g. BU123_Jul.17_SalesDat.txt

We have corresponding Month and Business Unit entities in the database. These are available as pagers in a screen. I would like to utilise these to dynamically pass the user selections to the data reader (via a procedure) so that the appropriate file can be read. I know we can use the @USER substitution variable but not sure how to use other entities. I tried using @MONTH but was not successful.

Any suggestions would be appreciated.

It would be even better if we could check if the filename exists and return a message if it doesn't.

Thanks,

Dinesh

Find more posts tagged with

Accepted answers

All comments

unknown

Hi Dinesh,

I'd suggest you to to something like this.

Let the user choose BU and Month through a flag cube made by those entities;
Run a procedure (let's call it P1) such that you extract the flag cube with tabulator. Hence I'm expecting you obtain something like "month1code---->BU1code------>1" in the txt file.
suppose you have all your files to be read in ASCII within folder with path "path1", whereas the layout you extracted in point 2 is in the database default dataset (let it be dataset1).
After the extraction run the following .bat file.

FOR /F "usebackq tokens=1,2" %%G on (dataset1\layout1.txt) do (copy path1\%%H_%%G_SalesDat.txt path1\readBoard\readthis.txt).

Adjust %%H and %%G according to the order month and BU are in you file name.

See For - Loop through command output - Windows CMD - SS64.com for further details.

Place the .bat in the C:\Board\Job and run it after the extractions in procedure P1.
Set a wait of few seconds in the procedure.
Finally put in P1 the datareader ASCII that will always read the file path1\readBoard\readthis.txt.

P1 should look like this in the end.

Extract cube
wait 2 seconds
run .bat
wait 2 seconds
run datareader ASCII

In other words you are dynamically selecting the files you need, but Board is still apparently reading the same one with the same name in the same path. This way you override the need of substitution variables.

Hope it helps

Brendan Broughton

Hi Dinesh,

Are the BU and Month also I the txt file data (or can they easily be added)? If so I'd have a look at setting your ASCII data reader to "file/Path = ALL" to read every file initially... I'm not sure how many there would be and server specs so I'm not sure on how long this will take and if it's appropriate..assuming it is....

You could then either try using the 'Use Current Selection' check box on the ASCII reader (to only read the user selections) or read the files into a staging cube and then dataflow this to your target cube with the screen selections applied (to only populate the current user selections).

You could then use an 'if' action in the procedure to pop up a message if there is no data for the users selection to notify them no data has been read/file doesn't exist.

Cheers

Brendan

Johannes Blischke

Hi Dinesh Chand,

just a little hint: You are not able to use @Entity in exports because it is not supported. Only @User will work.

Cheers

Johannes

unknown

I'm happy to see the wealth of help from Giovanni Privato, Brendan Broughton and Johannes Blischke. You all make great points.

Following the same batch file approach, you could also use powershell, or any scripting language, with the same algorithm Giovanni Privato articulated.

Here is a similar document for traversing files using powershell.

Loop through files in a directory using PowerShell - Stack Overflow

Dinesh Chand

Thanks everyone for the great suggestions and options. I will let you know how I go. This is a cloud environment (Azure) so could make some of the scripting suggestions interesting but conceptually great.

Dinesh Chand

Thanks Brendan Broughton. This is the method I adopted for this cloud client.

Brendan Broughton

Great to know it came in useful Dinesh Chand