Solved

Importing invoice details into QuickBooks from PayPal

Posted on 2014-09-24
15
577 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 90

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
 
LVL 90

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 90

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now