DataImporter - XML Configuration File

Table of contents

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

NOTE : As the XML model file used as an example in this section is for imports done with Excel 2007, 2010 (or more recent), it is important to refer to the Connection Examples section for all other types of imports or older Excel versions. 

 

Example file

<?xml version="1.0" encoding="utf-8" ?>
<Sources>
  <Source Name="ITEquipements">
  <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Import\CI.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";</ConnectionString>
        <ViewName>[CI Import$]</ViewName>
        <Content>CI</Content>
        <IdentificationMethod>CIByName</IdentificationMethod>
        <ManageCIRetirement>false</ManageCIRetirement>
        <MainContactIdentificationMethod>UserByWindowsUsername</MainContactIdentificationMethod>
  </Source>
</Sources>

Analysis of the Example File

  • <Source Name="value"> : Name of the import source. Acts as a reference if you want to reuse the same source later.
  • <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Import\CI.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";</ConnectionString> : Path and name of the file containing the data to import.
  • <ViewName>[CI Import$]</ViewName>
  • <Content>CI</Content>
  • <IdentificationMethod>CIByName</IdentificationMethod>
  • <ManageCIRetirement>false</ManageCIRetirement>
  • <MainContactIdentificationMethod>UserByWindowsUsername</MainContactIdentificationMethod>
     
NOTE: Only the text in black requires modification. The parts in blue must remain the same.

Information on the Tags

  • <Source Name="SERVER">
    • The Source name is a reference tag to indicate from where the import is being done and it can be used for automated updates on the CI relations. 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.
NOTE: The name cannot contain accents or the ampersand (&) character
  • <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Import\CI_TI(Equipement).xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";</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: www.connectionstrings.com. In addition, you will find examples of most popular connections in the following section.
  • <ViewName>[CI Import$]</ViewName> 
    • The View Name indicates the tab to import if you are using Excel, the table name or view name if you are importing from a database (Access, SQL, etc.).  

If your data source is:

  • Excel : you need to specify the tab name of the Excel file in between square brackets and a ”$“ at the end. 
    • Example : <ViewName>[Sheet1$]</ViewName>
  • SQL, Access or other database: you need to specify the table or view name.
    • Example : <ViewName>CI_Vue</ViewName>
  • CSV File :when the data source is a CSV file, do not indicate the file name in the path of the connection string section, but indicate it in the view name section. 
    • ​Exemple : <ViewName>CIImport.csv</ViewName>. 
 <?xml version="1.0" encoding="utf-8" ?>
<Sources>
     <Source Name="Serveurs" >
          <ConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Import\;Extended Properties="text;HDR=Yes;FMT=Delimited";</ConnectionString>
          <ViewName>ImportationCI.csv</ViewName>
          <Content>CI</Content>
     </Source>
</Sources>
NOTE: The name cannot contain accents or the dash (-) character.
  • <Content>CI</Content> 
    • The Content is the declaration of the type of data being imported. In other words, in which module the data will be imported (CI, Users, Suppliers, etc.). This value is different for each type of import and is specified in the articles for each import type.

Additional Tag Explanation

The XML file can contain additional tags based on the import type. These tags are not mandatory and when they are not specified, the default value is used. 

To find out the available tags based on the import type, it's better to refer directly to the page on the targeted import type. 

Typical Use of XML File

DataImporter can import multiple data sources. For example, an Excel spreadsheet can contain one tab per source. The example below represents an XML file containing 3 sources and 3 different types of connections. When it is run DataImporter will import the 3 sources, one after the other, even if they refer to 3 different tabs or 3 spreadsheets.

The configuration file can be saved under any name. It is also possible to use several different files, if you want to run DataImporter at different times depending on the different sources to import.

Example of a File with Multiple Sources

<?xml version="1.0" encoding="utf-8" ?>
<Sources>
     <Source Name="Computer" >
          <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Import\CI(Equipement).xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";</ConnectionString>         
          <ViewName>[CI_PC$]</ViewName>
          <Content>CI</Content>
     </Source>
     <Source Name="Screen" >
          <ConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Import\CI(Equipement).mdb</ConnectionString>
          <ViewName>[CI_Screen$]</ViewName>
          <Content>CI</Content>
     </Source>
     <Source Name="RelationPCScreen" >
         <ConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Import\;Extended Properties="text;HDR=Yes;FMT=Delimited";</ConnectionString>
         <ViewName>CI_Relationship.csv</ViewName>
         <Content>CIRelation</Content>
     </Source>
</Sources>

 

Connection Examples

Here are the most used connections. Depending on your data source, you need to modify the <ConnectionString> section of your XML file. 
You can refer to the following site for other ODBC data sources: www.connectionstrings.com

  • Excel97 - 2003 :
    • <ConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Import\MonFichier.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";</ConnectionString> 
  • Excel 2007 - 2010 :
    • <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Import\MonFichier.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";</ConnectionString>
  • Access 2003 :
    • <ConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Import\MaBaseDeDonnees.mdb</ConnectionString>
  • Access 2007 :
    • <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Import\MaBaseDeDonnees.accdb;Persist Security Info=False;</ConnectionString>
  • CSV File  :
    • <ConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Import\;Extended Properties="text;HDR=Yes;FMT=Delimited";</ConnectionString>
    • <ViewName>MonFichier.csv</ViewName>
  • ODBC Data Source :
    • <ConnectionString>"Provider=MSDASQL;DSN=dsnName;UID=NomUsager;PWD=MotDePasse;";</ConnectionString>
    • <ViewName>MaVue</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.
X
Help us improve our articles








Help us improve our articles