Collation

Collation script helps to gather scattered data. It is designed to be used for merging tables containing related data. It works in a similar way as a standard import and export, however it requires 3 files to be created (all of them should have the same names – e.g. my_collation.script, my_collation.destination, my_collation.collation ):

  • *.script – it contains information about a source and destination for imported data.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    <?xml version="1.0"?>
    <Script xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <Actions>
        <Action xsi:type="Load">
          <Source>D:\my_collation.collation</Source>
          <Destination>my_collation</Destination>
        </Action>
      </Actions>
    </Script>
  • *.destination – this file contains information about database connection.
    1
    user id=USER;password=PASSWORD;data source=dbserver\sqlexpress;integrated security=False;pooling=true;enlist=false;initial catalog=statmap;cartridge=SqlServer;schema=dbo
  • *.collation – Collation file is consisted of Master Table and unlimited Detail Tables.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    <?xml version="1.0" encoding="utf-8"?>
    <Collation>
      <MasterTable>
        <ConnectionString>user id=USER;password=PASSWORD;data source=dbserver\sqlexpress;table name=table;initial catalog=statmap;cartridge=SqlServer;schema=dbo</ConnectionString>
        <ColumnNames>
          <string>Id</string>
          <string>Location</string>
        </ColumnNames>
      </MasterTable>
      <DetailTable>
        <ConnectionString>data source=\\SDrive\data\LLPG\Tables;table name=National_Coverage;cartridge=MapInfo Tab</ConnectionString>
        <DetailColumnName>Location</DetailColumnName>
        <MasterColumnName>Location</MasterColumnName>
        <ColumnNames>
          <string>Geometry</string>
        </ColumnNames>
      </DetailTable>
    </Collation>

MasterTable contains a ConnectionString and ColumnNames string array.

DetailTables have a ConnectionString, MasterColumnName, DetailColumnName and ColumnNames string arrays.

  • ConnectionString is a set of parameters required to connect to database or to local file,
  • ColumnNames is a list of column, which will be inserted in resultant ‘collated’ table,
  • MasterColumnName – is a unique column from MasterTable, which provides a link to DetailColumnName,
  • DetailColumnName – is a unique column from each DetailTable, which provides a link to MasterColumnName.

Simple example showing usage of the Collation Script presented above: