QuickBooks Import: 1099 - Desktop
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. At this time the only method available for 1099 importing is the Manual Extract method:
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 1099s 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.
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. Once the CSV file is created DO NOT open it up in Excel to look at it and DO NOT modify it.
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
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 on the screen and change their order. If the order has been changed, the import will not work.
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" in Step 1 and select the box to "Combine with previous file imported" in Step 2.
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.
The trick here is to examine your recap. If you do not see any totals then the mapping of data in the 1099 Detail Transaction Report has not been completed. You can access the mapping database by using the buttons labelled 1/2/3 below the recap list. First select button 1 to see what accounts are being used on the 1099 Detail Transaction Report. Then go to button 2 which is for mappings. For Desktop we include some standard mappings for you. But since Desktop bases the 1099 on accounts in the chart of accounts you may need to add some field mappings. In button2 all you are doing is telling our program which box on the 1099 to use to put the description used under the column titled "1099 Box" in the Detail report. After mapping you then select button 3 which will recalculate the recap list at the top.
A list of those employees with invalid Taxpayer ID#s is also provided on the right. 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