Interfacing with Priority from an external program

In or around August this year, my company commenced contact with an independent contractor who is the representative of the Imos program in Israel. This program is intended for woodwork shops, but like others of its ilk, it considers itself to contain all the functionality necessary for such a shop and so has difficulty in interfacing with external programs, especially ERP.

After several months of waiting, I finally received a file yesterday which contains enough information to build an order but also to create bills of materials for the parts contained within the order. My job was to read the file and import the data into Priority.

My first problem was that the file was in xlsx format whereas Priority likes tab delimited files. I had done some preliminary work on this a few months ago, writing a tool which supposedly would save the xlsx as tab, but this program encountered problems. Trying to save an excel file as text brings up messages such as 'do you want to save the changes you made to x.txt' (what changes? I just saved the file). When I answer yes, then I'm asked whether I want to replace the existing x.txt file. What is this? How is one supposed to answer?? It was clear to me that I wanted to save these awkward questions from the user.

When I saved the file manually as tab delimited and imported it with the temporary interface structure, I discovered that I had another problem. For some reason, there were lines which had fields separated by two tabs and as a result, the line wasn't read correctly. I also discovered (at a later stage) that some fields had quotation marks appended before and after the data, and that some fields even had a stray carriage return at their end.

So I was forced to write a program in Delphi which would read the raw xlsx file (by means of automation), correct the above problems then output a tab delimited file with the name of my choice. It will be interesting to see whether these faults occur in future files. I have written the program such that nothing will happen if these faults do not exist.

Once I had got past all of these hurdles, I was able to import the data and store it in a temporary table within Priority. As opposed to previous procedures which I have written to import data, I decided to use a different technique of first importing the data then building files within Priority which would be used to create the final data. This was easy as I have done this before, albeit with a slight twist.

I didn't bother with building the bills of material as this will require a fair amount of fiddling and imagination and my stores had run dry yesterday. Still, I was very pleased with my output. A meeting will be held this afternoon with the contractor, the wood factory manager and assorted others. It will be nice to show some progress after months in which it seems that nothing has happened.