DataImporter - XML Configuration File

SHOW ALL CONTENT

Table of contents

Import (DataImporter)

Overview

The configuration file is an XML format text file (ANSI code) that contains all the information on the data source to import. This article explains the format of the file as well as the tags to use and how to modify/correct this file.

References

XML File

XML Template

<?xml version="1.0" encoding="utf-8" ?>
<Sources>
  <Source Name="Source name">
        <ConnectionString>String containing all information to connect to the source</ConnectionString>
        <ViewName>[View Name]</ViewName>
        <Content>Content Type Name</Content>
        ...
        [All other elements based on the imported data type]
        ...
  </Source>
</Sources>

 

Information on basic tags
  • <Source Name="Source name">
    • ​Name of the import source. Acts as a reference if you want to reuse the same source later.
      This tag is not validated, but it is important to enter a value that has significance and that is different for different types of import (Workstations, Monitors, Users, etc.). 
      During regular imports, this is the value that the system will look at to compare the data to be overwritten.
    • This value cannot contain accents or the ampersand (&) character.
    • This value will be visible in Octpous via the "Import source name" column.
  • <ConnectionString>String containing all information to connect to the source</ConnectionString>
    • ​The Connection String includes all the specific information about an OLEDB provider and how to connect. You will find all available types at the following site: https://www.connectionstrings.com.
    • In addition, you will find examples of most popular connections in the following section.
  • <ViewName>[View Name]</ViewName>
    • ​The View Name indicates the name of the object which contains the data to import.
    • See next section for more information
  • <Content>Content Type Name</Content>
  • All other elements based on the imported data type

​

Connection string

This element is probably the most complex of the XML file. It indicates to DataImporter which driver to use and where is the data source containing the data to import

<ConnectionString>Provider=Nom du pilote OLEDB / ODBC / SQL;Data Source=Nom de la source;Extended Properties="Informations supplémentaires pour traiter les données";</ConnectionString>
<ViewName>[View Name]</ViewName>

 

Tag explanation

 

<ConnectionString>

  • Provider
    • Name of the driver to user to connect to the data source. Most commons are :
      • CSV File
        • Microsoft.Jet.OLEDB.4.0
      • Excel file / Access table or view
        • Microsoft.ACE.OLEDB.16.0
      • SQL Table, view or stored procedure
        • SQLNCLI
  • Data Source
    • Name of the datasource to be used by the specified driver. This is where are the data to import
      • CSV File
        • Directory name where is the CSV file
      • Excel Sheet, Access Table or View
        • Name of the Excel or Access file(xlsx or accdb)
      • SQL Table, view or stored procedure
        • Name of the SQL Server followed by the credentials to be used. More information on connectionstrings.com.
      •  
  • Extended Properties
    • Depends on the driver and data source used. Most commons are:
      • CSV File
        • text;HDR=Yes;FMT=Delimited
          HDR=Yes : Instructs the OLEDB driver that the first line contains headers
          FMT=Delimited : Instructs the OLEDB driver that the columns are comma separated
      • Excel file
        • Excel 12.0 Xml;HDR=Yes;IMEX=1
          HDR=Yes : ​Instructs the OLEDB driver that the first line contains headers
          IMEX=1 : Instructs the OLEDB driver that the first line contains headers to treat the content of the column as text
      • Other datasource (SQL or Access)
        • This parameter can be removed. It is unused

<ViewName>

  • Specify that name of the object, within the datasource, to be queried in order to obtain the data.
  • This name cannot contain accents, the dash (-) or the ampersand (&) character.
  • The value of this element depends on the data source:
    • CSV file
      • Specify the name of the CSV file. Hyphens are not accepted for .CSV file name.
      • ​Exemple : <ViewName>CIImportation.csv</ViewName>
    • Excel
      • This is the name of the sheet within the excel file. The name must be suffixed with the $ symbol and surrounded by square brackets.
      • ​Exemple : <ViewName>[Sheet1$]</ViewName>. 
    • ​SQL, Access or other database engine
      • Specify the name of the view or stored procedure
      • ​Exemple : <ViewName>VIEW_CI</ViewName>

 

NOTE: Only the text in black requires modification. The parts in blue must remain the same.

Connection string exemples

Excel sheet

Sheet named Workstation  within an XLSX file existing in C:\DataImporter\CI.xlsx

<ConnectionString>Provider=Microsoft.ACE.OLEDB.16.0;Data Source=C:\DataImporter\CI.xlsx;Extended Properties="Excel 12.0 Xml;HDR=Yes;IMEX=1";</ConnectionString>
<ViewName>[Workstation$]</ViewName>

Access view

View named Servers within an ACCDB file existing in C:\DataImporter\MyDatabase.accdb

<ConnectionString>Provider=Microsoft.ACE.OLEDB.16.0;Data Source=C:\DataImporter\MyDatabase.accdb;Persist Security Info=False;</ConnectionString>
<ViewName>[Servers]</ViewName>

SQL view

View named DI_Workstation existing in a SQL database named OctopusImport on the OCTOSQL01 server.

<ConnectionString>Provider=SQLNCLI;Server=OCTOSQL01;Database=OctopusImport;Trusted_Connection=yes;</ConnectionString>
<ViewName>[DI_WORKSTATION]</ViewName>

CSV file

CSV file existing in C:\DataImporter\CI.csv

<ConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DataImporter\;Extended Properties="text;HDR=Yes;FMT=Delimited";</ConnectionString>
<ViewName>CI.csv</ViewName>

Note : Dataimporter does not accept user.csv as a file name. Name should not contain hyphens ( - ).

Note : If your file contains accents, add characterset=65001; to the command line as follows: <ConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\IMPORTSClients\isq-poc\;Extended Properties="text;characterset=65001;HDR=Yes;FMT=Delimited";</ConnectionString>

ODBC view

View named MyView existing within a database which can be access via the DSN dsnName

<ConnectionString>Provider=MSDASQL;DSN=dsnName;UID=NomUsager;PWD=MotDePasse;</ConnectionString>
<ViewName>MyView</ViewName>

 

Tips and Tricks

  • If your data source is available in an ODBC format, you need to install the ODBC driver first and configure an appropriate DNS before using the OLEDB for ODBC (MSDASQL) supplier.
  • If you have issues identifying an error in your XML configuration file, you can use the validator from w3Schools to troubleshoot the issue XML Validator. For this, copy the content of your XML file in the Syntax-Check Your XML box and then press on Validate.
  • If you want to use a different delimiter instead of the comma in a CSV file, refer to Microsoft's article on using a Schema.ini File. The schema.ini file will have to be stored in the same folder as the CSV file, as pointed in your XML file.
X
Help us improve our articles








Help us improve our articles