DataMagic Technical Column Vol.11

[Data correction section

Introduction

Business data, even data with the same meaning, can sometimes be expressed in different formats depending on the business system. A typical example of this is date data. For example, dates on application and registration forms are often in the Japanese calendar, but the receiving business systems generally use the Gregorian calendar for all dates. When the formats used to express dates differ between the Japanese and Gregorian calendars, it becomes necessary to correct the data and convert it into the appropriate format for each.

DataMagic If the date is a "date", it can be automatically converted to any format, such as removing the year and month and using only numerical values, in addition to simple Western/Japanese conversions. In addition to date conversion, a wide variety of correction functions are available, ranging from right-justification/left-justification and correction of decimal places (rounding) to tax-excluded/tax-inclusive conversion.

This TIPS will introduce an example of converting date and amount data contained in training application data. Please note that in order to use this TIPS, DataMagic must be installed on your computer. For information on where to obtain DataMagic and how to install it, please refer to the separate article "Installing DataMagic."

• DataMagic trial download
• DataMagic for Windows Installation
• DataMagic Manager Installation

How to operate

Step 1 - Prepare the source data

First, prepare the database data that will be used as the source data for conversion. This file is a SQLite3 format database file that defines a table called P_MASTER and stores the following data. Once the data is ready, save it in the following folder on the PC where DataMagic is installed.
C:\work\SAMPLE11\ (saved with the file name "in")

»Download the source data (Note: The sample file is in zip format. Please unzip it before use.)

Step 2 - Download and configure DataMagic script file

Download the script file below and save it in the folder where DataMagic is installed.
C:\work\SAMPLE11\ (saved as "sample11.igen")

Launch DataMagic management screen and import the downloaded script file into DataMagic. To import, select [Tools] → [Bulk Registration of Management Information Files] from the file menu. Specify the downloaded file on the bulk registration of management information files screen and click the import button. If the import is successful, the ID SAMPLE11 will be registered on the data processing information list screen.

»Download the script file (Note: The sample file is in zip format. Please unzip it before use.)

Step 3 - Run DataMagic

Double-click the data processing information ID SAMPLE11 registered in step 2 and check that the file names for the input and output settings are set correctly. Open the data processing information ID SAMPLE11 from the screen and click the "Execute" button at the top of the screen.

Step 4 - Check the execution results

When execution is complete in step 3, a file called "out" will be created in C:\work\SAMPLE11\, which is specified in the output settings. Please check this file using an editor such as Notepad.

lastly

This time we introduced examples of data corrections such as converting from the Gregorian calendar to the Japanese calendar and truncating decimal places for consumption tax calculations, but various data corrections are possible, such as rounding off or padding with 0 or spaces. It may be a small thing, but creating a program to do it can be quite time-consuming.
Please download DataMagic trial version and try out the technical column explanations.

• The trial version is free to use for 60 days.
• After you sign up for the trial version, you will receive 90 days of free technical support.

• DataMagic trial download

DataMagic Column List

Related Content