QuickBooks Import - Accounts Payable 1099
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.
Ø Check the Field Mappings 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.
Step 2: Extract
We utilize the data from the 1099 Detail 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 - 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. You will need to input the proper dates to use and then 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. When you press the OK button 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 1099 Detail 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:
Ø Your are maintaining a large number of 1099sl within QuickBooks. A large volume of data needs to be exported for this. It appears as if QB can only handle 32k of data for 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 1099 Detail report.
Ø You use QuickBooks online.
Therefore in order to extract the data, you will need to generate the 1099 Detail 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 1099 Detail Report yourself and saving the output to a CSV file.
The 1099 Detail Report is available under Reports / Vendors. 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.
Type
Date
Name
Name Address
Name City
Name State
Name Zip
SSN/Tax ID
Account
Paid Amount
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 1099 data maintained in QB is very large 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 vendors were extracted. QuickBooks has a 32.000 record limitation on export files so it will be necessary to export the first half of the year to 1 file and then the second half to another file. Run the import twice but on the second time, uncheck the box to "Delete all records for this payor prior to import."
We have placed a button on this window so you can load your QuickBooks program if it hasn't already been done. Please note that when loading QB that the QB program sometimes prompts you with an update being available. This message is sometimes displayed in the background so you may not notice it right away.
Step 3: Verification
A recap summary of the data extracted from QuickBooks is automatically generated when Step 2: Extract is performed. You should review the totals provided to ensure accuracy. Any employees with invalid Taxpayer ID#s are not included in the recap totals.
A list of those employees with invalid Taxpayer ID#s is also provided. You can correct them within this screen. If you wish to make a correction to a Taxpayer ID#, either double click on the highlighted record or select the Change Record button. Corrected Taxpayer ID#s must be input with the dashes in the correct place. If you do not have a valid Taxpayer ID# 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 Taxpayer ID# and we will treat it accordingly in our program as a System SSN.
When finished updating any invalid Taxpayer ID#s select the button to Update Imported Data. This will write back the corrected ID#s 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 ID#s already exist in the program. Those corrections will not be implemented so you will need to use a different ID# in your correction. Any ID#s 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