Importing invoice details into QuickBooks from PayPal

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.
Go-BruinsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JohnBusiness Consultant (Owner)Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill BachPresident and Btrieve GuruCommented:
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
Go-BruinsAuthor Commented:
That's more that we are technically capable of, but thank you.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

JohnBusiness Consultant (Owner)Commented:
@Go-Bruins  - Thank you and I was happy to help.  Thank you Bill for additional information on the matter.
0
Bill BachPresident and Btrieve GuruCommented:
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
Go-BruinsAuthor Commented:
Thank you. Have you had any experience with "Big Cartel" ecommerce software?
0
Bill BachPresident and Btrieve GuruCommented:
No.  I have done extracts from both MivaMerchant and ASPDotNetStorefront, but not "Big Cartel".
0
Go-BruinsAuthor Commented:
It appears that I can download .CSV files that show order details from Big Cartel. Does this make the import procedure easier?
0
Bill BachPresident and Btrieve GuruCommented:
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
Go-BruinsAuthor Commented:
In this scenario, will I have to write code as you mentioned earlier?
0
Bill BachPresident and Btrieve GuruCommented:
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
Go-BruinsAuthor Commented:
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
Go-BruinsAuthor Commented:
I'm now beginning to wonder if QuickBooks (Intuit) itself doesn't offer an ecommerce solution that ties everything together...
0
Bill BachPresident and Btrieve GuruCommented:
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
JohnBusiness Consultant (Owner)Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
QuickBooks

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.