Wednesday, November 12, 2014

MaxL Quick Tip - Loading Multiple Files with Wildcards

Hey Everyone,
Call me "late to the party" if needed, but I recently came across a bit of functionality that I had never used before and felt the need to compile a bit of research on the functionality.  Introduced in Essbase v11.1.2.2, there was a new MaxL feature that allows you to use wildcard characters in flat file names when loading a database.

Below is an example of how to import multiple files, in parallel, using these wildcards:

import database SCOTTAPP.SCOTTDB data from data_file "D:\\Data\\ThisIsData*.txt" using SERVER rules_file 'LoadItUp' on error append to "D:\\ErrorFiles\\ThisIsData.err";

This code above will load any file in the D:\Data directory that the name starts with "ThisIsData".  

For example the following files would be loaded:
  • ThisIsData.txt
  • ThisIsData_1.txt
  • ThisIsData_2.txt
  • ThisIsData_ButNotGoodData.txt

The following example files wound NOT be loaded:
  • ThisIsGoodData.txt
  • ThisIsData.csv

Below is an excerpt from the v11.1.2.2 user guide in regards to using wildcards in this scenario.

To import from multiple files in parallel, use the wildcard characters * and/or ? in the IMP-FILE name so that all intended import files are matched.
  • * substitutes any number of characters, and can be used anywhere in the pattern. For example, day*.txt matches an entire set of import files ranging from day1.txt - day9.txt.
  • ? substitutes one occurrence of any character, and can be used anywhere in the pattern. For example, 0?-*-2011.txt matches data source files named by date, for the single-digit months (Jan to Sept).

Hope this helps!  If you have any questions, please post them below in the comments section and I will answer as quickly as I can.