Skip to content

File Path and File Name Patterns#

Dynamic file path and file name patterns can be used when creating certain types of import and export jobs. If you are importing or exporting data to a file-based connection, you must specify a file type and name. If you set up your import/export job to update automatically on a specified schedule, then the new file overwrites the old one. This can be avoided by using dynamic file path and name patterns.

INFO

You can't use dynamic (variable) values, such as column names or data values, generated from Workbooks as patterns.

File Path Placeholders#

The following variables are replaced by dynamic values during import/ export:

Variable Dynamic Value
%day% Current day of month (01-31)
%hour% Current hour (00-23)
%id% Auto increment number °
%min% Current minute (00-59)
%month% Current month (01-12)
%sheet% Sheet name
%user% Current user
%wb% Workbook name
%year% Current year
%partition% Date partition(s) according to the configured filter, applicable to the path name only for partitioned export sheets

° An auto increment number only works if the import/ export job is split into more than one file. The first split is 1 and then continues to the total number of imported/ exported files.

Example#

/home/data/%user%/%year%/%month%/%day%/myFile.csv

is replaced with

/home/data/John/2009/02/08/myFile.csv

when e.g., an import job is executed on Feb. 8th, 2009 by the user John.

Custom File Path Placeholders#

You can create custom placeholders if additional variables are required for the file path.

Create custom path placeholder:

  1. Open the default.properties file.
  2. Add in the custom property and save the file.

    system.property.envname.<placeholder name>=<folder name>
    
  3. Open or restart Spectrum

  4. Create or open an import/export job.

  5. Select a filesystem based connection (Example: SFTP, HDFS, FTP, ...) and file type.

  6. Enter the file path using %env:<placeholder name>%as defined in the default.properties file.
  7. Finish completing the import/export job wizard.

    INFO: If using Smart Execution:

  8. Repeat steps 2-8 for the das-common.properties file.

Example#

Saving the following to the default.properties folder:

system.property.envname.PRODUCT=MyProduct

The file path:

/home/data/path/%env:PRODUCT%/myFile.csv

is replaced with:

/home/data/path/MyProduct/myFile.csv

Date Patterns#

Defining date patterns.

Letter Date or Time Component Examples
G Era designator AD
y Year 1996; 96
M Month in year July; Jul; 07
w Week in year 27
W Week in month 2
D Day in year 189
d Day in month 10
F Day of week in month 2
E Day in week Tuesday; Tue
a Am/pm marker PM
H Hour in day (0-23) 0
k Hour in day (1-24) 24
K Hour in am/pm (0-11) 0
h Hour in am/pm (1-12) 12
m Minute in hour 30
s Second in minute 55
S Millisecond 978
z;zzz Time zone PST; Pacific Standard Time
Z;ZZ;ZZZ Time zone offset/id -0800; -08:00; America/Los_Angeles

Examples#

Date and Time Pattern Result
"yyyy.MM.dd G 'at' HH:mm:ss z" 2001.07.04 AD at 12:08:56 PDT
"EEE, MMM d, ''yy" Wed, Jul 4, '01
"h:mm a" 12:08 PM
"hh 'o''clock' a, zzz" 12 o'clock PM, Pacific Daylight Time
"K:mm a, z" 0:08 PM, PDT
"yyyyy.MMMMM.dd GGG hh:mm aaa" 02001.July.04 AD 12:08 PM
"EEE, d MMM yyyy HH:mm:ss Z" Wed, 4 Jul 2001 12:08:56 -0700
"yyMMddHHmmssZ" 010704120856-0700
yyyy-MM-dd HH:mm:ss.SSSSSS 2012-05-16 11:16:55.532875

INFO: When no patterns are used in file names, existing files are replaced.

Other Tools Used to Write File Paths#

Other than writing the direct file path, using default pattern placeholders, building your own placeholders, or using date patterns, Spectrum has other tools useful to write file paths.

Tool Description Example
Wildcard (*) This can represent any number of characters (including zero, in other words, zero or more characters) You can also use a wild card to indicate that all files of one type within a specific folder should be used. /bin/data/account_data. /Users/testUser/data/*/.xml
Alternative globbing patterns Various command-line utilities. View a list located in the GNU/Linux Command-Line Tools Summary documentation. bin/data/dat[a-z].csv
Regular Expressions A sequence of symbols and characters expressing a string or pattern to be searched for within a longer piece of text. bin/data/%year%/%month%/%day%/\[]data.csv /%pattern%/[^]*