When using a Data Connector import rule, the data to import includes a blank (null) value for an existing record's column value. For example, a computer's System Number field is desired to be blanked by setting its value to null via the data file. This may look like the following:
Name, System Number, Comment PC1,,This PC was changed on 9/1/2013
Where ",," is the blank System Number column value.
When the import rule runs, however, this fails to blank the specified column, instead skipping this and returning the error "x rows of data were skipped for data class '<column_name>'."
In the Connector Rule Rule Status window:
x rows of data were skipped for data class '<column_name>'.
In the Import Rule Log Viewer window:
Row x skipped for data class '<column_name>'. All columns contain null values.
The column contains a blank value, which Data Connector ignores.
This is working as designed. Data Connector is only designed to import data as valid characters. A blank or any number of only spaces, effectively making it null, is not a valid character(s) to import.
There are two ways to change this functionality:
If all values for all records for the specified column are all to be removed, this can be set in the "Data class mappings" section of the Import Rule:
a. In the import rule, in the "Data class mappings" section, find the data class mapping to change. b. Change its Source from the data column name to "<Null>". c. Run the import rule. This then removes all values for the specified column and sets them to null.
Or, if only some values for some records of the specified column are to be removed, a temporary "flag" value can be added that can then later be changed to null by using a SQL script:
a. In the data file used, change the columns that are null to a flag value, for example, "changethistonull". Note: Ensure that whatever the flag value is, that the data class itself can use this as a valid value, otherwise a different error will occur stating that this is an invalid value to set it to. b. Run the import rule normally. The specified columns for the specified rows then have their values set to the flag value. c. In SQL, the user will have to know what tables and data classes contain the flag value, and then, using a SQL script, change these to be null. If the user is unsure which tables and data classes these are, a search script, such as provided in the following article, can be used to help find these: