Is it Possible to Merge Two Nutrition Information Panel Calculator Backup Files? Yes, it can be done, and we have done it, but it is unbelievably difficult.
Being a food technology consultancy, we produce a large number of NIP’s. Over the years we’ve had a few incidents where we have lost our cache and had to rely on backup files, and have had situations where staff have imported our main file from my computer, and done some work on their computer. The problem is that the member of staff has ended up with important information on their computer, and that information has had to be manually added to the main file.
Recently, with one member of staff leaving who had too many panels to easily manually transfer, we were faced with a major problem that was slowing us down. Surely there is a way of taking the two files, from two machines, and merging them, right?
I had a look at the file structure a few years ago and concluded that it might be possible, but would be extremely tricky. I didn’t have the time back then, and frankly, didn’t have that much of a need. Now was different, I still didn’t have the time, but also didn’t have the time to do it manually.
I had a dabble for a few hours and uncovered some major problems with trying to merge the files. In frustration I emailed FSANZ and asked if it is even possible; this was their reply…
I have never had much luck copying recipes from other NPC text files into one and trying to reload a single file into the NCP.
“Although theoretically I think it is possible, in my opinion, the easiest way is to load the file that contains the recipes you want and work off that one. You can then create / add new custom ingredients if needed. You can always load the other file and create a recipe for each custom ingredient so that you can at least re-enter the same values.”
In other words, yep, you can probably do it, but we can’t.
My first effort took a very long time and resulted in a file that won’t upload. The online calculator has a validation process that checks the file as you upload it, and if it is not perfect, it will not load the file.
If there is a way of performing this task using standard Excel, I don’t know what it is. Its possible that I could now go back with the knowledge I have now of the file, and manipulate it using Excel, but I suspect that it wouldn’t do the job. There are simply so many hurdles in the data.
While researching techniques we discovered a newish feature to Excel – the Power Query Editor. This is a very advanced add on to Excel and it turns out to be extremely handy. Even if you are an expert at Excel, it won’t help you much, its like a completely new piece of software.
After a lot of playing around with this editor, we were able to produce a file that can almost totally automatically take 2 NIP files, which contain many duplicates (that’s one of the tricky bits), and merge them into a single, valid file. We can even get it back into a date sorted format.
This is without a doubt the most complicated Excel task I have undertaken. It involved learning how to manipulate an advanced piece of data analysis software and understanding the data very well.
If you have this problem of having 2 files, then we’re happy to help you merge them. Send the 2 files to us and we can get them joined.
We can even do it confidentially for you if that is a requirement, by utilising a couple of pieces of software. We won’t require copies of the files so you can keep your secret recipes secret. All you need is a copy of Excel 365 on a Windows format and we can take it from there.
