Solved

Importing invoice details into QuickBooks from PayPal

Posted on 2014-09-24
15
627 Views
Last Modified: 2016-03-24
Hi,

Is there a way to import PayPal transaction data into QuickBooks as invoices? Right now, we can import PayPal data as journal entries that hit the income and expense accounts, but we're looking to import the invoice detail data and print the invoice from within QuickBooks.

Thanks in advance.
0
Comment
Question by:Go-Bruins
  • 6
  • 6
  • 3
15 Comments
 
LVL 94

Accepted Solution

by:
John Hurst earned 250 total points
ID: 40343644
No. You can import transactions and journal entries (as you noted), but you cannot create a Bill (with links to Accounts Payable type) or an Invoice (with links to Accounts Receivable type) from a journal entry.
0
 
LVL 28

Assisted Solution

by:Bill Bach
Bill Bach earned 250 total points
ID: 40343838
I have not done this with Paypal, but I have written a basic script (using VBS) that extracts data from my web-based cart (ASPdotnet Storefront) and creates invoices directly into QuickBooks. The script has two pieces -- one that reads data from the storefront database, and another which creates the data in QB.

If you can somehow extract the data from Paypal. Then I suspect that what you want would be very possible. In the worst case, you can save the paypal HTML page and parse that, but this may involve a manual step to save the page in a folder. Once you have that, the rest is easy.

On the QB side, you need to purchase a license to QODBC, and then write some simple INSERT statements in the script. (Examples are on the QODBC site.)
0
 

Author Closing Comment

by:Go-Bruins
ID: 40344020
That's more that we are technically capable of, but thank you.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 94

Expert Comment

by:John Hurst
ID: 40344033
@Go-Bruins  - Thank you and I was happy to help.  Thank you Bill for additional information on the matter.
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40344104
It really is not that hard.  In fact, here is a simple VBScript that builds an Invoice from QODBC:

    WScript.Echo "Opening QuickBooks Connection..."
    Set QBconn = CreateObject("ADODB.Connection")
    strConnect = "DSN=QuickBooks Data;"
    QBconn.Open strConnect
    Set QBobjRS=CreateObject("ADODB.Recordset")
    Set QBobjRS=QBconn.Execute("sp_optimizeupdatesync Invoice") 'This makes sure that the Invoice database in cache is up to date
      ' Use Item of "Email" with no tax for this generic import...
      ItemId = "1450000-1077634110"
      ItemDesc = "Email Software to: customer@domain.com; "
      SalesTaxId = "20000-1084564850"
      CustomerId = "5520000-1133190876"
      ARAccountID = "510000-859669459"
      ItemTaxId = "10000-858461043"
      CustMessageId = "120000-1017777555"
      ShipMethodId = "B0000-970836072"
      SalesRepRefId = "10000-1084564850"
      TermsId = "40000-858460945"
      Export = "INSERT INTO InvoiceLine (InvoiceLineItemRefListId, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, FQSaveToCache) VALUES ("
      Export = Export & Q & ItemId & Qcq & ItemDesc & Q & ", 0.0, 0.0, 1)"
      WScript.Echo Export
      Set QBobjRS = QBconn.Execute(Export)
      Export = "INSERT INTO Invoice (CustomerRefListId, PONumber, CustomerMsgRefListId, Shipaddressaddr1, Shipaddressaddr2, Shipaddressaddr3, Shipaddressaddr4, ShipMethodRefListID, SalesRepRefListID, TermsRefListId, TxnDate )"
      Export = Export & "VALUES(" & Q & CustomerId & Qcq & PONumber & Qcq & CustMessageId & Qcq
      Export = Export & ShipToAddr1 & Qcq & ShipToName & Qcq & ShipToAddr2 & Qcq & ShipToAddr3 & Qcq & ShipMethodId & Qcq & SalesRepRefId & Qcq & TermsId & "', {d '" & Today & "'} )"
      WScript.Echo Export
      Set QBobjRS = QBconn.Execute(Export)
      Set QBobjRS = Nothing

Open in new window


This creates an invoice, and also creates one Invoice Line item with an Email address on it.  (The codes and such are pulled from QB's other related tables, but these codes fill in the default values for a number of fields.)

To add another item to the Invoice after the fact, you need to first locate the TxnID, like this:
  QBQuery = "Select TOP 1 TxnId from Invoice WHERE RefNumber = '" & InvoiceNumber & "'"
  Set QBobjRS=QBconn.Execute(QBQuery)
    'Read the Data for the first Invoice
    MyTxnId = Trim(QBobjRS.Fields("TxnID"))
    WScript.Echo "QB Transaction ID is " & MyTxnId
  QBobjRS.Close

Open in new window


Adding additional items only requires locating the ItemId and adding a new line item, like this:

        SKUQuery = "SELECT ListID FROM Item WHERE Name = '" & MySKU & "' AND ParentRefFullName <> 'Products'"
        Set SKURS=CreateObject("ADODB.Recordset")
        Set SKURS=QBconn.Execute(SKUQuery)
        IF Not SKURS.EOF Then
          MyListId = Trim(SKURS.Fields("ListID"))

          'Insert the Part Number First
          InsertQuery = "INSERT INTO InvoiceLine (TxnId, InvoiceLineItemRefListId, InvoiceLineQuantity) VALUES"
          InsertQuery = InsertQuery & "('" & MyTxnId & "','" & MyListId & "', 1)"
          Set QBobjRS=QBconn.Execute(InsertQuery)
          WScript.Echo InsertQuery
        else
          MyListID = ""
        end if
        SKURS.Close

Open in new window


Of course, this is the latter half of the problem -- you still need to be able to extract the data from the PayPal side.  However, the process is not as difficult as you might think.  I import data from unknown data formats by simply pressing Ctrl-A, creating a new text document in my import folder, and then pasting the contents into the text file.  Thus, the first part becomes a parsing routine to strip out all the gunk and locate the important fields, which are usually indicated with consistent formatting, headers, or the like.
0
 

Author Comment

by:Go-Bruins
ID: 40344133
Thank you. Have you had any experience with "Big Cartel" ecommerce software?
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40344161
No.  I have done extracts from both MivaMerchant and ASPDotNetStorefront, but not "Big Cartel".
0
 

Author Comment

by:Go-Bruins
ID: 40344358
It appears that I can download .CSV files that show order details from Big Cartel. Does this make the import procedure easier?
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40344386
Most certainly.  You can dump the CSV file into a specific folder, then have a process grab those files as they show up and import them accordingly.
0
 

Author Comment

by:Go-Bruins
ID: 40344391
In this scenario, will I have to write code as you mentioned earlier?
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40344416
Most likely.  In fact, what you are doing is often called an "ETL process", which stands for Extract, Transform, and Load.  Essentially, the process is simple -- you need to Extract the data from the source system, Transform it somehow so that it matches your target environment, and the Load the data into the target environment.

If you had many such projects to do over a period of time, then you would want to be looking for an ETL tool to help make this process vastly easier.  There are many available, some open source, including Actian's Data Integrator, Informatica, Jaspersoft, Pentaho, and many more.  Many of these tools allow you to parse CSV files automatically and have super-powerful transformation modules to convert data between data types, parse things like names and addresses into their component parts, and so on -- all with very little or no "real" coding.  However, they usually have a pretty steep learning curve, and a pretty steep price tag to match.  Of course, when data changes on the source or target, the transformations don't change, and you can address new data formats almost on the fly.

If you only have one such project, then implementing via code can be far less expensive overall, but it can be much harder to maintain in the long run, as costs increase every time someone has to go in and update it to deal with a change in the data or transformations.

To directly answer your question -- getting the data in CSV format simplifies the "extract" side of things.  It is far easier to parse a CSV file than an HTML or text document.  However, you will still need to code the data read, as well as the transform and the load.
0
 

Author Comment

by:Go-Bruins
ID: 40344429
Ugh..thanks.

And I suppose that's not the whole of it. The PayPal transactions can easily be downloaded and imported (it's in IIF format), but tying the individual payments to the individual invoices would be yet another hurdle to overcome...
0
 

Author Comment

by:Go-Bruins
ID: 40344453
I'm now beginning to wonder if QuickBooks (Intuit) itself doesn't offer an ecommerce solution that ties everything together...
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40344463
Agreed.  However, the system may be tracking unique numbers to link them automatically.  If coming from the general public, perhaps a name/address match would be possible, too.  You'd really need to look at the raw data to be sure.
0
 
LVL 94

Expert Comment

by:John Hurst
ID: 40344469
You would need a third party e-commerce solution for that. Go to the Intuit Marketplace and look for e-commerce.  It is at marketplace.intuit.com.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Issue configuring a dropdown selector on a donation form. 13 85
convert qif for use in quickbooks 4 59
Where is QuickBooks file 9 34
Paypal Payment Methods 9 77
By Linda Saltz Customized item descriptions from bills DO import into a customer invoice!   This is a wonderful tip for companies that want to use generic items like Hardware, Software, Mileage, Travel in their item list which helps keep the i…
You may need to view past transactions from previous QuickBooks files or other QuickBooks company files when:       1. you are working in another QuickBooks file       2. QuickBooks is closed QuickBooks users who would benefit from this artic…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question