PowerShell Cmdlets for Auto Dealerships

I am getting more and more calls for help with CDK Global, DealerTrack, and InfinitiNet data extraction and I want to post now about how I can help best:

  • I will write you a PowerShell cmdlet that will exactly meet your needs

I find the Microsoft PowerShell platform the best for folks that are not technically adept to extract data when, where and as often as they want. It helps to know how to run a PowerShell cmdlet but that is not critical.

 

3rd Party Data Extraction with Reynolds and Reynolds and ERAccess

I had a call from a 3rd Party vendor to the automobile dealership industry who was pulling data from their client’s Reynolds DMS’ and all of a sudden couldn’t do it anymore. Not wanting to become colossal pains to their clients they asked me to help. These are nice folks not pulling a high volume of data but important data none-the-less. Definitely not candidates for RCI. I had to do a little research to get myself up to speed as I hadn’t done much R&R work lately. See my last post about a previous conversation with the RCI team.

Installing the Reynolds Software Manager and ERAccess and is the key. To do that someone needs to help you. Otherwise you will probably be remoting into an ERAccess PC on the customer’s network and working there. Once we have access unfortunately we have to use the R&R proprietary Query (7601) or Report Generator programs to pull the data just like before. I’ve yet to find a 3rd Party extraction tool for UniVerse although it probably wouldn’t be hard to wire up PowerShell using the UniOLEDB Data Provider. The problem is someone with access to the Unix root would need to get to the UniVerse Server Admin and setup an account to show files. That’s not going to happen unless someone with an R&R system wants to experiment with me.

Since ADP and R&R use the same database (Currently Rocket UniVerse, formerly IBM UniVerse, originally PICK) their “Certified” interface would be a .Net data provider (hopefully) or XML service. I have a data provider for ADP but it crashes on Win7 systems. At any rate, we were able to solve the vendor’s problems going forward and get them back in the game. The goal now is to streamline this process with Rocket Software’s assistance and in this regard I will keep you posted.

XML and PowerShell Links

Links to helpful articles and Blog posts on XML and PowerShell.

During my search for information on XML and PowerShell I came across these links that I used many times. My desire to blog about XML and PowerShell was motivated by the fact that I had a hard time finding answers to my questions.

https://www.pluralsight.com/community/blogs/dan/archive/2006/10/28/41337.aspx
http://www.pluralsight-training.net/community/blogs/dan/archive/2006/11/28/43561.aspx
http://www.w3schools.com/xpath/
http://thepowershellguy.com/blogs/posh/archive/2007/12/30/processing-xml-with-powershell.aspx
http://powershell.com/cs/blogs/ebook/archive/2009/03/30/chapter-14-xml.aspx
http://www.kirupa.com/net/readingXML_pg1.htm
http://powershell.com/cs/blogs/tobias/archive/2009/01/17/xml-part-1-playing-with-rss-feeds-and-xml-content.aspx
http://powershell.com/cs/blogs/tobias/archive/2009/02/02/xml-part-2-write-add-and-change-xml-data.aspx
http://msdn.microsoft.com/en-us/library/ms256060(VS.80).aspx

If there are more let me know.

Using PowerShell to Request, Filter and Manipulate an [XML] SOAP Response

PowerShell’s Select-Xml commandlet is used to filter a SOAP Response.

I’m back after a few weeks of car business busyness. I think it is important to post some alternatives to the initial script I wrote to pull data off the ADP DAP Server. When I began this project I didn’t do a lot of research and I thought the Select-XML commandlet was some 3rd party script and not part of PowerShell 2.0. I wanted my script to be usable without any 3rd party snap-ins or functions so I used the XPathDocument method called Navigator instead of Select-XML. Last time I presented my discoveries as it related to using those .net objects.

This time I will show you the script I wrote using Select-XML which is a little cleaner. There were some modifications of the XML output because of differences in the way the commandlet works compared to using the XPathDocument object.

The script has the same XPath query options and for now, I just uncomment the one I want to use and comment out the rest. I have added some Excel formating statements to help make the output more readable.

#With this Script we pull the Employee Object into Xml and we have three places we can set conditions:
#   1) In the $soapMessage Selection Statement 
#   2) In the $query XPath expression statement - uncomment the one you want or make a new one
#   3) In the Pipeline (line 37 or 39) where we select the Employee node and restrict by DateAdded (Need to do this until I figure out how to convert strings to dates in XPath Expression)
$Url = http://xxx.yyy.zz.aa:pppp 
$webclient = new-object System.Net.WebClient 
$soapMessage = @" 
<?xml version="1.0" encoding="UTF-8"?> 
<eoapi:Request xmlns:eoapi=http://www.adp.com/xxxxxx version="1.0" ShowUnknownElementErrors="Yes"> 
   <Session> 
   <Connection> 
      <Product>Product</Product> 
      <Server>Server</Server> 
      <Password>password</Password> 
      <Pooling>No</Pooling> 
   </Connection> 
   <GetSet TimeoutSeconds="1800"> 
    <Qbject>Employee</Qbject> 
    <Group>DealershipCode</Group> 
    <Selection></Selection> 
    <MultiFlag>E</MultiFlag> 
   </GetSet> 
  </Session> 
</eoapi:Request> 
"@ 
# Upload the Soap payload ($SoapMessage) to the URL:PORT of the ADP XML Engine ($Url)
$result = ([XML]$webclient.UploadString($Url, $soapMessage))
#Write a XPath queries - Uncomment the one you want to use, comment the one's you don't
#$query = "/*/Employee" # This query returns all Employees in the object
$query = "/*/Employee[CustOrCompanyCode='PR']" # This query returns all Employees in the object that show as Persons as opposed to 'BS' for businesses
#$query = "/*/Employee[FirstName='DAVID']" # This query returns all Employees whose first name is 'DAVID' (case matters)
#$query = "/*/Employee[[dateTime]DateAdded > [dateTime]'2010-04-01']" # DOES NOT work....still Working On This One
$qryResult = Select-XML -content ($result.reply.session.get_InnerXML() | out-string | %{$_.substring($_.IndexOf('<Reply group='))}) -xpath $query | %{"<Employee>"+$_.node.InnerXML+"</Employee>"}
$qryResultXML = [xml]("<Reply>"+$qryResult+"</Reply>")
#Uncomment the following and comment out the next statement if you have pipeline conditions
#$qryResultXML.Reply.Employee | ?{[dateTime]$_.DateAdded -gt [dateTime]"2010-04-01"} | Select HostItemID, CustNo, NameCode, AccountingAccount, DateAdded, LastUpdate, CustOrCompanyCode, FirstName, MiddleName, LastName, Name1, SSN, CreditLimit, DealerField1, DealerField2, DealerField3, DealerField4, DealerField5, DealerField6, DealerField7, DealerField8, DealerField9, DealerField10 | Export-CSV "C:\Users\John Donnelly\Desktop\Employee.csv" -NoTypeInformation
#Uncomment the following and comment out the preceding if you have no pipeline conditions
$qryResultXML.Reply.Employee | Select HostItemID, CustNo, NameCode, AccountingAccount, DateAdded, LastUpdate, CustOrCompanyCode, FirstName, MiddleName, LastName, Name1, SSN, CreditLimit, DealerField1, DealerField2, DealerField3, DealerField4, DealerField5, DealerField6, DealerField7, DealerField8, DealerField9, DealerField10 | Export-CSV "C:\Users\John Donnelly\Desktop\Employee.csv" -NoTypeInformation
#Open Excel
$excel = new-object -comobject Excel.Application
#Make Excel visible
$excel.visible = $true
#Open the CSV just created
$workbook = $excel.Workbooks.open("C:\Users\John Donnelly\Desktop\Employee.csv")
#Get the first sheet by name
$sheet1 = $workbook.worksheets | where {$_.name -eq "Employee"}
#Make first row bold
$range = $sheet1.range("A1:w1")
$range.font.bold = "true"
#Put a border around it
[void]$range.borderAround(1,3)
#Format the various field columns
$sheet1.columns.item('m').NumberFormat = "#,##0.00_);(#,##0.00)" # for numbers
$sheet1.columns.item('l').NumberFormat = "000-00-0000" # for telephone numbers
$sheet1.columns.item('e').NumberFormat = "mm/dd/yy;@" # for dates
$sheet1.columns.item('f').NumberFormat = "mm/dd/yy;@"
$sheet1.columns.item('b').NumberFormat = "@" # for general (makes all entries nicely left justified even if a number)
#Select the Range in use
$WorkBookNew = $sheet1.UsedRange
#Autofit all the columns
[void]$WorkBookNew.EntireColumn.AutoFit()

Here’s my thought process building this rather inelegant solution:

The SOAP request, response variabe and XPath query lines are the same as last post. The major difference is that I am now using the Select-Xml commandlet to filter the SOAP response. Granted this isn’t the most efficient way to select the data. If PICK’s ENGLISH database query language was half as robust as XPath’s I would skip the Select-Xml statement all together and filter using the SELECTION tag of the request.

The power and new part of the script is this monster line that loads the $qryResult variable. We use the Select-Xml commandlet with the -content and -xpath parameters in use. The -content parameter requires a valid xml object so we use the get_InnerXML() method of the XMLDocument object $result.reply.session and strip off the extra “Reply” tag as in the previous post. The -xpath parameter holds a valid XPath query string and I list several examples. The one that is uncommented selects only those Employee nodes where CustOrCompanyCode=’PR’ meaning the employee is a person as opposed to a business (‘BS’). Go figure.

There’s more to this line but here’s the line to look at before we go on:

$qryResult = Select-XML -content ($result.reply.session.get_InnerXML() | out-string | %{$_.substring($_.IndexOf('<Reply group='))}) -xpath $query | %{"<Employee>"+$_.node.InnerXML+"</Employee>"}

Next the Select-Xml returns an XMLDocument object that is piped into a Foreach and wraps each set of child elements in its own Employee tag. We need this or we would just have the children in a string not grouped by Employee. You can think of it as wrapping each set of children of the node queried in an Employee tag.

Now we need a Root so we wrap $qryResult in a Reply tag and type it [xml]. Again if it were not well-formed the script would error out here.

$qryResultXML = [xml]("<Reply>"+$qryResult+"</Reply>")

Next we use dot notation and access the Employee Nodes of the XMLDocument object:

$qryResultXML.Reply.Employee

and pipe it into a Select-Object commandlet to grab the child items we need. I selected all in the script so I can delete each time what I don’t need. The resulting objects are then piped into the Export-Csv commandlet and saved locally.

Here’s the whole line:

$qryResultXML.Reply.Employee | Select HostItemID, CustNo, NameCode, AccountingAccount, DateAdded, LastUpdate, CustOrCompanyCode, FirstName, MiddleName, LastName, Name1, SSN, CreditLimit, DealerField1, DealerField2, DealerField3, DealerField4, DealerField5, DealerField6, DealerField7, DealerField8, DealerField9, DealerField10 | Export-CSV "C:\Users\John Donnelly\Desktop\Employee.csv" -NoTypeInformation

I then instantiate an Excel Com Object and open th csv file just created. I then activate the proper sheet and start formatting the first row and the columns so dates, phone numbers, dollar figures and other columns present well.

Next I autofit all columns used so I can see all the contents of the cells.

That’s it for now. Any comments or suggestions will be appreciated.

Accessing ADP’s XML Engine with PowerShell

Creating my first script to explore the various Data Objects in their entirety was a success I felt and a great experience exploring PowerShell’s XML capability. The experience also taught me quite a bit about .Net’s XML capabilities. I reviewed a few objects, by no means all of them because it gets real dry even for a data hound like me. I reviewed the ServiceSalesClosed, ServiceSalesOpen, ServiceSalesTechHoursOpen, ServiceSalesMLSOpen, ServiceSalesPartsOpen, ServiceSalesDetailsOpen, ServiceSalesDetailsClosed, GL Detail, Customer, Vehicle, and Employee.

During this process one day I had the need to pull some data from the General Ledger Detail. It should have been easy using my script but I ran into problems because I needed to filter the results. In other words I had to use the “Selection” tag in the XML Request. The DAP documentation states that the Selection tag accepts “ENGLISH” statements. ENGLISH seems to be the query language of the PICK DBMS which has been improved or upgraded by IBM (I believe) and is called retrieVe which I think might be part of uniVerse (which I think is what IBM calls PICK these days.) Anyway ADP uses the old ENGLISH it seems, not retrieVe. ENGLISH, as I mentioned in an earlier post, has limited operators and no function building capability. Also no way to join files. PICK has multivalued fields which is a challenge for me. An ENGLISH statement in the selection clause allows for unlimited ANDs and ORs. Here is my first selection clause used on the GL Detail object:


AND WITH ACCTG.DATE.EXT BETWEEN “08/01/10” AND “08/31/10”

The problem with ADP DAP selection clause development is that there is no good source for the Property (Field) Names (called DICT names in ADP speak). The help desk advises you to use an Object exploration tool to look into the “Desc” property of the file on which the object depends and, if the developer described things properly, the DICT name would be there. The problem is that it is not always clear what file the object gathers its information from. Needless to say I invested a significant amount of time searching for the correct DICT name for the property I wanted to filter on. I now have an Excel spreadsheet that houses all my successful ENGLISH statements and what they do for which object.

The above ENGLISH statement says to filter the XML Response to only include GL entries for Account 205 (which would be Contracts in Transit) with an Accounting Date in August 2010. I learned later though that the BETWEEN operator is not inclusive; so I received no 08/01/10 or 08/31/10 entries!

It was very frustrating trying to build successful ENGLISH statements. There are so many wonderful fields available for which I could not find a successful DICT name that I decided that I would need to filter the response after it is returned from the server. This seems inefficient as hell but until I figure a better way to get to these DICT names and, in order to have the most robust data extraction system possible, I need to filter the response……. with XPath….yikes!

As I mentioned before I have never delved this far into XML processing so I reviewed everything I could find on the web about XPath and PowerShell and tested and experimented.

First I developed a script without using Select-XML and then one using it. In the first script which I will develop in this post I used an XPathNavigator object and passed in XPath query strings so here it is:

$Url = http://xxx.yyy.zz.aa:pppp 
$webclient = new-object System.Net.WebClient 
$soapMessage = @" 
<?xml version="1.0" encoding="UTF-8"?> 
<eoapi:Request xmlns:eoapi=http://www.adp.com/xxxxxx version="1.0" ShowUnknownElementErrors="Yes"> 
   <Session> 
   <Connection> 
      <Product>Product</Product> 
      <Server>Server</Server> 
      <Password>password</Password> 
      <Pooling>No</Pooling> 
   </Connection> 
   <GetSet TimeoutSeconds="1800"> 
    <Qbject>Employee</Qbject>
    <Group>DealershipCode</Group> 
    <Selection></Selection> 
    <MultiFlag>E</MultiFlag> 
   </GetSet> 
  </Session> 
</eoapi:Request> 
"@ 
# Upload the Soap payload ($SoapMessage) to the URL:PORT of the ADP XML Engine ($Url)
$result = ([XML]$webclient.UploadString($Url, $soapMessage))
# Create an XPath Object by eliminating the duplicate reply tags starting the XML string at 2nd Reply tag (one with the group attribute), then convert that string to Xml ([xml])
$xpath = [System.XML.XPath.XPathDocument][System.IO.TextReader][System.IO.StringReader]($result.reply.session.get_InnerXML() | out-string | %{$_.substring($_.IndexOf('#Run the query and store the OuterXML property of the results in $qryResult
$qryResult = ($navigator.Select($query) | %{$_.OuterXml})
#Add Reply Tag to query result to create Root
$qryResultXML = [xml]('<Reply>'+$qryResult+'</Reply>')
#Date conditions are handled in the pipeline (until I figure out how to convert in the XPath expression) then send required fields to a CSV file.
$qryResultXML.reply.Employee | ?{[dateTime]$_.DateAdded -gt [dateTime]"2010-04-01"} | Select HostItemID, CustNo, NameCode, AccountingAccount, DateAdded, LastUpdate, CustOrCompanyCode, FirstName, MiddleName, LastName, Name1, SSN, CreditLimit, DealerField1, DealerField2, DealerField3, DealerField4, DealerField5, DealerField6, DealerField7, DealerField8, DealerField9, DealerField10 | Export-CSV "C:\Users\John Donnelly\Desktop\Employee.csv" -NoTypeInformation
#Open Excel
$excel = new-object -comobject Excel.Application
#Make Excel visible
$excel.visible = $true
#Open the CSV just created
$workbook = $excel.Workbooks.open("C:\Users\John Donnelly\Desktop\Employee.csv")

Step by step:

In this instance I am perusing the Employee object. The field names I use are fairly common. The first several lines are the same as in the previous post. I use a WebClient object to send a request to the XML Engine and store the results in the $result variable.

I then create an XPathDocument object and pass in the $result.reply.session InnerXML (slightly modified) using the object’s get_InnerXML() method

I then invoke the CreateNavigtor() method on the XPathDocument object and create a navigator object and store it as $navigator.

The fun part was writing a few queries which I have listed with all but one commented out. I could not figure out how to get DateTime to work so I put all DateTime filters in the pipeline later.  In this script the uncommented query pulls all employees who are coded as persons not businesses (interesting distinction eh?) . The date query commented out indicated that I can’t figure out how to write that one. Anyone care to comment?

I then invoke the Select() method of the $navigator object and pass in the query storing the OuterXML of the result in $qryResult. then in order to have an XML object to put in the pipeline I need to give it a root tag, in this case a Reply tag.

Next I select the Employee node like so; $qryResultXML.reply.Employee and pipe that to a foreach to filter out the DateAdded properties we don’t want and then Select the fields we want and Export –CSV.

This took me about 5 hours to put together because of the dearth of PowerShell related discussions about XML and my lack of coding experience.. I really want to thank Dan at PluralSight for his write-ups on XML and PowerShell. They were wonderfully specific enough to help me through this.

This script is somewhat a kluge but it works and will serve as the basis for more refined queries in the future. Thanks for listening and if you see anything strange, please comment and help out those who might rely on my work in the future.

Digg This