Creating a Data Connector data source that accesses data in an Excel spreadsheet through OLEDB

Products

• IT Management Suite

Issue/Introduction

This article describes how to create and configure a Data Connector data source to access data in an Excel spreadsheet (an .XLS or .XLSX file) through OLEDB (Object Linking and Embedding, Database). 

Environment

ITMS 8.x

Resolution

A Data Connector data source is needed to access the user's data so that a Data Connector import rule can later import this into the Symantec Management Platform database. For more information on how to use Data Connector or how to use different data source types, refer to the following article:

Using Data Connector to import data into the Symantec Management Platform database
181160

1. In the Symantec Management Platform Console, click on the Settings button > All Settings.
2. Click on to expand the folders Notification Server > Connector > Data Sources.
3. Right click on the Data Sources folder > New > OLEDB Data Source.
4. Click on the New OLEDB Data Source name and change this to a new name. For example "Data source used for importing computers from an Excel spreadsheet".
5. Click on "Add description", if desired, to add an optional description.
6. Click in the "OLEDB data source type" drop-down list and select MS Excel (*.xls, *.xlsx).
7. In the "MS Excel file" field, select the Excel spreadsheet to use. The Excel spreadsheet must be on the Symantec Management Platform server or available to it via a UNC path.
   
   If the Excel spreadsheet is on the Symantec Management Platform server:
   
   a. Click on the "Import from file" browse ("...") button.
   b. Navigate to where the Excel spreadsheet is located at.
   c. Click on to select the Excel spreadsheet.
   d. Click on the Open button.
   
   If the Excel spreadsheet is available from a UNC path:
   
   a. Type the UNC path to where the Excel spreadsheet is located at and then add its file name. For example: \\ServerX\c$\user data\mydata.xls.
   
   Note: If after selecting an Excel spreadhsheet to use, the error "Failed to load list of tables/sheets. The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the locale machine." occurs, install Microsoft Access Database Engine 2010 Redistributable (64-bit) on the Symantec Management Platform server. For information on how to install this, refer to the following article:
   
   Error: "Failed to load list of tables/sheets. The 'Microsoft.ACE.OLDEDB.12.0' provider is not registered on the local machine." occurs when trying to use a Data Connector data source with an Excel spreadsheet. KB 154132
    
8. In the "Worksheet name" field, click on the Refresh button.
9. Click in "Worksheet name" drop-down list and select the worksheet to use.
10. Click on the "Allow import" checkbox to enable it.
11. Click on the "View import data" button to view the data from the Excel spreadsheet. If the data doesn't appear as expected, correct the Excel spreadsheet data until it does. Note: Viewing data from an Excel spreadsheet will also display empty columns. This is working as designed and works the same way as viewing the data in Excel itself.
12. If data is to be pre-processed using C# code:
    
    a. Click on the "Pre-process import data" checkbox to enable it.
    b. Click on the "Edit function" button.
    c. Type the C# code to use.
    d. Click on the "Save changes" button.
    e. Click on the "View import data" button again. If the data no longer appears as expected, correct the C# code until it does.
    
    Note: When setting up the data source, it is not recommended to immediately set up its pre-processing C# code as that may further complicate the configuration. This can be skipped and later added after the data source and import rule are configured and working. For information about how to use C# code for data source pre-processing, refer to the following article:
    
    Using C# code for Data Connector data source pre-processing
    181197
     
13. Click on the "Save changes" button. The data source should now look similar to the following example:
    
     
     
14. Next, create a Data Connector import rule that uses the data source created in these instructions. For more information on how to create a Data Connector import rule, refer to the following article:
    
    Create a Data Connector import rule that creates or updates resources
    181748

Additional Information

Issues occur when using a Data Connector import rule that uses an data from an Excel spreadsheet
158084