Excel Source XML Example

This page details an example Excel Source connection

1. Overview

This page details an example Excel Source connection.

Use Case: You have historically maintained a record of all of your employees in an Excel spreadsheet. Knowing that this significantly hinders your data and data management capabilities, you want to sync your file into a Cinchy table. Once synced, you can manage your employee information through the Cinchy data browser, instead of through a data silo.

2. XML Example

This example XML uses the following values:

Value	Description	Example
Source	The source location of your file. This can be either Local, S3, or Azure Blob Storage. If you select Local, make sure that you have properly set your Parameter and Path. Once you run your job, you will be able to upload your Excel file.	Local
sheetName	The name of the sheet within the Excel file that you want to sync.	Sample
HeaderRowsToIgnore	The number of records from the top of the file to ignore before the data starts (includes column header).	1
FooterRowsToIgnore	The number of records from the bottom of the file to ignore.	0
Path	The path to the Excel source file to load. For Local Upload this should be set to reference the same value from the Parameters section in the Info tab, along with the '@' symbol.	@file
Column Name	The name(s) of your source column(s).	"First Name" "Last Name" "Email Address" "Title" "Company"
dataType	The data type of your source column.	"Text"

2. XML Example

This example shows an Excel source and a Cinchy Table target.

<?xml version="1.0" encoding="utf-16"?>
<BatchDataSyncConfig name="Excel Batch Sync Example" version="1.0.0" xmlns="http://www.cinchy.co">
    <Parameters>
        <Parameter name="file"/>
    </Parameters>
    <ExcelDataSource source="PATH" path="@file" sheetName="Sample" headerRowsToIgnore="1" footerRowsToIgnore="0">
        <Schema>
            <Column name="First Name" dataType="Text" trimWhitespace="true" isMandatory="false" validateData="false"/>
            <Column name="Last Name" dataType="Text" trimWhitespace="true" isMandatory="false" validateData="false"/>
            <Column name="Email Address" dataType="Text" trimWhitespace="true" isMandatory="false" validateData="false"/>
            <Column name="Title" dataType="Text" trimWhitespace="true" isMandatory="false" validateData="false"/>
            <Column name="Company" dataType="Text" trimWhitespace="true" isMandatory="false" validateData="false"/>
        </Schema>
    </ExcelDataSource>
    <CinchyTableTarget reconcileData="true" domain="sandbox" table="Excel Batch Sync Example" suppressDuplicateErrors="false" degreeOfParallelism="1">
        <ColumnMappings>
            <ColumnMapping sourceColumn="First Name" targetColumn="First Name"/>
            <ColumnMapping sourceColumn="Last Name" targetColumn="Last Name"/>
            <ColumnMapping sourceColumn="Email Address" targetColumn="Email"/>
            <ColumnMapping sourceColumn="Title" targetColumn="Title"/>
            <ColumnMapping sourceColumn="Company" targetColumn="Company"/>
        </ColumnMappings>
        <SyncKey readonly="false">
            <SyncKeyColumnReference name="First Name"/>
        </SyncKey>
        <NewRecordBehaviour type="INSERT"/>
        <DroppedRecordBehaviour type="DELETE"/>
        <ChangedRecordBehaviour type="UPDATE"/>
        <PostSyncScripts/>
    </CinchyTableTarget>
</BatchDataSyncConfig>