ARBER Batch Upload with Excel

Contact

Please see the instructions below for how to successfully format an XML file for an ARBER batch upload using Excel. Importing the XSD schema into Excel and attempting to format an XML file from that seems to consistently fail. The process below outlines how to import a valid sample XML file into Excel, so Excel can correctly rebuild the ARBER schema for your batch upload.

Instructions

Download TRU Equipment List from ARBER

Note: This step is only required when using a batch upload to update registrations in your account on ARBER or mark them as inactive.  If you are uploading new registrations, please skip to the next section.

  1. Log in to ARBER
  2. Hover over the Reports tab near the top left and click on Equipment Reports
    • Reports Tab
  3. Select Equipment Report in the drop down and indicate you would like a CSV as your output
    • TRU Equipment List
  4. Click SUBMIT and download the file
  5. When you open the file with Excel, you will see a list of all TRUs in your account; TRU trace numbers, which are needed for updating existing registrations, are in Column A

Import Sample File into Excel

  1. Choose and download the appropriate sample XML file for your situation
    • Click on the link for that sample file
    • This will open that XML file as a page on your web browser
    • Save that page as an XML file (.xml extension) on your computer using Ctrl + S
  2. Right click on the XML file you just saved, hover over Open With, and select a text editor, such as Notepad or Notepad++
  3. Update the company information in the arb:CompanyProfile section to match your company
    • Note - if your company uses a non-US Tax-ID or Address, please contact arber@arb.ca.gov for assistance
  4. Save and close the file
  5. Right click on the XML file, hover over Open With, and select Excel
  6. In the Open XML window, select “Use the XML Source task pane”
    • Click OK on the next pop up that notifies you Excel will create a schema based on the XML source data
    • You will see a blank Excel workbook with the XML Source task pane on the right side
  7. In the XML Source task pane on the right that displays the schema, do the following in this order:
    • Drag “ns1:SchemaVersion” to cell A1 ns1:SchemaVersion
    • Drag “ns1:CompanyProfile” to cell A2 ns1:CompanyProfile
    • Drag “ns1:TRUs” to cell A3 ns1:TRUs
    • Right click on cell A3 and click “Refresh”
  8. The data from the sample XML file should populate the fields, you will see your company information at the top with no headers, and two TRUs in a table below
    • Note - these two TRU registrations are samples so Excel can generate the schema correctly, you will need to delete them before uploading to ARBER
  9. Add your TRUs to the table, starting with row 4. Here is a breakdown of all possible columns. Not all columns will be present in your file:
    1. ns1:tracenum – The trace number for the TRU; only used when updating existing registrations
    2. ns1:PurchaseDate – The date you purchased the TRU
    3. ns1:UnitModel – TRU model name
    4. ns1:UnitModelYear – TRU model year
    5. ns1:UnitSerialNumber – TRU serial number
    6. ns1:CompanyEqupmtNumber – (optional) internal company equipment number to help you track your equipment; leave blank if you do not wish to provide
    7. ns1:Refrigerant – The refrigerant used in the TRU; use Other for TRUs which do not use refrigerant, such as TRU generator sets
    8. ns1:DateOfSale – The date the TRU was sold; only used when marking TRUs as Sold
    9. ns1:StatusActive – TRU active status; use “Y”
    10. ns1:MfrName – TRU engine manufacturer
    11. ns1:Model – TRU engine model name
    12. ns1:ModelYear – TRU engine model year
    13. ns1:SerialNumber – TRU engine serial number
    14. ns1:PowerRating – TRU engine power rating; use “Greater than or Equal to 25HP” or “Less than 25HP”
    15. ns1:OriginalEngine – Specifies that the TRU’s engine is the original engine; use “Y” for original engines
    16. ns1:MfrName2 – TRU Manufacturer
    17. ns1:VIN – VIN from the trailer for trailer TRUs or from the truck for truck TRUs; only used for truck and trailer TRUs
    18. ns1:PlateNum – License plate number from the trailer for trailer TRUs or from the truck for truck TRUs; only used for truck and trailer TRUs
    19. ns1:LicenseState-US – The full name of the state for which the license plate was issued (example: “Oregon”); only used for truck and trailer TRUs
    20. ns1:RailCarReportingMark – The railcar reporting mark issued by the American Association of Railroads for their UMLER system; only used for railcar TRUs
    21. ns1:BIC – The BIC code of the TRU genset or refrigerated domestic shipping container; only used for TRU gensets and domestic shipping container TRUs
  10. (Optional) Use “Verify Map for Export…” at the bottom of the XML Source window on the right and correct any errors
    • Note - this option may not always appear
  11. Save the Excel File

Export from Excel to XML

  1. Go to the Developer tab in the top ribbon and in the XML section, click on “Export”
  2. (Optional) You can check the XML file generated by Excel file against the .xsd schema v2.12 to see if there are any formatting errors (Notepad++ or Microsoft XML Notepad can do this, as well as various other text editors)
  3. Log into ARBER Test and upload the batch file under the Batch Processing tab. You may still receive some errors, but these will most likely be related to typos or issues with the data, rather than the format of the XML file or any missing fields

Sample XML Files

If you do not see a sample file that fits your situation, you can either choose the closest match and reformat accordingly, or contact arber@arb.ca.gov to request that sample file be added to this page.

Open a file below, and then use your web browser to save it to your computer as an XML file (.xml extension).

New Registrations

Update Existing Registrations

Mark Registrations as Inactive