Data conversion is a significant task in any implementation of a new accounting system. Transferring code tables such as charts of accounts or transactions and balances from the old system to the new can be a headache for your in-house information systems (IS) people or consultants. Even when the system is installed and running, there is often a need to interface accounting to other external systems either to receive data or to pump it out.
Data Junction is a data extract, transformation, mapping and conversion tool that can help with all of these tasks. In a nutshell, Data Junction reads data from a single source file/table and writes it to a single target file/table (see Basic Data Conversion Steps).
Data conversion products can be used for a variety of purposes, such as:
- converting charts of accounts and transactions from old to new accounting systems as part of a system cutover process
- converting transaction files from external systems, such as payroll, into formats suitable for importing into accounting modules such as the general ledger
- converting transactions from multiple source accounting systems for import into a single accounting system (for example for consolidations)
- converting transactions from a single source accounting system for import into multiple accounting systems (for example for intercompany transfers or allocations)
- extracting data from an accounting system to feed into specialist report writers or external financial datamarts (for example, for decision support purposes)
Many other data conversion tools exist, but most tackle higher-end conversion tasks, are focused primarily on relational database sources and are significantly more costly than Data Junction. At its price point of less than a thousand dollars, Data Junction is significantly cheaper than many competitive products while able to handle a wide range of common single file/table data conversion tasks. Of course, Data Junction has weaknesses, some of which are the lack of:
- built-in scheduling for running conversions or batches
- ability to use SQL join queries to extract data from multiple source tables
- function to summarize multiple source transaction records into summary target balance records
The latest 32-bit version of Data Junction is a single-user application that requires an Intel PC running Microsoft Windows 95 or NT (a UNIX version is also available). Data Junction can convert from and to dozens of source and target file formats. These include generic formats such as Access, ASCII, Btrieve, COBOL, dBase, EBCDIC (mainframe) and ISAM files, specific accounting application files (see Some Accounting File Formats Supported), and a wide range of other database and application file formats. It is likely that you will be able to find some way of converting from a source to a target file using Data Junction's capabilities.
To begin using Data Junction, create a new Data Conversion. Once it's defined and tested, save it to a library for reuse later. A Data Conversion comprises three steps:
- Select a source file/table type to convert from and specify the location of the file/table.
- Select a target file/ table type to convert to and specify the location of the file/table.
- Specify source to target data mapping rules to transform the data.
I carried out a couple of basic tests of Data Junction using data exported from my Intuit QuickBooks accounting package in Intuit Interchange Format (IIF). Although Data Junction supports Intuit Quicken QIF files (and many other accounting system file formats), I used the Delimited ASCII format as the type for my source file (a format to which most accounting systems can export data).
I converted the IIF files to tables stored in a Microsoft Access 97 database. Data Junction allows you to replace, append or update the data in the target file/table so you can incrementally add to your target file/table over time. The package also allows you to browse source and target data to check if the data looks like you expect it to look before and after you convert it.
I conducted a simple file conversion from my QuickBooks chart of account data into a Microsoft Access table (see Data Junction Screen Show). This kind of simple file conversion took less than half an hour to define and run. By storing my QuickBooks transaction data in an Access table I have a wider range of analysis possibilities than QuickBooks alone can provide.
More Complex Conversions
If the source file to be converted is in a format such as Fixed ASCII or Hexadecimal, a special Source Record Parser tool can be used to tell Data Junction how to understand the organization of the source data. Also, if the data is stored in a database server that is secured, you can specify the server, database and table name where the data and the user ID reside, and the password needed to access the data, so that Data Junction can log in and read the data required for the conversion.
Mapping the data can be very simple, such as a 1:1 mapping of source fields to all target fields or mapping selected source fields to selected target fields. However, mapping the data can also be very complex; this task is made easier through the use of Data Junction's Expression builder. You can use expressions to "program" how data is extracted from the source file/table by applying sophisticated filters to the source data. You can also use expressions to program how source data is "transformed" when it is written to the target file/table. Expressions may include looking up values in external tables for substitution purposes or use operators, such as shown in the table below.
The expression builder can make use of over 70 functions to build source data filtering or target field data transformation logic. Apart from using conditional or logical operators to filter source data for conversion, you can also define ranges of record numbers to be converted or define that every nth (e.g. every 10th) record only is converted.
When a conversion is run you can specify that certain error logging takes place and then view the error log to identify and fix problems. Records rejected by a conversion can also be written to a separate file for interrogation. You can also print definitions of conversions to help in documenting data conversions.
Because many data conversions are repetitive, you can capture for reuse both source data structures (for the data from which you most regularly convert) and target data structures (for the data to which you most regularly convert). These data structures store the definition of the source data file/table and location and data extract filtering rules or the definition of the target data file/table and location and data transformation rules. You can also define conversion batches so that multiple conversions can be run as a single task and both individual conversions and batches can be run outside of Data Junction as "command line" operations to avoid the need to load and run the conversion from within the Data Junction program.
Overall, I found Data Junction easy to use for the task it was designed to do. So the next time a consultant tries to charge you an arm and a leg for data conversion or IS complains about programming yet another extract from your accounting system, ask if they know about tools like Data Junction — you could save yourself a bundle of money and a lot of time.