Using Open-Source Programming In Software Integrations

Business Scenario: "We need to integrate our ERP software with other applications (webshops, customers, suppliers etc)."

It's a simple enough idea, but when you consider each trading partner has their own specific integration criteria, things can become more complex.

In the ERP world, you should have specific tools to accomplish this (MEC for Infor M3). However, you'll come across scenarios where you don't have the firepower to deal with it. Or you do, but it's so complex to use that it isn't worth the time. This is where some basic knowledge of programming languages comes in handy. If you couple that with ETL software, you'll stand a good chance of accomplishing your problem.

In this scenario, I'm going to describe how I set about converting a CSV file to an XML using purely open-source techniques. It's not my preferred method, but the CSV module of the software I was working with simply wasn't complex enough to handle my requirements, but it's XML capabilities were so a conversion was needed prior.

This is where it gets a little techy...

The Trigger - Powershell & Command Prompts

A couple of weeks ago I stumbled across the language PowerShell. It's native to windows and can accomplish so much, the internet is awash with resource on it.

In PowerShell, I was able to write a script that would monitor a directory in windows and run a command prompt (CMD) whenever a file was detected. I also wrote some code that would act as an archiving function, so that the original file would be left untouched in a separate directory.

The file name is passed as a variable from PowerShell to the CMD script, and subsequently to my ETL tool. Using the Windows Task Scheduler, the PowerShell script is set to run on startup and continue always.

The Conversion

The ETL tool has now been activated and knows what the filename is, subsequently it loads the file in and converts it to an XML (this is another topic which I'm not covering in this article).

Note: you don't have to convert to XML you could do anything here, e.g. send someone an email.

The resultant XML output is saved to a location of choice and the original file is deleted (we still have the archive) so that we don't reconvert the original CSV file.

The result was a free solution that could archive and convert a file to any standard of my choice. The ETL tool handles the conversion well, but it lacked the directory monitor functionality, which I added through utilizing PowerShell.

If you're interested in the code or methodology behind this then please get in touch and we can talk more.