Thursday, 13 March 2014

Extracting data from Paradox DB for easier reporting system

Few months ago I had to do extraction of data from application that is using Paradox DB. 
Application was running great but it takes to much manual work for reporting system that we need to do.
So as Linux admin I came to idea of making vbs script(because it was running of Windows XP) and make my job more easy.

Idea was to convert one Paradox table into some manipulative format like txt or csv and from that new file format extract necessary information.

Searching on the net for solution I came across to this perfect program called Paradox Converter.
God things about him is that you can do conversion thru CMD because I wanted to do this conversion through vbs script. I downloaded trial version. Only restriction in trial version is that you can only convert 50 column of particular table what in my case was more then enough.

God thing about Paradox Converter is that you can insert parsing parameter like date,you can select what column to use,what column to parse,etc. in file and when you execute conversion there is no need for human interaction.

Because I was unable to master vbs scripting in short time that I need to done this task I decide to install Cygwin and use all simplicity and power of Linux on Windows! :)

Now practical part!

Suppose you have Paradox table called Paradox_table.DB and you want to convert it to txt and from that new file do wanted parsing.

Suppose that we installed Paradox Converter in Program Files.(I will only be talking about CMD usage of Paradox Conveter. In case you want GUI, this is no post for you.) Find location of Paradox_table.DB. 

Most simple conversion would be like this:

C:\Program Files (x86)\Paradox Converter\pxcnv.exe C:\my documents\Paradox_table.DB C:\my documents\Paradox_table.txt

where Paradox_table.txt is converted file from Paradox Database to txt. You can call it what ever you want.
Executed like this, this will convert all columns. In case that you want only certain column to convert than in you should add this parameter


at the end of line.

There are many filter options that you can use. For me it was interesting to use date parameter so that I can sort information by day and not to use empty fields. To use filter use parameter FILTER like this


at the end of line where filter.txt is text file in which you will store filter parameters.
My filter.txt file look like this

$ cat filter.txt

So finally my executing line is

C:\Program Files (x86)\Paradox Converter\pxcnv.exe C:\my documents\Paradox_table.DB C:\my documents\Paradox_table.txt /COLUMNS:column_name1,column_name2,column_name3/FILTER:filter.txt
This will convert my Paradox table into txt file only converting columns that I want filtering values that I want.

You can make .bat file in which you will store above command.

I called my prdx2txt.bat

Now we will use Cygwin!

#cat prdx2txt.bat
"C:\Program Files (x86)\Paradox Converter\pxcnv.exe" "C:\my documents\Paradox_table.DB" "C:\my documents\Paradox_table.txt" /COLUMNS:column_name1,column_name2,column_name3 /FILTER:filter.txt

Now when you want to use in script, use it like this



This saved me a lot of time.

I hope this will help you!:)

No comments: