Overview of the process

Transforming data from one form to another is easy with the right tools. Let us automate this process and remove the labor common in Excel file manipulation. Whether you need text files, excel files, document stores, or databases transformed into a new structure, we can develop a custom solution which extracts, parallelizes, and transforms data in bulk so a 2 month project only takes a few days. See below for more information on how we can help you.

Example 1. Excel Payments file to Quickbooks FileContact Us

Example describes the process for converting a sample Payments Excel file into one compatible for import into Quickbooks.

Tackling data files in an unfamiliar format depends on finding developer resources provided by the vendor which explain the file 'schema'. Quickbooks Desktop's schemas are defined here. Examples on how to produce each data element are very useful in determining the patterns that must be reproduced in an automated program, and seeing a live example is even more helpful in determining the edge-cases that might occur in a large volume of data that may not be as obvious in a file with only a few rows.

Downloading the bill.iif ZIP file then opening the 'Full Deposit.iif' file shows a peculiar file format. At first glance there is data everywhere without a clear pattern until you notice the "!" character which denotes the definition of a HEADING. So each instance of a "!" means a heading is being described where each column name is separated by a tab character. Then subsequent non-'!' rows will follow the same pattern as the '!' heading row but with column values.

This weird system allows a Quickbooks file to establish a heading at the top of the file for Accounts, follow that pattern for a few rows, then establish a new heading for Customers thus creating a combined file with many different types of defined objects like Accounts, Customers, and Bills.

In the above example, there are 5 different data sections for different entity types in Quickbooks which would be considered bad practice in other industries. It is much more common to have one file for one entity type which would mean the example above should produce 5 much smaller files with isolated entities, but Quickbooks engineer's must have made the decision to keep the data parsing dynamic where a script should be capable of reading a new entity type at any point in the file.

These patterns are just a sneak peak of what a data specialist must spot at a glance to guarantee efficient and timely data transformation and delivery to the output format of your choice.

Example 2. Adding data to an existing Excel fileContact Us

Example describes scenario where client asked for an on-demand script which can inject data from a file into an existing Excel balance sheet with formula injection to save time.

Putting data exactly where YOU need it is the most important job for a data specialist. Understanding a business process and finding room for automation inclusion increases job efficiency and satisfaction. This is the same value one client was looking to achieve by asking us to write a script to combine two files into an Excel balance sheet with cells pre-populated with formulas after processing. Such a simple program is run multiple times a day saving 10 minutes of manual labor each time this combination needs to occur AND lets the client move onto what is important - interpreting the spreadsheet. 

The above Excel snippet is a fully generated snippet placed on an existing Excel sheet with source data, and it only took 5 seconds with an automated program instead of 10 minutes. Data Transformation focuses heavily on modifying data to align with YOUR business process. If you have a business process that needs more automation, please contact us!