Access text files using SQL statements by DB Query Analyzer

Ma Gen feng  

(Guangdong Unitoll Services incorporated, Guangzhou510300)

Abstract   Is it a dream that you can access text files using SQL statements？

 But now, it is true that DB Query Analyzer provides you a power tool which can help you realize the dream above. I’ll give you a sample to show its powerful function. What’s more, the process is so efficient that you can get result by querying files with 2,500,000 records in about 59 seconds. Please make sure that column name coinciding with the data should be included in the first row in text file.

Key words   DB Query Analyzer

1      Brief introduction of DB Query Analzyer  

DB Query Analyzer is presented by Master Gen feng, Ma from Chinese Mainland. It has English version named ‘DB Query Analyzer’ and Simplified Chinese version named .

Development of DB Query Analyzer

After four years research, design, development and about three months integrated test based on Oracle, Sybase, DB2, Informix, MS SQL Server, MYSQL, MS ACCESS, FoxPro,Paradox, SQLite which is a embedded database system, EXCEL, TEXT and CSV, DBQuery Analyzer was distributed to the world In 2007. From then on, the continuous improvement of function or interface has been done.

Achievement of DB Query Analyzer

In the New products & Tools reviews of programmer second issue of 2007, DB Query Analyzer had been strongly recommended.

Now DB Query Analyzer is the top 20 database application software in the famous software website Zhongguancun online, the URL for the Simplified Chinese version  is http://xiazai.zol.com.cn/download_order/sub_550.html. Up to now, it has been downloaded more than 100,000 times.

Out of question, DB Query Analyzer 5.04 is one of the few excellent UDA ( Universal Data Access) Tools in the world for its’ powerful function, friendly interface,easy operation and applicability to every production of RDBMS.

In this essay, I will illustrate how to access text files using complex SQL statements by DB Query Analyzer. What’s more, the process is so efficient that you can get result by querying files with 2,500,000 records in about 59 seconds. 

2       Preface

DB Query Analyzer is very powerful in text file process. I’ll give you a sample to show its powerful function. Please make sure that column name coinciding with the data should be included in the first row in text files.

OS :  Windows 2000 Server

CPU: 2.8 GHZ single

RAM: 1GB

Files : .CSV/.TXT

Client tool : DB Query Analyzer 5.04

3   What’s the question to be solved

I work for Guangdong Unitoll Services incorporated, which is authorized to be engaged in Express way Network Toll Collection in Guangdong Province. Each vehicle’s toll data will be uploaded to our corporation by Every Highway Corporation. We must split all the tolls to each Highway Corporation by certain rules.

Within the recent 2 months, a great number of error records have been uploaded to our DB2 database. As a result, we have to neglect those records at first.

This week, our company decides to re-upload those neglected error records. So first step is to get all the neglected records, then distribute them in the four different client database server to re-upload them.

The two files including ‘batch 1.csv’ and  ‘batch 2.csv’ are the statistic batch that are required to re-upload. Files like ‘Client exist.txt’ and ‘Both exist.txt’ are generated by data mining from DB2 database including all the records which were neglected. While file ‘tb_road.txt’ is just exported from table tb_road in OLTP Database.

Figure2 Generate file ‘both exist.txt’ by data mining.

In order to get the results files, I have to access the five text files mentioned above using SQL statement by DB Query Analyzer.

4   Create ODBC Data Source according to .CSV/.TXT

First I create a ODBC Data Source ‘odbc_txt_csv ’ according to .CSV/.TXT by DB Query Analyzer. Click menu item toolsàODBC Data Source Manager to run the windows ODBC Data Source Administrator form. This is shown in Figure 3 and Figure 4.

Figure 3   Create ODBC Data Source odbc_txt_csv

Figure 4   Create ODBC Data Source odbc_txt_csv

   After select the directory where the target files locate, input the ODBC DSN Name ‘odbc_txt_csv ’, then click OK button to finish the whole process of creating ODBC DSN Name.

5  The whole process to access text files using SQL statement

Run DB Query Analyzer 5.04, then click ‘File’ menu to choose ‘Connect’ to open the logging window. Select ODBC Data Source ‘odbc_txt_csv ’.

Figure 5 login ODBC Data Source odbc_txt_csv, blank user name and password are OK.

Figure 6   User Tables coincide with odbc_txt_csv in ObjectView

Figure7   .CSV/.TXT file coincide with odbc_txt_csv in windows explorer

Figure8 SQL statement to generate thefour results files

The complex SQL statements in Figure 8 is bellow:

select listno,‘2014-06-16‘

from

(

    select distinct b.LISTNO,b.roadno

    --select count(*) as rec_num,sum(b.CASHMONEY)/100 asCASHMONEY,sum(b.ETCMONEY)/100 as ETCMONEY

    from

    ( 

        selectw.roadno,w.squaddate,w.roadname,w.outvehclass,

               sum(w.rec_count)as rec_count1,sum(w.cashmoney) as cashmoney1,sum(w.etcmoney) asetcmoney1

        from

        (

           select *

           FROM  [batch 1.csv] 

           where(Remark is null) and Reupload=‘Yes‘ and (cashmoney+etcmoney)<0

           union all

           select *

           FROM  [batch 2.csv] 

           where(Remark is null) and Reupload=‘Yes‘ and (cashmoney+etcmoney)<0

        ) w

        group by w.roadno,w.squaddate,w.roadname,w.outvehclass

    ) a,[Both exist.txt] b

    where  ( (b.CASHMONEY+b.ETCMONEY)<0 ) and a.roadno=b.roadno and a.SQUADDATE=b.SQUADDATE

          anda.OUTVEHCLASS=b.OUTVEHCLASSNAME

    union

    select distinct b.LISTNO,b.roadno

    --select count(*) as rec_num,sum(b.CASHMONEY)/100 asCASHMONEY,sum(b.ETCMONEY)/100 as ETCMONEY

    from

    ( 

        selectw.roadno,w.squaddate,w.roadname,w.outvehclass,

               sum(w.rec_count)as rec_count1,sum(w.cashmoney) as cashmoney1,sum(w.etcmoney) asetcmoney1

        from

        (

           select *

           FROM  [batch 1.csv] 

           where(Remark is null) and Reupload=‘Yes‘ and (cashmoney+etcmoney)>0

           union all

           select *

           FROM  [batch 2.csv] 

           where(Remark is null) and Reupload=‘Yes‘ and (cashmoney+etcmoney)>0

        ) w

        group by w.roadno,w.squaddate,w.roadname,w.outvehclass

    ) a,[Both exist.txt] b

    where  ( (b.CASHMONEY+b.ETCMONEY)>0 ) and a.roadno=b.roadno and a.SQUADDATE=b.SQUADDATE

          anda.OUTVEHCLASS=b.OUTVEHCLASSNAME  

    union

    select distinct b.LISTNO,b.roadno

    --select count(*) as rec_num,sum(b.CASHMONEY)/100 asCASHMONEY,sum(b.ETCMONEY)/100 as ETCMONEY

    from

    ( 

        selectw.roadno,w.squaddate,w.roadname,w.outvehclass,

               sum(w.rec_count)as rec_count1,sum(w.cashmoney) as cashmoney1,sum(w.etcmoney) asetcmoney1

        from

        (

           select *

           FROM  [batch 1.csv] 

           where(Remark=‘Client‘) and Reupload=‘Yes‘ and(cashmoney+etcmoney)<0

           union all

           select *

           FROM  [batch 2.csv] 

           where (Remark=‘Client‘)and Reupload=‘Yes‘ and(cashmoney+etcmoney)<0

        ) w

        group by w.roadno,w.squaddate,w.roadname,w.outvehclass

    ) a,[Client exist.txt] b

    where ((b.CASHMONEY+b.ETCMONEY)<0 ) and a.roadno=b.roadno anda.SQUADDATE=b.SQUADDATE

          anda.OUTVEHCLASS=b.OUTVEHCLASSNAME

    union

    select distinct b.LISTNO,b.roadno

    --select count(*) as rec_num,sum(b.CASHMONEY)/100 asCASHMONEY,sum(b.ETCMONEY)/100 as ETCMONEY

    from

    ( 

        selectw.roadno,w.squaddate,w.roadname,w.outvehclass,

               sum(w.rec_count)as rec_count1,sum(w.cashmoney) as cashmoney1,sum(w.etcmoney) asetcmoney1

        from

        (

           select *

           FROM  [batch 1.csv] 

           where(Remark=‘Client‘) and Reupload=‘Yes‘ and(cashmoney+etcmoney)>0

           union all

           select *

           FROM  [batch 2.csv] 

           where(Remark=‘Client‘) and Reupload=‘Yes‘ and(cashmoney+etcmoney)>0

        ) w

        group by w.roadno,w.squaddate,w.roadname,w.outvehclass

    ) a,[Client exist.txt] b

    where ((b.CASHMONEY+b.ETCMONEY)>0 ) and a.roadno=b.roadno anda.SQUADDATE=b.SQUADDATE

          anda.OUTVEHCLASS=b.OUTVEHCLASSNAME   

)      www

where roadno in (select roadno from [tb_road.txt] where areano=4407)

Figure9 When executing the SQL, CPU Usage percent immediate reaches 100%

Figure10 After 59 seconds, one file with about 60,000 records is generated

Figure11 the two source files have about 2,500,000 records

The verdict:

    In terms of interactive data process, there are two means that can be used. One way is to create tables in database and import the data to the table,then you can design SQL statement to get the results.

    The other way is to generate the temp files by data mining, then DB Query Analyzer can help you to access those text files using SQL statement. What’s more, the process by DB Query Analyzer is very efficient. It only takes me about 59 seconds to get the result from the resources files with more than2,500,000 records in PC Client. The CPU usage percent reaches almost 100%.

6   64 technical articles about DB Query Analyzer have been published or been publishing in computer journal,  Baidu Library, the CSDN resource or my four blog-website.

The following 6 articles have been published in computer journal.

DB Query Analyzer to cancel a running SQL statement in Computer Era 12th issue of 2011 in Hangzhou.

DB Query Analyzer Returns Information in More Detail While Batch SQL Statement End of Execution in Computer Programming Skill & Maintenance 24th issue of 2011 in Beijing

The Application of the Transactions Control in DB2 with DB Query Analyzer in Computer Programming Skill & Maintenance 22nd issue of 2011 in Beijing.

How does DB Query Analyzer cancel the SQL statement committed to DBMS in Computer Engineering & Software 6th issue of 2011 in Tianjin.

The Application of the Transactions Control in Oracle with DB Query Analyzer in Microcomputer Applications11th issue of 2011 in Shanghai.

    DB Query Analyzer had been strongly recommended In the New products & Tools reviews of programmer 2nd issue of 2007.

The following 64 articles have been published or been publishing in Baidu Library, the CSDN resource or my four blog-website.

The DBMS that DB Query Analyzer Users often use cover all kinds of DBMS have been published.

How to configure ODBC DSN in Client to access remote DB2 for Windows

How to configure ODBC DSN to access local DB2 for Windows

Which SQL statement is the trump card to the senior software developer

Exe packer prevent DB Query Analyzer from beging debugged have been published.

13 articles from The 1st tip of DB Query Analyze to The 13th skills of DB QueryAnalyzer have been published.

The table name must be enclosed in double quotation marks or sqare bracket while accessing EXCEL by DB Query Analyzer

DB Query Analyzer 5.04 download URL: 

http://xiazai.zol.com.cn/detail/43/420901.shtml

http://www.unitedpowersoft.com/UpFile/DBQueryAnalyzer_English_503.rar

     DB Query Analyzer Simplified Chines eversion 5.04  download URL:

http://xiazai.zol.com.cn/detail/27/264455.shtml

Remarks :

Please uninstall the former version first, then install the latest version.

My blog are  http://blog.csdn.net/magenfeng

http://blog.sina.com.cn/magenfeng

         http://user.qzone.qq.com/630414817