Use Excel to Update NetSuite without CSV, ODBC or Web Services

This article is relevant if you want to use Excel to update NetSuite but you are not interested in purchasing third party tools and you want to avoid tedious CSV file exports and imports.

Background

For one of our client engagements, the Accounting department was looking for a more rapid way to update customer payments from their lockbox operation. ¬† On a daily basis, an Excel file was delivered to the client representing that day’s cash receipts. ¬†The client was looking for an easy way to update NetSuite to apply the lockbox cash receipts against open invoices.

When the General Approach to Update NetSuite is Inefficient

The general way that most approach this situation is to target NetSuite through a CSV upload.  The challenge with the CSV approach is that you have to get all your data lined up to reference appropriate entity IDs, items, and other information before the data will successfully import.  When getting data from a third party source that has no clue about your NetSuite business system, you are confronted with massaging the data in a spreadsheet and use power user functions, such as VLookup, to reference the proper information before it can be uploaded to NetSuite.

All of this data preparation work negates the value and begs for automation — especially when receiving data in a frequent manner. ¬†Traditional types of automation may be unfeasible. ¬†For example, our client’s lockbox operation was offered as a competitively priced service by their bank — yet the bank had no capacities to develop an integration with NetSuite. ¬†Furthermore, our client was not going to invest in any old-school IT which may typically would entail setting up local database applications.

Leverage NetSuite RESTLet / SuiteLet Technology and Excel Macros

Based on the design pattern offered in this article I previously crafted,
How To: Avoid Data Downloads with Excel Formula Based NetSuite Saved Search Queries,
we solved the challenge. ¬†The article demonstrates the way to create Excel Macros that uses Microsoft’s ¬†XMLHttpRequest¬†library to call out over the web to a custom NetSuite SuiteLet (or RESTlet). ¬†NetSuite receives the request from Excel and processes the information returning a result that will indicate success or failure. ¬†In our case, if successful, the SuiteLet will return the URL of the customer payment record we created against the open invoice.

The actual mechanics of what we created is not being presented here because it simply is an enhancement of the work in the original article.  But what is important to illustrate is that you can do quite a bit of processing outside of conventional approaches for data import or full blown system integration.

In our client’s case, the solution was as simple as cutting-and-pasting a formula each day and recalculating the worksheet to do the work. ¬†This is very easy for the novice Excel user. ¬†If the data didn’t link, the client would research the reason, refine the data in the sheet, and then hit F9 (Excel’s manual calculate) to recalculate.

Take Control of Your NetSuite System

Most people just use software. ¬†We believe that software was meant to be enhanced to increase revenue and lower costs to ultimately drive more profit. ¬†NetSuite’s power is often locked up primarily because of limited leadership and / or lack of innovative thinking to solve challenges. ¬†If you are looking for superior help with your NetSuite system, contact us.

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is Southern California's NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 25 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to setup a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - Google Plus - YouTube

| Tags: , , , , | Category: Accounting, NetSuite | 2 Comments

2 Comments

  1. Tom Albert
    Posted February 22, 2017 at 12:07 pm | Permalink

    Hi Marty,
    We have customers who send us orders via csv files. I am very adept at creating autorun macros to import and reformat the files in various formats from various customers into a standardized format ready for import. We need a way to automate the actual sales order import process. I am wondering if you would recommend the process you described in your articles “Use Excel to Update NetSuite without CSV, ODBC or Web Services” for this purpose? Can multi orders and lines be imported this way?

    thanks for your attention,
    Tom

  2. Posted February 22, 2017 at 8:28 pm | Permalink

    We will be posting a series of articles soon on how we are using Google Sheets to call RestLets to do just what you are asking. We moved away from Excel because the automation is not cross-platform. Excel for MAC doesn’t support the HTTP library. I am happy to turn you on to these algorithms. Please contact me for direct conversation at http://www.prolecto.com/services/innovations/

    Marty

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>