Why are the Q99 payroll data conversion exports from ADP so complicated & impossible to decipher?
To process trillions of payroll calculations and transactions, Automatic Data Processing (ADP) has been using the IBM mainframe since the 1960s. Although ADP clients navigate through modern front-end graphical user interface (GUI) through new school web & mobile applications, the backend payroll calculator continues to be an old school mainframe. The IBM mainframe technology was introduced to the world 60+ years ago when data storage on physical round magnetic reel tapes was very expensive. Therefore, data exports from the ADP mainframe, often referred to as “quitter tapes”, have a unique & compact format designed to minimize the amount of memory needed to store the information being saved. This minimalistic data footprint design makes it difficult for HR & Payroll professionals to extract, transform, and load (ETL) information from ADP into modern HR applications such as Workday, Oracle, and SAP/SuccessFactors.
Although extremely complicated to work with, the ADP Q99 quitter tapes are the most reliable source of payroll history conversion data that you can get from ADP. Luckily for you, through dozens of ADP data migration engagements, KloudLift has created a repeatable ETL process built with python scripts to process Q99 quitter tapes and transform them into information that can be easily loaded into your new HCM system.
Get Started Today
Let a KloudLift data expert help you extract, transform, and load your ADP data.
Solutions We Offer:
Most of us are familiar with delimited files - the data contained in these files have a special character marking the end of a column in each record/row of information. Any character may be used to separate the values, but the most common delimiters are the comma, tab, and colon. The vertical bar (also referred to as pipe) and space are also sometimes used. The most common export we have all seen is comma space variable (CSV) where the end of the variable sized column is denoted with a comma, for example:
“FirstName”, “LastName”, “Address1”, “Address2”, “City”, “State”, “ZipCode”
“Robert”, “Fernandez”, “123 Main Beacon Hill Street”, “Apt. 6173”, “Boston”, “MA”, “02114”
The CSV example above is the most common way to export data from a legacy system except the mainframe. Why? Because every quote and every comma used to separate the data uses more memory/space and, as we alluded to in the introduction above, computer memory was worth more than GOLD back in the 1960s. It takes a special kind of data scientist to work through the intricacies of a Q99 quitter tape from ADP - we have taken the time to decipher and script the data conversion process to transform ADP exports into usable information. Our data conversion consultant can very quickly help you map the legacy fields to your new HCM fields so we can produce the import-ready file you need for Workday, Oracle, SAP, or other cloud HCM systems.
The Q99 quitter files have no headers - there is no way to tell what is a tax or taxable amount except through tedious trial and error. These files come from a mainframe that first started with the concept of punch cards. Instead of columns, the data is organized in rows. Unless you have worked through a high volume of quitter files in the past, you will be hard-pressed to figure out what data is being represented in each punch card, where it starts, and where it ends.
Countless hours of mapping apples to oranges. Your target system will most likely want “QTD in pay components” but ADP stores quarter-to-date and year-to-date cumulative data in various accumulators. You will have to identify each accumulator one at a time in order to get the mapping right for each pay component.
Inconsistent configurations across multiple ADP company codes. Although ADP service representatives do a good job of maintaining a standard configuration across your various legal entities, their accumulator configuration on the ADP mainframe often diverges from one company code to the next. ACC Code AB1 could be QTD vacation earnings in one company code while the same AB1 in another CoCode is collecting holiday hours. This inconsistency creates a lot of manual work, if the ACC-to-Pay Component mapping is done by hand. Using KloudLift’s proprietary python scripts library, we’ve automated the mapping exercises to transfer as many as 5,000 accumulators to their corresponding pay components for a single client.
Reconciliation of pay component to ADP AMC totals. At minimum, you will want to reconcile every single pay component, taxable wage, and tax amount for every single employee in scope - this is another time suck. ADP may have hidden configuration (i.e., tax blocks) on some of their pay components which can create discrepancies when reviewing total tax & taxable wages at the employee level. You will need to automate the reconciliation process to ensure your data is 100% reconciled and balanced. Best of all, if you’re doing a mid-year conversion, you will have to rinse and repeat this process for every single quarter of the year of conversion.
Get Started Today
Let a KloudLift data expert help you extract, transform, and load your ADP data.