Process Help

SPLIT - separate file into multiple files based on key values

 

Process Name

Menu Path

Link to Command Table

SPLIT

Data ribbon | Data Tools | Relational | Split

Click here

 

Introduction

Takes a single input file and copies the data within it to multiple output files.

How to use

A new file is generated for every unique value of a selected key field. An optional &FNAMES file can be used to define the output filename for each unique value in the *KEY field.

If the &FNAMES file is not defined then the output filenames will be created from the actual value of the KEY field.


Files, Fields and Parameters

Input Files

Name

Description

I/O Status

Required

Type

IN

Input data file. This file does not have to be sorted on the key field.

Input

Yes

Table

FNAMES

Input file defining the file names to be used for each unique value in the KEYVALUE field. Must be sorted by FILENAME and have the fields; FILENAME A - Output file name to be created. KEYVALUE A/N- Value of the key field which will be written to the file defined in FILENAME.

This field can be either alphanumeric or numeric but must be the same type as the KEY field.

A record in the IN file may be written to more than one output file by having multiple records with the same KEYVALUE and different FILENAMEs in the FNAMES file. Similarly, records with different key values can be written to the same file by having multiple records with different KEYVALUEs and the same FILENAME.

Input

No

Undefined

Output Files

Name

I/O Status

Required

Type

Description

Fields

Name

Description

Source

Required

Type

Default

KEY

Alphanumeric or numeric key field in the IN file used for selecting data.

IN

Yes

Any

Undefined

Parameters

Name

Description

Required

Default

Range

Values

NDP

Number of decimal places to be used for creating file names from a numeric KEY field (0).

Option

Description

0

do not display any digits after the decimal point. >0= replace the decimal point by the letter 'D' and display the specified number of digits following it. -1= create a file name using scientific notation.

No

0

-1,1

-1,0,1

MAXFILES

Maximum number of files which will be created (50). If this maximum is exceeded an error message is printed and the process is terminated. The maximum allowed value is 250 files.

Rules for creating file names from KEY field;

If KEY field is alphanumeric: - only the first 24 characters will be used.

- if a blank is encountered in the KEY value only those characters preceding the blank will be used.

- if a decimal point is encountered only the character immediately following the decimal point will be used.

If KEY field is numeric:

 - all file names are preceeded by one of two letters; P = plus M = minus

- file names that are too long due to a large number of decimal places will use only the first eight characters.

- all numbers with more than 7 digits preceeding the decimal point will be converted to scientific notation. ie. 1.23456E+09 becomes P1235P06 - trace (TR) are written to the file TRACE.

- maximum values (+) are written to PLUS.

- undefined values (-) are written to MINUS.

No

50

Undefined

Undefined


Notes

No additional notes.


Example

Example 1

Filenames created from a numeric *KEY field. The resultant file names, for different keyfield values are listed below for each of the 5 possible @NDP parameter values.

 

!SPLIT &IN(ASSAYS), *KEY(BHID)

-----------------------------------------------------------------------

VALUE IN
*KEY FIELD

@NDP=-1

@NDP=0

@NDP=1

@NDP=2

@NDP=5

-----------------------------------------------------------------------

-

MINUS

MINUS

MINUS

MINUS

MINUS

PLUS 

PLUS 

PLUS 

PLUS 

PLUS

TR

TRACE

TRACE

TRACE

TRACE

TRACE

 

 

 

 

 

 

0.0

P0000M04 

P0000000 

P00000D0 

P0000D00 

P0D00000

1.0 

P1000M03 

P0000001 

P00001D0 

P0001D00 

P1D00000

12.0 

P1200M02 

P0000012 

P00012D0 

P0012D00 

P1200M02

123.0 

P1230M01 

P0000123 

P00123D0 

P0123D00 

P1230M01

1234.0 

P1234P00 

P0001234 

P01234D0 

P1234D00 

P1234P00

12345.0 

P1235P01 

P0012345 

P12345D0 

P1235P01 

P1235P01

123456.0 

P1235P02 

P0123456 

P1235P02 

P1235P02 

P1235P02

1234567.0 

P1235P03 

P1234567 

P1235P03 

P1235P03 

P1235P03

1.2346E+07 

P1235P04 

P1235P04 

P1235P04 

P1235P04 

P1235P04

1.2346E+08 

P1235P05 

P1235P05 

P1235P05 

P1235P05 

P1235P05

1.2346E+09

P1235P06

P1235P06

P1235P06

P1235P06

P1235P06

-1.0 

M1000M03 

M0000001 

M00001D0 

M0001D00 

M1D00000

-12.0 

M1200M02 

M0000012 

M00012D0 

M0012D00 

M1200M02

-123.0 

M1230M01 

M0000123 

M00123D0 

M0123D00 

M1230M01

-1234.0 

M1234P00 

M0001234 

M01234D0 

M1234D00 

M1234P00

-12345.0 

M1235P01 

M0012345 

M12345D0 

M1235P01 

M1235P01

-123456.0 

M1235P02 

M0123456 

M1235P02 

M1235P02 

M1235P02

-1234567.0 

M1235P03 

M1234567 

M1235P03 

M1235P03 

M1235P03

-1.2346E+07 

M1235P04 

M1235P04 

M1235P04 

M1235P04 

M1235P04

-1.2346E+08 

M1235P05 

M1235P05 

M1235P05 

M1235P05 

M1235P05

-1.2346E+09

M1235P06

M1235P06

M1235P06

M1235P06

M1235P06

0.1

P1000M04

P0000000 

P00000D1

P0000D10

P0D10000

0.12

P1200M04

P0000000

P00000D1

P0000D12

P0D12000

0.123

P1230M04

P0000000

P00000D1

P0000D12

P0D12300

0.1234 

P1234M04

P0000000

P00000D1

P0000D12

P0D12340

0.12345 

P1235M04

P0000000

P00000D1

P0000D12

P0D12345

0.123456 

P1235M04

P0000000

P00000D1

P0000D12

P0D12345

0.1234567.0 

P1235M04

P0000000

P00000D1

P0000D12

P0D12345

1.2345E-25

P1235M28

P0000000

P00000D1

P0000D00

P0D00000

-1.2346E-25

M1235M28

M0000000

M00000D1

M0000D00

M0D00000

1.2346E+25

P1235P22

P1235P22

P1235P22

P1235P22

P1235P22

-1.2346E+25

M1235P22

M1235P22

M1235P22

M1235P22

M1235P22

 

Example 2

Writing multiple drillholes to a file:

!SPLIT &IN(ASSAYS), &FNAMES(FILENAME), *KEY(BHID)

&FNAMES(FILENAME)

FILENAME       KEYVALUE 

FILE01         BH0001 
FILE01         BH0002 
FILE01         BH0003 
FILE02         BH0004 
FILE02         BH0005 
FILE02         BH0007 
FILE03         BH0008 
FILE03         BH0009 
FILE03         BH0010 
FILE03         BH0011 


Produces the following three files:

FILE01     FILE02     FILE03

BHID       BHID       BHID 

BH0001     BH0004     BH0008 
BH0002     BH0005     BH0009 
BH0003     BH0007     BH0010 
                      BH0011 
 

Example 3

Writing identical drillholes to multiple files:

!SPLIT &IN(ASSAYS), &FNAMES(FILENAME), *KEY(BHID)

&FNAMES(FILENAME)

FILENAME       KEYVALUE 

FILE01         BH0001 
FILE01         BH0002 
FILE01         BH0003 
FILE02         BH0001 
FILE02         BH0002 
FILE02         BH0004 
FILE03         BH0001 
FILE03         BH0003 
FILE03         BH0004 
FILE03         BH0005 


Produces the following three files:

FILE01     FILE02     FILE03

BHID       BHID       BHID 

BH0001     BH0001     BH0001 
BH0002     BH0002     BH0003 
BH0003     BH0004     BH0004 
                BH0005


Error and Warning Messages

Message

Description

Solution

>>> ERROR: @MAXFILES outside of range 1 to 250 <<<

The number of output files have exceeded the defined @MAXFILES parameter value.

Increase the @MAXFILES parameter value (to a maximum of 250); select a different *KEY field; create a new keyfield in the input data file which will reduce the number of output files.

 

 

 

>>> ERROR: &IN file does not exist <<<

The &IN file does not exist

Check that the specified &IN file exists.

 

 

 

>>> ERROR: Missing KEY field in &IN() <<<

The specified *KEY field is incorrect or missing from &IN

Check that the specified *KEY field exists in the &IN file.

 

 

 

>>> WARNING: &FNAMES file does not exist <<<

The &FNAMES file does not exist.

Check that the specified &FNAMES file exists.

 

 

 

>>> ERROR: Missing KEYVALUE field in &FNAMES() <<<

The field KEYVALUE is missing from the &FNAMES file

Check that the field KEYVALUE exists in the &FNAMES file.

 

 

 

>>> ERROR: KEY and KEYVALUE fields incompatible <<<

The *KEY and KEYVALUE fields are not the same field type

Check that the specified *KEY field and the field KEYVALUE are either both numeric or alphanumeric.

 

 

 

>>> ERROR: Missing FILENAME field in &FNAMES() <<<

The FILENAME field is missing from the &FNAMES file

Check that the field FILENAME exists in the &FNAMES file.

 

 

 

>>> ERROR: FILENAME field in &FNAMES() is not alphanumeric <<<

FILENAME field in &FNAMES() is not alphanumeric

Check that the field FILENAME in the &FNAMES file is alphanumeric.

 

 

 

>>> ERROR: &FNAMES() not sorted <<<

The &FNAMES file is not sorted by the FILENAME field

Sort the &FNAMES file on the FILENAME field.

 

 

 

>>> ERROR: Too many output files >nn <<<

Too many output files

Increase the @MAXFILES parameter value (to a maximum of 250); select a different *KEY field; create a new keyfield in the input data file which will reduce the number of output files.

 

 

 

>>> ERROR: No valid output files - process aborted <<<

No valid output files - process aborted

Check all files and parameters.

 

 

 

>>> ERROR: Key fields don't match output files <<<

Key fields don't match output files

Check all files and parameters.

 

 

 

>>> ERROR: Cannot open file - process aborted <<<

The selected input data file cannot be opened. It may be being used by another process.

Complete the existing process and close the data file.