AASHTO Trns•port® Payroll XML

Combining Payroll XML files

Prepared by the Louisiana Department of Transportation and Development

(LA DOTD)

AASHTO Trns•port, the AASHTO Trns•port logo, the AASHTOWare logo, BAMS/DSS, the

Trns•port CRLMS logo, Trns•port Estimator, the Trns•port Estimator logo, Trns•port

Expedite, the Trns•port Expedite logo, Trns•port FieldBook, the Trns•port FieldBook logo,

Trns•port FieldBuilder, the Trns•port FieldBuilder logo, Trns•port FieldManager, the

Trns•port FieldManager logo, LAS, the Trns•port LAS logo, PES, the Trns•port PES logo,

Trns•port Preconstruction, SiteManager, Trns•port SiteManager, the Trns•port, SiteManager

logo, Trns•port SiteXchange, and the Trns•port SiteXchange logo are registered trademarks

of AASHTO.

Trns•port BAMS/DSS, Trns•port BAMS/DSS Standard, Trns•port CAS, Trns•port CES, Trns•port

Construction, Trns•port Construction/Materials, Trns•port Estimation, Trns•port LAS,

Trns•port Materials, Trns•port PES, Trns•port Preconstruction logo, Trns•port TRACER, the

Trns•port TRACER logo, webTrns•port, and AASHTOWare Project are trademarks of AASHTO.

AASHTO Trns•port, Trns•port BAMS/DSS, Trns•port CAS, Trns•port CES, Trns•port CRLMS,

Trns•port Expedite, Trns•port LAS, Trns•port PES, Trns•port Preconstruction, Trns•port

SiteXchange, Trns•port SitePad, and Trns•port SiteManager represent one or more

proprietary products of AASHTO.

AASHTOWare is a registered service mark and trademark of AASHTO. Other product and

company names mentioned herein may be trademarks and/or service marks of their

respective owners.

FieldManager, FieldBook, and FieldBuilder represent one or more proprietary products jointly

owned by InfoTech, Inc., and the State of Michigan.

FieldNet represents a proprietary product of Info Tech, Inc.

any form without written permission of the publisher. Printed in the United States of

America.

This guide shows the steps to combine data from more than one XML file prior to importing the

combined XML file to Civil Rights & Labor (CRL). The CRL application has no limits on the size

of XML files that can be imported.

The following procedures can be used to import payrolls with more than 50 classifications or more

than 18 lines of fringe benefit program information, which are the limitations found on the Excel

spreadsheet.

Combining employee payroll data from two XML files into one XML file is covered on pages 3 -

12. Combining fringe benefit program information from two XML files into one XML file is

covered on pages 13 - 20.

Combining employee payroll data

1. Enter employee payroll data for the first 50 classifications on LA DOTD’s version of the Excel

spreadsheet, found on LA DOTD’s Compliance Certified Payroll Website, and save the Excel file

in the normal manner noting it as Part 1.

2. Enter the remaining employee classifications on a separate copy of the LA DOTD Excel

spreadsheet with the same payroll number and week ending date as Part 1 and save the file in

the normal manner noting it as Part 2.

3. At this point you have two Excel files:

Combining XML files

4. Convert each Excel file to XML files using AASHTO Trns•port® Payroll Spreadsheet Conversion

Utility. Save each file to a local drive.

5. At this point you have two Excel files converted to XML files:

If you double click on either XML file it will open in an internet window but you would only be able to

copy from the file and not be able to paste to add data to the other XML file. To combine XML files,

the files must be opened with an application (XML text editor) which permits both copying and pasting.

Microsoft Notepad is shown in this guide because it is standard on most MS Windows PC’s.

LA DOTD recommends downloading “Notepad++” to your computer from the following link:

http://notepad-plus-plus.org/. This application contains extra features which will aid in processing XML

files.

Combining XML files

6. Open the Part 1 XML for your payroll by clicking once on the filename, then right click, slide

down to Open With and over to Notepad. Just leave the file open in the notepad application

window for now. We will need it in minute.

Note: If you downloaded Notepad++ that application may show up in the first sub window

depending on which Windows operating system you are running.

7. Open Part 2 XML for your payroll by clicking once on the filename, then right click, slide down

to Open With and over to Notepad

Combining XML files

At this point, both XML Payroll files are now open

8. The top of the Part 2 XML file looks similar to this:

Note: XML, which stands for eXtensible Markup Language, is just information wrapped in tags. The

information is structured into root elements, child elements and subchild elements. Root elements

are located furthest to the left in the document. Child elements are under and indented right of

root elements.

<root>

<child>

</child>

</root>

Combining XML files

In the example below <PayrollEmployee> is a subchild element to <PayrollEmployees>.

9. Scroll down until you come to the row <PayrollEmployee>, the subchild element under

<PayrollEmployees> (with an “s” at the end of Employee). This is the starting point for copying

data to be pasted into the other XML file.

10. On the <PayrollEmployee> row place your cursor as far to the left as possible to capture all the

blank spaces. Then start highlighting and scrolling down. The highlighted copy box should

appear vertically straight (flat) on the left side when completed correctly.

Combining XML files

Continue scrolling to capture all employee data down to the entire line ending with

</PayrollEmployee> Do not capture the line with </PayrollEmployees> (with an “s” at the end

of Employee)

11. Right click the mouse and click Copy

12. Next go back to the previously opened Part 1 XML file.

13. Scroll down to the bottom, place your cursor at the end of the last Payroll Employee in this XML

file. Hit enter to create a blank row above </PayrollEmployees> (with an “s” at the end of

Employee). Do not move the cursor.

Combining XML files

14. Right click the mouse and click on Paste

15. Check to see if you have any blank rows in the now combined XML file. If so delete them.

16. Next save your Part 1 XML in the normal manner noting it as the Combined XML.

Combining XML files

17. You can repeat this process if you are in need of other Excel spreadsheets to record more than

100 employees.

18. After you have experience doing this you might feel confident enough to skip the next three

steps.

19. Navigate to Cloverleaf XML https://xml.cloverleaf.net/ and click on XML Resource Kit

Combining XML files

20. Click on a Validator and follow the steps

Combining XML files

21. Navigate to and select your new combined XML file, agree to Terms of Use and click Validate

If your XML file is valid you will see this:

This only means the XML file matches the valid schema which defines Payroll XML acceptable to

the CRL application.

22. Import your combined XML file to CRL following your normal import process.

Combining XML files

Combining fringe benefit data

The standard payroll Excel spreadsheet only allow the entry of 18 Fringe Benefit programs for a

pay period. The combining of fringe benefit information from two XML files into one XML file is

similar to the process steps for combining payroll information; the data is just located in a

different section on the XML file. The two processes can be done simultaneously should you

exceed the entry limits for both employees and fringe benefit programs.

1. Enter fringe benefit data in the first 18 rows on LA DOTD’s version of the Excel spreadsheet,

found on LA DOTD’s Compliance Certified Payroll Website, and save the Excel file in the

normal manner noting it as Part 1.

2. Enter the remaining fringe benefit data on a separate copy of the LA DOTD Excel

spreadsheet with the same payroll number and week ending date as Part 1 and save the file

in the normal manner noting it as Part 2.

3. At this point you have two Excel files:

4. Convert each Excel file to XML files using AASHTO Trns•port® Payroll Spreadsheet

Conversion Utility. Save each file to a local drive.

5. At this point you have two Excel files converted to XML files:

Combining XML files

6. Open the Part 1 XML by clicking once on the filename, then right click, slide down to Open

With and over to Notepad. Just leave the file open in the notepad application window for

now. We will need it in minute.

7. Open the Part 2 XML by clicking once on the filename, then right click, slide down to Open

With and over to Notepad

Combining XML files

At this point, both XML Payroll files are now open

8. The top of the Part 2 XML file looks similar to this:

The benefit program data starts here.

Combining XML files

Note: The information in XML files is structured into root elements, child elements and subchild

elements. Root elements are located furthest to the left in the document. Child elements are

under and indented right of root elements.

<root>

<child>

</child>

</root>

9. The subchild element under <PayrollBenefitPrograms> (with an “s” at the end of Program)

is <PayrollBenefitProgram> (no “s” on the end of Program). The far left of this row is the

starting point for copying data to be pasted into the other XML file.

On the < PayrollBenefitProgram > row place your cursor as far to the left as possible to

capture all the blank spaces. Then start highlighting and scrolling down. The highlighted

copy box should appear vertically straight (flat) on the left side when completed

correctly.

Combining XML files

Continue scrolling to capture down to the entire line ending with </PayrollBenefitProgram>.

Do not capture the line with </PayrollBenefitPrograms> (with an “s” at the end of Program).

Right click your mouse and Copy

10. Next go back to the previously opened Part 1 XML file.

Combining XML files

11. Scroll down a little and place your cursor at the end of this row and hit enter to create a

blank row above </PayrollBenefitPrograms> (with an “s” at the end of Program). Do not

move the cursor.

12. Right click your mouse and then click Paste

13. Check to see if you have any blank rows in the now combined XML file. If so delete them.

14. When finished, you can run the payroll through the steps on pages 10 -12 to validate the

fringe benefit data similar to running the payroll information through the validator.

Combining XML files

15. Next save your Part 1 XML in the normal manner noting it as the Combined XML.

16. Validate your now combined XML file as described on pages 10, 11 and 12 of this guide.

17. Import your combined XML file to CRL using your normal payroll import process.