Clive Woodhouse

Various Thoughts

OR: How to solve the dreaded "Can't remove the ID from the split definition in the middle of the import file"

PayPal seems to do a terrible job of exporting history files to the Quickbooks iif format  I usually get a "Can't remove the ID from the split definition in the middle of the import file" error when importing into Quickbooks Pro 2013.  Here is a work-around that I managed to figure out.

To download my PayPal transactions into a "PayPal" account created in Quickbooks' "Chart of Accounts" I go into my transactions in PayPal then select the downloads link.  This opens the Download History window where I select a date range and select "Quickbooks (.iif)" from the dropdown menu.

Once the file downloads open it with Excel as a tab delimited file.  The original file creates an error because it is missing a SPLID column.  In Excel insert a new column before column B with the "DATE" header.  In this new column B insert "TRNSID" in B1 and "SPLID" in B2 (without quotes).

If you wish to have the transactions imported as cleared you can also do the following.  I prefer this so I can distinguish these newly imported values from existing data to help manually balance the account.  You should now have a column H with "MEMO" as the header.  In column I add "CLEAR" to both I1 and I2.  Then, in I3 add the formula "=IF(ISBLANK(F3),"","Y")".  Copy this down to all the data rows.  This will add a "Y" for any rows that have a transaction in them.  Your spreadsheet header should look like this.

Quickbooks spreadsheet example

Save the spreadsheet, making sure it retains its original tab delimited (text) format and not as an Excel file.  For safety, backup your Quickbooks file before attempting an import.Now import this into Quickbooks as an iif file and everything should work.

 

Leave your comments

Post comment as a guest

0

People in this conversation

Template Settings

Color

For each color, the params below will give default values
Blue Red Oranges Green Purple Pink

Body

Background Color
Text Color

Header

Background Color

Footer

Select menu
Google Font
Body Font-size
Body Font-family
Direction