How to Import all Files in a Directory

We’ve recently been on a Property Management project using TM1 to deliver a Management, Planning and Reporting Solution. The Client has properties which are managed by an external property manager who produces tenancy schedules and general ledger reports for data consumption.

The Problem:

Every properties reports would come from the external property manager in a different file. Making the process to load these into TM1 only mildly faster than using Excel as a solution.

The Solution:

A small script was developed which lists the files from a given directory into a text file for consumption by a “Master” TI Process which then calls another “Child” TI Process for each file, giving the filename and path as a parameter each time.

The “Master” TI Process’ data source is the generated list of files and in the prolog it would run the script over the provided directory (via a parameter variable). This ensures the list of files is up to date.

The “Child” TI Process dynamically sets the datasource to the filename and path provided as a parameter. As the load is accumulative, the section of the cube (a property specific slice) being loaded needs to be cleared before each load to prevent data doubling up. This was further complicated by the fact that the property could not be determined by the name of the file. The clear out was achieved by reading the first line of the datasource file and retrieving the Property Identifier from it, thus performing the Zero Out in the Metadata Tab.

Download Here: makeListOfFoldersFiles.zip

Script Usage:

*PathToScript*\makeListOfFoldersFiles.vbs "*PathToFolder*" "*PathToOutputTextFile*"

Example:

C:\makeListOfFoldersFiles.vbs "C:\TM1s\PropertyModel\Data Files\Oct 2011\" "C:\TM1s\PropertyModel\Data Files\files.txt"

There is additional scripting required when then using this within a TI Process:

Executing the Script from a TI Process: (Prolog)

sExec = 'CScript  D:\blat\full\makeListOfFoldersFiles.vbs "' | pFolder | '" "C:\TM1s\PropertyModel\Data Files\files.txt"';
EXECUTECOMMAND(sExec,1);

This will execute the Script listing the files held within the folder path stored in the parameter variable “pFolder” and write this list to the specified path, which is also the datasource path for this process. Within the Data Tab of the process the “Child” process is called using the “EXECUTEPROCESS” function passing the filename as a parameter. Lastly the “DataSourceNameForServer” Local variable is used within the prolog of the “Child” Process to set the given filename as the datasource to be imported.

If you like this post, please spread the love…

2 comments on “How to Import all Files in a Directory

  1. George says:

    HI Ben,

    We have done this before but possibly there is a slight difference in requirement.
    Our clients would give us multiple data dumps that all need to be imported, just as your client did.
    What we did was created a batch file with a FOR loop to process a set of files in the specified directory.
    For each file we used the COPY /B + command to concatenate the files into one.
    The datasource was set to the destination file and did not require changes.

    This was a very simple solution to the problem at the time.

    Hope this gives others an alternative.

  2. JNG says:

    The problem “How to Import all Files in a Directory” can also be solved with the ExecuteCommand() function and the ‘DIR’ window command line. (The ‘Dir’ function lists the file of a folder).

    We can for example execute the folowing commnand:
    ‘Dir /B ‘| pFolder |’ > ‘ |TISourceFile

    pFolder as the Folder path for TI parameter
    TISourceFile as the full path for the source file of the TI process.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.