There are times when you want to bulk load data, especially if you are doing significant process changes or new customer installs, unfortunately there aren’t really any generic bulk load tools that I’ve come across that are…um…free or don’t offend my sensibilities 🙂

A colleague (Alistair Johnson of JBC – www.jbc.co.nz) wrote an Excel VBA macro which used the mvxAPI Toolkit to push data in to M3 via the APIs. Essentially you’d specify the username/password, API and transaction and it would generate columns that represented each of the different fields in the API.
We could then add rows of data in to the respective columns, execute the macro and it would extract each row, then each column and populate the appropriate APIs fields and execute it.

It’s a very handy tool (and one day I will re-write it to use the REST API calls!)

Last year I was doing some work for a new M3 customer and the decision had been made to not SQL any data in to their system as part of the original dataload. As I’m sure many of you know, this is a rather challenging prospect. Most of the data they wanted in their system had APIs – so I could use the spreadsheet mentioned before. However there were a number of areas around fixed assets and the item user defined fields where this simple isn’t possible. What we could do however was wrap a display program in a webservice and call that webservice to populate the data.

The problem then becomes how do we bulk load the data against the MWS/LWS webservice? I could write a quick little application for each of the webservice calls we will make – but this would be throw away one use code – which as a fledgling consultant really didn’t sit very well with me. I wanted something that would be reusable. I was told about some other tools – pocketSOAP which is a nolonger maintained tool – but I wanted to avoid having to install anything. So over the course of several evenings and a weekend or two I did some research on how I could generate SOAP requests on the fly – I didn’t want to have to parse the .wsdl files – it was going to be too much of a time investment given the time commitments. With my research I came across several different discussions on StackOverflow about compiling a .wsdl in to an assembly, then you can dynamically load that assembly and call the methods – this appealed to me as a relatively quick and easy solution.

With quite a lot of experimentation I managed to get proof of concept code written to handle basic M3 WebServices. I experimented with the display programs and further refined the code so it would handle the oddities that occur with that type of webservice (we have to do recursive calls), and finally had a single .Net executable which would read a spreadsheet, locate a webservice endpoint and dynamically generate an assembly which would allow me to determine all of the fields in the webservice. I then create a column for each of those fields in the spreadsheet. A user can populate each row with the respective column values and then we use the same technique to generate an assembly before pushing each row in to the webservice via the dynamically generated assembly.

This solution requires a single .exe, Microsoft Office (I’ve only tested against Office 2013) to be installed and .Net4.5

The application looks like this

A button called Generate Spreadsheet will prompt for the path to an Excel spreadsheet which has certain parameters configured in one of the tabs. It generate the columns representing the fields in the WebService.

Import Data will actually import the data off the spreadsheet and push it to the appropriate webservice.

So our spreadsheet Parameters Tab looks like this

Where we populate the path to the WSDL file, the username and password that has authority to call the webservice. And finally the method in that webservice that we will call.

In the Data tab we will populate from cell D7 across for each field in the webservice. A user can then enter in data from row 8 in to the appropriate column.

Example:

1. I populate the fields like so
   
2. I save and close my spreadsheet.
3. I run the M3 WebService Data Import tool and clink on Generate Spreadsheet.
4. I get prompted for a spreadsheet to read so I point it at the spreadsheet I just saved in step 2.
5. My spreadsheet will open and after several seconds I will be prompted in Excel to save. I hit save
6. My Data table of my spreadsheet is now populated with the different fields in the webservice call
   
   Which is what we see if we looked at the webservice in Visual Studios object browser
   
7. Now I populate my data starting at row 8. Then I save. (remember to format your cells as TEXT so Excel doesn’t format them and cause errors)
8. Finally I click on the Import Data button and will be prompted for a spreadsheet – I will point it to the spreadsheet in step 2.
9. The M3 WebService Data Import will load the spreadsheet and start processing it. If an exception is encountered it will write the error in to column A for the corresponding row.

You can retrieve the source for this tool from here:

https://github.com/potatoit/M3_WebServiceDataload

And just the .exe and the template spreadsheet from here:

https://github.com/potatoit/M3_WebServiceDataload/releases/latest

And you should see the following

I’ve tested this pushing through display programs and wrapped M3 APIs. But the testing has been limited to the scenarios that I specifically had to work with. So though I expect it to work, it should be tested in your scenarios extensively.

The tool is only designed to push data in to M3, not to extract data.

Enjoy,

Scott