Issues occur when using a Data Connector import rule that uses an data from an Excel spreadsheet
Article ID: 158084
Updated On:
Products
Issue/Introduction
When using a Data Connector import rule that uses data from Excel spreadsheet (as configured in the import rule's data source), odd issues or errors occur when trying to run the import rule.
Environment
ITMS 8.x
Cause
Invalid data in the Excel file and/or Altiris database (from prior invalid Data Connector imports).
Resolution
Sometimes using data from Excel spreadsheets may result in issues due to formatting. The best and easiest way to test this is to save the data to a .CSV file and try using this in Data Connector. If this works, then the Excel data or its formatting is likely the issue. The user can then decide if using the .CSV could be a solution or if it needs to be resolved in Excel before continuing the import. The following information describes how to troubleshoot this.
Issue: Data contains invalid or wrong characters.
Solution: Depending on what the invalid data is, different solutions are available:
- Check for trailing spaces. For example, a Serial Number column is used. In its cells, this looks fine. But when accessed, the serial number may look something like "123ABC " with extra trailing spaces. This is imported exactly as entered and the trailing spaces may then cause lookups issues or sync failures. This is particularly critical if the column is a key value such as serial system or barcode number as these must be unique. It's therefore possible to inadvertently cause "duplicates" with trailing spaces, as "123ABC " can be imported, and later, "123ABC " can be imported because it couldn't match with any existing serial number due to its difference in trailing spaces.
- Check for non-alphanumeric characters, such as copyright symbols, pi symbols, graphics, etc. Test removing these to see if these are causing issues.
Issue: Clean up the invalid or wrong characters in the database.
Solution: If invalid or wrong characters has been be imported before, depending on what was imported, it's possible that the database now has issues for the affected tables and columns. This needs to be cleaned up at the database level before new data imports can be safely ran next. This may have to be performed by SQL scripts or it may be able to be performed by directly editing the affected assets. The following SQL scripts can help troubleshoot this, which are designed to evaluate specific areas:
-- Find trailing spaces in serial numbers. This finds serial numbers that have been imported that have trailing spaces.
-- This SQL script can be modified for any table and data class by changing the table and data class names.
USE Symantec_CMDB
SELECT '"' + [Serial Number] + '"'
FROM Inv_Serial_Number
WHERE [Serial Number] LIKE '% '
-- Remove trailing spaces in serial numbers. This removes trailing spaces from serial numbers that have been imported that have trailing spaces.
-- This SQL script can be modified for any table and data class by changing the table and data class names.
-- WARNING: It is strongly recommended to back up the table to be modified or the database before using this as this will permanently change data!
USE Symantec_CMDB
UPDATE Inv_Serial_Number
SET [Serial Number] = LTRIM(RTRIM([Serial Number]))
-- Check for duplicate serial numbers. After removing trailing spaces, duplicates may then exist. These may then need to be resolved, especially if their data classes only allow unique values, such as serial numbers.
-- This SQL script can be modified for any table and data class by changing the table and data class names.
USE Symantec_CMDB
SELECT [Serial Number], COUNT(*) Total
FROM Inv_Serial_Number
GROUP BY [Serial Number]
ORDER BY 2 DESC, 1
Issue: Excel Cells that contain formulas or calculations.
Solution: If any cells that are used for data contain formulas or calculations, these may result in unexpected issues when attempting to access their data in Data Connector. In general, Data Connector is able to correctly read this formulated/calculated data. But in some cases it may not be able to. Test removing these to see if they are causing issues.
Additional Information
Feedback
