Free online consultation available. Please feel free to contact us.

DataMagic Technical Column Vol.10

<DB table data extraction>

Introduction

In recent years, "big data" has become a new keyword that has attracted attention, and the importance of data within companies is increasing. All kinds of data are scattered within companies, and when analyzing this data, one of the distinctive challenges is "collection and processing of data."

With DataMagic, you can easily extract data from a database by simply setting an extraction key, and output the data in a file format suitable for data analysis.It can also be combined with user-written SQL statements, making it possible to handle complex processing.

This TIPS will introduce an example of data extraction using the database (SQLite) included with DataMagic. Unlike Oracle or SQL Server, SQLite does not use a server-client configuration; instead, it reads and writes directly from files. This means that a server is not required, and you can try extracting data from a database using this TIPS without having to prepare a separate database. 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."

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\SAMPLE10\ (saved as "in.db")

»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\SAMPLE10\ (saved as "sample10.igen")

Launch DataMagic management screen and import the downloaded script file into DataMagic. To import, click the [Import Management Information] icon under [Tools] on the start screen. Specify the downloaded file on the Import Management Information screen and click the Import button. If the import is successful, the ID SAMPLE10 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 SAMPLE10 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 SAMPLE10 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\SAMPLE10\, which is specified in the output settings. Check this file using an editor such as Notepad.

lastly

This time we introduced data extraction from a database, but it is also possible to insert and update data into the database. There are two setup methods available: "Simple specification," which allows for easy integration without having to worry about SQL, and "SQL statement specification," which allows you to specify the SQL to be issued when inputting and outputting data. This allows for a wide range of tasks, from simple to complex.
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 Column List

Related Content

Return to column list