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:

Browser not supported