Data mappings consists of two parts; the header and definition:
Header
A correctly formatted header is essential to avoid issues running the Mapping Designer and the data mapping process during import or export.
The header indicates the required types and templates that will be used, plus optional extensions.
General
Source Type: mdf
Source Template:
Destination Type: xls,xlsx
Destination Template: C:\ProgramData\Geroc\DATA\Templates\External\EXCEL\GO!4\GO!4v2.xls
•Type - defines file extensions (comma separated list) that a mapping can be used with.
•Template - defines a file representing the external file structure.
AGS
Source Type: mdf
Source Template:
Destination Type: ags
Destination Engine: AGS4
Destination DICT: C:\ProgramData\Geroc\DATA\Templates\External\Schema\AGS4 NZ\DICT.xls
Destination ABBR: C:\ProgramData\Geroc\DATA\Templates\External\Schema\AGS4 NZ\abbr.ags
•Engine - indicates which AGS engine is used to read / write data.
•DICT - defines the file containing the structure.
•ABBR - defines the file containing codes and types.
Extensions
Row Number: true
Suffix: _mapping1
Procedures: usp_CPTMaxDepth,...
Corrections: CptFilterInterval(0.5, 0.88, 0.05),...
•Row Number - indicates if auto-incremented integer values are added to each row.
•Suffix - appends the file name with a text value ensuring data is not overwritten when exporting the same data with different mapping.
•Procedures - defines stored procedures (comma separated list) that are executed before export or after import.
•Corrections - defines internal methods (comma separated list) that are run during CPT data import.
Definition
The definition contains information defining the mapping between external files and the database.
The start of processing a new table is denoted by **[Table Name].
Lines beneath the table line define data mappings between the destination table and column (left of the = sign) and source expression (right of the = sign) enclosed in << >> brackets.
Simple definitions
The most simple type of mapping is where data is coming from a single table and each column is a one-to-one relationship:
**FILE
[FILE].[FILE_FSET] = <<[FILES].[VALUE]>>
[FILE].[FILE_NAME] = <<[FILES].[Name]>>
[FILE].[FILE_DESC] = <<[FILES].[Description]>>
[FILE].[FILE_TYPE] = <<[FILES].[Type]>>
[FILE].[FILE_PROG] = <<[FILES].[Application]>>
[FILE].[FILE_DOCT] = <<[FILES].[DocumentType]>>
[FILE].[FILE_DATE] = <<[FILES].[Date]>>
[FILE].[FILE_REM] = <<[FILES].[Remarks]>>
...
If data needs converting from one value to another, a CASE statement is used:
[GEOLOGY].[Description] AS 'GEOL_DESC',
CASE
WHEN [GEOLOGY].[Legend] = '!TOPSOIL' THEN '101'
WHEN [GEOLOGY].[Legend] = '!FILL' THEN '102'
WHEN [GEOLOGY].[Legend] = '!CONC' THEN '104'
WHEN [GEOLOGY].[Legend] = 'CL' THEN '201'
WHEN [GEOLOGY].[Legend] = 'CL sh' THEN '201'
WHEN [GEOLOGY].[Legend] = 'CL shd' THEN '201'
WHEN [GEOLOGY].[Legend] = 'CL si' THEN '202'
...
Complex definitions
In complex situations (i.e. to filter data or combining data from more than one table) data may be processed through full SQL statements, with the option to include parameters.
Parameters are defined by the @ character (i.e. @[SCDT].[LOCA_ID]...) with a corresponding replacement index (i.e. {0}) in the statement. These are processed just before the mapping is run to assess the number of times that the index replacement and statement is executed.
**SCDT
@[SCDT].[LOCA_ID] [System.String] = <<[CPT_Dissipation_Data].[POINT_ID]>>
<<SELECT
[CPT_Dissipation_Data].[POINT_ID] AS 'LOCA_ID',
[CPT_Dissipation_Data].[ITEM] AS 'SCPG_TESN',
[CPT_Dissipation_Data].[TOP] AS 'SCDG_DPTH',
[CPT_Dissipation_Data].[SECONDS] AS 'SCDT_SECS',
[CPT_Dissipation_Data].[qc] AS 'SCDT_RES',
[CPT_Dissipation_Data].[u1] AS 'SCDT_PWP1',
[CPT_Dissipation_Data].[u2] AS 'SCDT_PWP2',
[CPT_Dissipation_Data].[u3] AS 'SCDT_PWP3',
[CPT_Dissipation_Data].[Remarks] AS 'SCDT_REM',
[CPT_Dissipation_Data].[FileSet] AS 'FILE_FSET'
FROM [CPT_Dissipation_Data]
WHERE [CPT_Dissipation_Data].[POINT_ID] = {0}>>
|
Column names cannot contain the * character. If a file does contain this character, it's automatically replaced with x. |
