Connector: How to pre-process import data

[Migration User]

I think it needs C# code here which I’m a bit lost with so any help would be appreciated…  The doco actually says that the pre-processing function is in C# you see…

 

I am importing user items from a SAP export (excel format) and I need to derive a FULL NAME column from the import.  The import has the columns “First Name” and “Last Name”.

 

Normally I’d take care of this via the import/export rule and do an expression (which does work using the expression [First Name] + ‘ ‘ + [Last Name]).  However, doing it this way means that I can’t make the ALTIRIS name of the record the derived full name.  So, I need to take care of this as a data source rule so that it is presented to the import/export rule as a Full Name from the source.

 

Looking at the data source (which is an excel spreadsheet), clicking on pre-process import data gives me some sample code but all I need to do as part of this pre-processing is to create a new column (let’s call it “Full Name” and it is simply derived of [First Name], a space, followed by [Last Name]).

 

Anyone keen to have a stab at this?

 

You may ask why I don’t do this via a CMDB rule.  Problem with CMDB rules is that the data needs to be in there first.  I was thinking about using task server to run the import first, followed by the CMDB rule immediately afterwards to change the display name to be first name + last name, but it’s not as elegant and might cause some issues in relation to subsequent imports, because the resource name lookup key is required for the import, so future imports won’t work basically…

[Migration User]

I've not done this much, but you should be able to use this article to find the information you're looking for.

https://www-secure.symantec.com/connect/articles/connector-solution-pre-processing-asset-65

I took a stab at it, but keep running into this weird issue where, even though I've merged the two columns "First_name" and "last_name" it still want's me to import those into the data class.  Here's the code I wrote to merge them together.  You could always create a custom data class and then just hide those columns.

If your columns don't have the Underscore you can of course pull them out.  I

DataTable filteredData = new DataTable();
foreach (DataColumn column in importData.Columns)
                filteredData.Columns.Add(column.ColumnName);
filteredData.Columns.Add("Full_Name");
DataRow[] users = importData.Select("[title] <> '1' ");
foreach (DataRow row in users)
{
                object[] currentRow = new object[row.ItemArray.Length + 1];
                row.ItemArray.CopyTo(currentRow, 0);
                currentRow[currentRow.Length - 1] = (string)row["first_name"] + " " + row["last_name"];
                filteredData.Rows.Add(currentRow);
 }

 return filteredData;

If you need the xml any of this to import for yourself let me know I can get it to you along wth the CSV file I tested with. 

[Mike.Langford]

So, if I understand right, you have data that contains First Name, Last Name, among various other fields.  You want to replace the first name and last name columns with a single column titled full name right?

[Mike.Langford]

Here is the code for what I think you are looking for.  Let me know if there is anything I get wrong.

The thing that MB left out is that when creating the filtered data table, you only want to include the columns you are planning on using, so in this case, in the foreach statement that builds the table, I am excluding the first and last name columns.

The next step that is a little different is when you are actually populating the filtered data table.  You can't use the ItemArray.CopyTo command any more because the item array that is based off your original data contains the first and last name columns which do not exist in your filtered data table.  To get around this, you would create an array as I have done below, and set the values of the specific columns instead of just a blanket copy statement that copies over all the columns.  For the example below, I took 4 columns (Title, First Name, Last Name, and Address) as an input, and I turned it into 3 columns (Title, Address, and Full Name) as an output.

//Code:

DataTable filteredData = new DataTable();
            foreach (DataColumn column in importData.Columns)
                if(column.ColumnName != "First Name" && column.ColumnName != "Last Name")
                    filteredData.Columns.Add(column.ColumnName);
      
            filteredData.Columns.Add("Full Name");
            DataRow[] users = importData.Select("[title] <> '1'");

            foreach (DataRow row in users)
            {
                object[] currentRow = new object[3];
                currentRow[0] = row["Title"];
                currentRow[1] = row["Address"];
                currentRow[2] = row["First Name"] + " " + row["Last Name"];
                filteredData.Rows.Add(currentRow);
            }
            return filteredData;
//End of code

 

Please let me know if anything is unclear.

[Migration User]

Aha! Thanks for pointing that out.

[Migration User]

Thanks so much for the help there guys.  Will try it out when I'm back on deck next week.

Just to clarify, the data source does not have a full name field, but I want the record to be named as the full name.  If I did the concatenation of the first+surname as an expression in the import/export rule, I can't have the name of the record as that, so it makes it hard for the user to do associations because it's not the real name of the person.  So by pre-processing it, I can set the import/export rule to name the record as the full name of the person.

Thanks again MBHarmon and Mike Langford.

[Migration User]

We needed to concatenate our PO number and Item number out of SAP which exported to an Excel file. Instead of beating our heads on the wall any longer with trying to get the data source to do it, we just talked to the SAP folks and they were able to add the concatenation to their query/export and create the new column on the fly in the Excel file. Problem solved! Might be worth a try with your names.... 

[Mike.Langford]

If you can get the data clean to start with, that is definitely the way to go.  I would use the preprocessing as a secondary option when there is no way to start with clean data.

Mike