Updating column Yes/No according to the record presence in an import file

In this article we will show how to ensure that records are marked based on the fact that they were present in an external file.

In our example we will have a CSV file that is produced by an external system. The file contains names of servers on which certain client component of this system is installed. We have to cope with the fact that the file is not well structured. Each row contains name of a single server. There can be spaces next to the server name and the file contains also empty rows.

There is column FQDN (Fully Qualified Domain Name) in our class Machine, according to which we will map the records from the class to the records in the file. If the given record of the class will be present in the file, the class record will be marked in the column SCOM agent as Yes. Records, that are not in the file, will be marked as No in the column SCOM agent.

Besides above stated columns FQDN and SCOM agent in the class Machine we will need to create an import, that will read the given file and perform data update in the column SCOM agent.

We will perform settings in the import according to the following printscreen.

The file does not contain a headline. Therefore, we will mark this in the import and we will have to state order of the columns in the file. Records are mapped according to the column FQDN. We are updating values in the column SCOM agent and setting up value Yes, if they are mapped. There is only update going on, no data insert or deletion.

We have to include folloving Script in the event After data upload into the database in the import.

var cl = OG.ClassDef.GetById( OGActualImport.ClassDefId);
var colScom = cl.Columns['scom_agent'];

// delete SCOM indication for all the records
var sql = OG.TextUtils.Format( 'update DataRow{0} set {1} = 0', cl.Id, colScom.DBColumnName);
OG.Sql.RunSql(sql);

//delete empty rows
var colFqdn = cl.Columns['fqdn'];
var sql = OG.TextUtils.Format( 'update {0} set iserror = 1 where col_{1}_s is null', OGActualImportDbTable, colFqdn.Id);
OG.Sql.RunSql(sql);

The script will perform following.  It deletes value in column SCOM agent for all the records after uploading the file content into a temporary table (and before update in the class itself). Further, it deletes empty rows in the temporary import table. After the script is executed, import continues and as a result all the records that were in the import file are correctly marked in the column SCOM agent.