Contents - Index

QuickBooks Import: Payroll W2

If you have a very large payroll being maintained in QuickBooks, make sure you read the section called "Manual Extract" in Step 2 in addition to combining multiple extract files.

There are 4 simple steps that need to be done:

Step 1: Settings

Ø   Choose a payor that the imported data will be for.  The payors are available in a drop-down list.  If you start typing out the name, you will see that there is an auto find feature.

Ø   The placement of the State and Local deductions is handled automatically by us.  If there are more than two states or localities for an employee, a 2nd W-2 will automatically be created.

Ø   Indicate the format of the employee's name.  This must match the setting you use in QuickBooks.

Step 2: Extract

We utilize the data from the Payroll Detail Review Report so you may wish to review the accuracy of that report prior to this task.  You have two options that can be used to extract the data from QuickBooks:

Auto Extract - A small window running a special QB extract program will appear.  The following rules apply:

Ø Your QB data file must be opened in order for this to work

Ø You must be logged in as the administrator or else you will not be able to access the data.

Ø The Payroll Detail Review Report has already been selected for you in the drop down report menu.

Ø You will need to input the proper dates to use.  At first the program will default to the calendar year being reported.  Generally you will use those dates unless you have a large payroll which needs to be segmented.

Ø Specify a CSV (comma separated value) file to store the extracted data.

Ø  Upon accessing QB for the first time for a company, a window will appear with the title "Quickbooks - Application with No Certificate" saying that an application without a certificate is trying to access your QB data.   All you have to do is grant access.  You must check off the box that allows this program to access Social Security Numbers or else nothing can be extracted.  Windows has now changed its focus to the QB program.  You will need to switch back to the Auto Extract program.  

Ø Press the OK button and you will be brought to another screen.  On the bottom right there is a button labeled Export.  Select that button to start the extract process.  

Ø Please be very very patient as the program must first establish a connection to your QB file.  With large QB files, this process could take a long time.  After the extract is complete, you will see that data on your screen.  Select the Close button.  It is sometimes possible that the QB access hangs up.  You may see a message from QB with the options "Switch to..."  Select that option to continue the process.  This is something caused by the QuickBooks SDK which is out of our control.

Note on Certificates in QB:  The certificate allowing our program to access your QB data is stored in QuickBooks under Edit / Preferences / Integrated Applications and then the tab for Company Preferences.  

Manual Extract -  This method provides the same exact results as the Auto Extract method except that you are running the QB Payroll Detail Review report by yourself and then exporting the data to a CSV file.  There are certain conditions where you may decide to use this method which are:

Ø You setup your QB payroll for Job Costing.  QuickBooks handles that a little differently so the Auto Extract method will not work correctly.

Ø Your are maintaining a large payroll within QuickBooks.  A large volume of data needs to be exported for this.  It appears as if QB can only handle 32,000 records in an export.  In this case you will need to apply a filter when generating the file (see more on this below)

Ø Your QuickBooks file may be corrupted.  We have experienced a case where the Auto Extract program would not work on a corrupted QB file.  This was a file where it had over 10 years of history in it, was over 300 mb in size and the purge function would not work.  However it was still able to generate the Payroll Detail Review report.

Ø You use QuickBooks online. 

Therefore in order to extract the data, you will need to generate the Payroll Detail Review report using the calendar year.  You may also decide that you like this method better. You will still get the same exact results.  All you are doing is running the Payroll detail review report yourself and saving the output to a CSV file.  Once the CSV file is created DO NOT open it up in Excel to look at it and DO NOT modify it.

The Payroll Detail Review Report is available under Reports / Employees and Payroll.  It will most likely default to the current calendar year so please check your dates and make sure that you are generating data for the correct year.  When that report is generated you will have to define the fields that we need on this report so click on the button to modify report.  The following fields are the ones we need.  Please make sure that no extra ones are checked off or the import program will not work correctly.

Trans #

Date

Name (if setup for Job Costing use Source Name instead.  Also see below about applying a filter)

Name Address

Name City

Name State

Name Zip

Paid through

SSN/Tax ID

Payroll Item

Income subject to tax

Wage Base

Wage Base (tips)  [even if you don't use tips you must include it]

Amount

It is also important that the fields being displayed on the screen are in the same order as the list above.  Sometimes QuickBooks users drag and drop the columns and change their order.   If the order has been changed, the import will not work.  The columns that display on the QuickBooks report must be in the same order as above.

After you have modified the report you can optionally save it by memorizing it so you don't have to perform this modification again.  After the report has been regenerated to the screen, click on the button to export data.  The data must be exported to a CSV file.  You can call it any name you wish because you will specify that name when importing it into our program.

If the payroll maintained in QB is very large or you are using Job Costing, then you will need to apply a filter.  You will know that it is too large because when you check your control totals they will not agree.  Check it out further by pulling down the View menu and select Browse Temporary Working File.  Go all the way to the bottom and you should be able to tell that not all of the employees were extracted.  QuickBooks has a 32.000 record limitation on export files so it will be necessary to setup a filter before the report is exported.  We normally take all of the records and only process the ones we need.  When setting up a filter we only need to look at the deductions.  In order to do this select "Modify Report" and then the "Filters" tab.  In the Filter list on the left, select "Payroll Item".  On the pull down list to the right, select "Multiple Payroll Items".  A list of all payroll items will appear.  You will need to check off those that you need.  DO NOT check off any Income Items.  Remember that we pull our Wages from the Base associated with the withholding tax.  Only choose the items that you need to appear on a W-2 form.  After you do that, regenerate the report and begin the import process again.  If your field mappings are correct, the control totals should agree this time.

If the filtering still generates more than 32,000 records then you will need to split up the export.  You will most likely end up exporting the first half of the year to 1 file and then the second half to another file.  You may even have to do it quarterly.  Run the procedure to "Process Manually Created CSV File".  Then check off the box to the right to "Combine with previous file imported".  Change the file that is being accessed and run the procedure again.  You totals will be recalculated and can be viewed under Step 3: Verification.  Perform this procedure as many times as you have manually extracted files for a client.

Step 3: Verification

• A recap summary of the data extracted from QuickBooks is automatically generated when Step 2: Extract is performed.  Check the recap summary to see that you have accounted for all deductions, especially those in Boxes 12 and 14.  A proper import is contingent on the mapping of the description in the payroll item column of the Payroll Detail Review Report to the fields in our system.  The standard fields have already been mapped for you according to test data we used for QB 2006.  There is a chance they will need to be modified if you are using a previous version of QB or you have changed the description.  It is also possible that you may need to add certain deduction and/or state or local fields to the mapping.  Check the exact wording of the QB Payroll Item.  It must match EXACTLY or the item will not be imported.  If necessary, you can change the wording in our field mapping to match your QB data or you can change the wording used in QB by selecting Lists / Payroll Item List within QuickBooks.

Tip:  If you do not see a total that you would expect to see in the recap summary than either the payroll item in the payroll detail review report has not been mapped or the payroll item description has not been entered EXACTLY as it appears on the payroll detail review.

Tip:  Use the button titled "List QB Payroll Items being used".  Use this to display all payroll item descriptions that printed on the Payroll Detail Review report.  Use this to verify that you have picked up all necessary W2 items and that the description matches exactly to your mapping.

• Any employees with invalid SSNs are not included in the recap totals.  

• A list of those employees with invalid SSNs is also provided.  You can correct them within this screen.  If you wish to make a correction to an SSN, either double click on the highlighted record or select the Change Record button.  Corrected SSNs must be input with the dashes in the correct place.  If you do not have a valid SSN on file for an employee, input an arbitrary SSN with 9 digits in it and the dashes in the correct places.  Place an "X" at the end of the number.  The "X" tells us that this is an arbitrarily assigned SSN and we will treat it accordingly in our program as a System SSN. 

• When finished updating any invalid SSNs select the button to Update Imported Data.  This will write back the corrected SSNs to the temporary file holding your imported QB data.  The recap totals on the left will automatically be refreshed.  You will be alerted if any SSNs already exist in the program.  Those corrections will not be implemented so you will need to use a different SSN in your correction.  Any SSNs that are still invalid will not update the imported data and will be excluded from the recap totals.

Step 4: Import

Select the button to finalize the importing of your data.  Throughout the first 3 steps, nothing has been written to the W-2/1099 Filer files.  This button will handle the writing of the QB extracted data to our program.