Chip eServices Blog

A missing piece of functionality in the Chip eServices Suite has been the ability to import data. We have many customers that have integrated solutions that synchronise data but we had no solution to simply import new data from a file or spreadsheet. We knew we had to tackle this problem. To date we have done data import as a free service for our customers but as our customer base and our partner list grows this situation cannot continue. A simple effective tool was needed. In January we decided to start developing a capability so that our customers could do this for themselves. The solution we have decided to adopt has turned out to be incredibly powerful.

First Insight

When deciding what kind of an import tool we needed we first looked at what format the data was available in. Every request for data import comes in either a delimeted or fixed column file or as an Excel spreadsheet. Since the first two file types can be imported into spreadsheets we soon realised that what we needed was the ability to import from spreadsheets.

Second Insight

All of our customers are Excel experts. Our customers like manipulating data in spreadsheets. The reason that customers like our export functionality is because the get the data in a spreadsheet and once its in a spreadsheet they can do what they like with it. Even though we offer an XML export function, I dont think a single customer has ever found a use for it. Don’t let anyone fool you, when it comes to people managed data migration the spreadsheet is King.

The Solution

So we needed a spreadsheet import solution that our customers found easy to use. At a brainstorming session one of our developers, Christian, who has a little Visual Basic for Applications (VBA) experience told us that Excel 2003 can call a Web service. We have a fully developed suite of Web services for our applications complete with validation rules and built in business logic. The solution was so obvious to everone at the meeting that a silence fell on proceedings. (How come the most useful meetings are always in the canteen and the ones in the board room are a waste of time?).

You guessed it, we are building a spreadsheet for each data type (Model Numbers, Spare Parts, Customers etc.). You copy and paste the data from your spreadsheet into the spreadsheet we supply. Press a button that calls the validation and upload Web services and your data is imported in a jiffy. We build the VBA code into the spreadsheet that does the magic. If the data doesn’t validate correctly we tell you why and on which line the validation failed. Type in a correction and press the button again.

The only cheat is that when a user selects Import from our application menus they are actually downloading a spreadsheet. The upload comes later.

There is one more good idea to tell you about. We do data export into spreadsheets and we do data import from spreadsheets. So, we have decided to make the two spreadsheet types very similar. If, for example, you want to update your Model Number list, export the list to a spreadsheet. Make the modifications you require. Copy the updated records to the import version spreadsheet, validate and upload. Eezeepeezee.

As is always the case in these situations, we will be rolling out this technology one data type at a time. Let us know which spreadsheets you need developed first.

There will be one surprising candidate in the first round of spreadsheets developed. Many customers get large RMA requests from logistics centres that accumulate from multiple retail outlets. So a request for 200 or more Items in an RMA is not unusual. This request usually arrives by spreadsheet attachment to an email. From now on that request can be copy and pasted into the upload spreadsheet and a days work done in a few minutes.

The only limitation on this technology is that we will only support Office/Excel 2003 or later and also remember that Excel only supports about 65,500 lines in a spreadsheet.