×
Menu
Index
Search
 
   
   
   
 
www.softrules.com
 

Example of an Excel based import tool

 
SoftRules® works only with XML documents. But because of the large amount of tools available that is able to translate XML, the possibilities are endless. For example it is also possible automate the import of a 'foreign' Excel file in your backoffice with SoftRules® after it has applied all kinds of necessary enrichments.
 
Suppose your relation can deliver you the following file.
 
What is striking is that the name fields are combined (you would like to have name, middle name and surname separated). The same applies to the address (street, number and addition should be separated for your backoffice). Also the car details are missing (but the license plate is known).
 
This XML structure is needed to import data in your backoffice.
 
<?xml version="1.0"?>
<CustomerDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://SoftRules.com/CustomerDocument.xsd">
     <CustomerWrapper>
          <Insured>
               <ID>69866</ID>
               <Firstname>John</Firstname>
               <SirName>Tapscott</SirName>
               <Street>Steele Street</Street>
               <HouseNumber>1362</HouseNumber>
               <Zipcode>60187</Zipcode>
               <Town>Wheaton</Town>
               <EmailAddress>jtapscott@softrules.com</EmailAddress>
          </Insured>
     </CustomerWrapper>
     <Contract>
          <Policy>
               <PolicyNumber>845784</PolicyNumber>
               <Startdate>20050603</Startdate>
               <CarrierProductCode>34</CarrierProductCode>
               <CarrierName>The Insurance Company ltd.</CarrierName>
               <Premium>343.12</Premium>
               <Payment>377.44</Payment>
               <IPT>34.12</IPT>
               <MotorVehicle>
                    <LicenseNumber>43SR22</LicenseNumber>
                    <Make>Volvo</Make>
                    <Model>S40</Model>
                    <BodyStyle>1.6D Momentum</BodyStyle>
                    <Weight>1306</Weight>
                    <RetailPrice>28,098</RetailPrice>
                    <Driver>
                         <Firstname>John</Firstname>
                         <SirName>Tapscott</SirName>
                         <Street>Steele Street</Street>
                         <HouseNumber>1362</HouseNumber>
                         <Zipcode>60187</Zipcode>
                         <Town>Wheaton</Town>
                         <EmailAddress>jtapscott@softrules.com</EmailAddress>
                    </Driver>
                    <LiabilityCoverage>
                         <CarrierCode>12</CarrierCode>
                         <Premium>343.12</Premium>
                         <Payment>377.44</Payment>
                         <IPT>34.12</IPT>
                    </LiabilityCoverage>
               </MotorVehicle>
          </Policy>
     </Contract>
</CustomerDocument>
 
As you can see name and address fields are separated and car details are fetched from an external webservice. Also a number of codes are added to be able to recognize the policy in the backoffice. Besides that a 'Payment' and a IPT (Insurance Payment Tax) field is added (where Premium + IPT = Payment). Each row in the Excel document (stands for a policy) should be import in the backoffice with a separate call.
 
To convert an Excel file to a very useful XML document we use a 3rd party tool. We can advise you about this tooling. Please contact us.
 
<?xml version="1.0" encoding="utf-8"?>
<ImportDemo>
     <Policy>
          <PolicyNumber>845784</PolicyNumber>
          <CustomerNumber>69866</CustomerNumber>
          <CustomerName>John Tapscott</CustomerName>
          <CustomerAdress>1362 Steele Street</CustomerAdress>
          <Zipcode>60187</Zipcode>
          <City>Wheaton</City>
          <E-mailaddress>jtapscott@softrules.com</E-mailaddress>
          <Startdate>2005/6/30</Startdate>
          <Premium>343.12</Premium>
          <Licence>43SR22</Licence>
     </Policy>
     <Policy>
          <PolicyNumber>934232</PolicyNumber>
          <CustomerNumber>38763</CustomerNumber>
          <CustomerName>Brigette Shane</CustomerName>
          <CustomerAdress>1000 Ashton Lane</CustomerAdress>
          <Zipcode>78753</Zipcode>
          <City>Austin</City>
          <E-mailaddress>bshane@comparity.nl</E-mailaddress>
          <Startdate>2001/11/28</Startdate>
          <Premium>451.76</Premium>
          <License>CP4188</License>
     </Policy>
     <Policy>
          <PolicyNumber>172557</PolicyNumber>
          <CustomerNumber>26526</CustomerNumber>
          <CustomerName>Gertrude Jenkins</CustomerName>
          <CustomerAdress>1500 Raver Croft Drive</CustomerAdress>
          <Zipcode>TN 37929</Zipcode>
          <City>Knoxville</City>
          <E-mailaddress>gjenkins@volmachtpremies.nl</E-mailaddress>
          <Startdate>2008/1/15</Startdate>
          <Premium>264.33</Premium>
          <License>3357VP</License>
     </Policy>
</ImportDemo>
 
We can build a schema based on the above XML document, or we can build a configuration without a schema.
 
After the configuration has been built, the process wil look like this.
 
 
For every row in the Excel document a webservice will be called to fetch the car info. After that the XML document will be converted to the correct format and it will be sent to the backoffce.