A user recently asked us for help importing a trial balance produced by CCH iFirm software.
As this seems to be becoming a more popular format, we though it would be helpful to understand the structure and perhaps make a few shortcuts in our TB importer to accomodate this.
We looked at the iFirm Excel TB and found that it contained a lot of merged cells, which were causing trouble on the import.
Un-merging the sheet only revealed a further can of worms - the prior year balances were often (but not always) on a different line to the other data line in the Excel TB, so our import misses the prior year data in these cases.
We wrote to CCH to see if there was another way of extracting TB data, and they replied that although they are aware of the issue there is no timeframe expectation on fixing it.
Our best workaround at this stage is as follows:
- Remove the headers from the iFirm TB and make the column headings our suggested code, name (or account), debit, credit and prior - all on the same line
- Use save as on the .xls file to convert to PDF (I know this sound bizarre - but the converter effectively screen-scrapes the TB data and leaves out all the untidy and inconsistently merged cells)
- Use Adobe PDF converter (or similar) to export back into Excel
- Check that everything looks okay, add a section column and import
If you are an iFirm user perhaps you could write to them and ask them to fix this issue. If they get enough suggestions along this line they will probably prioritise it (that's what we do!).
Then we will be able to make a streamlined importer to make the whole process much easier.