Now-a-days most of the organizations/corporations are still using legacy software which produces output in the form of flat files. Integrating these flat files and text files with other data formats such as HTML, XML, etc. in a modern computing environment is increasingly difficult.
Many tools are available in the market to convert flat file to HTML file in Windows operating system. UNIX based operating systems have gained a lot of popularity in the recent years because of its stability and security. In data warehousing project, it would be very helpful when we get the statistics report in HTML format through mail. It can be accomplished using UNIX scripts.
The main intention of this blog is to convert any delimited or flat file to HTML report and send it through mail.
Step by step process:
Scenario 1: If the number of columns in a file is known
Consider we have data in a file and we know the number of columns in that data file.
Step 1: Create a txt file with data as below.
(Cat is the command to view/read the file)
Note: In Unix, extension of the file can be anything. Unix doesn’t care about extension.
Step 2: Create a script file as below and save it with a name. This will create a file ‘ttt.txt’ and send it to the mentioned mail id with the subject line ‘Test HTML File’.
<th bgcolor > is for adding background color. (#D60SF7 is for Purple color)
Mail – is the command used to send a mail. (-s is for adding subject line)
Step 3: Execute the created script file as below. When we execute, this will create a file ‘ttt.txt’ in current directory and send that created HTML file to the mentioned mail id with the subject line ‘Test HTML File’.
The below two screenshots are showing the file created after execution and the mail has been sent.
When we open the attachment, it will display as below.
Uuencode: we can send mail without using uuencode command as below,
> mail -s “Test HTML File” abc.xyz@ymail.com < ttt.txt
During mail delivery, the mail is relayed from one mail delivery agent to the next, until it finally arrives at the recipient. Each delivery agent may transform the mail message, e.g. by stripping the 8th bit of each character, removing NUL bytes (ASCII code 0), converting the end-of-line character LF (“line-feed”, ASCII code 12) to a local representation (e.g. CR LF), or removing trailing space or TAB characters from each line.
Since only some characters are sure to arrive unmodified, the traditional solution is to encode the mail from binary format to a text format that is safe to transmit. The program used for this is called “uuencode” (“UNIX to UNIX encoding”), the program to decode the data is called “uudecode”.
Scenario 2: If the number of columns in a file is unknown
Consider we have data in a file and we don’t know the number of columns in that data file.
Step 1: Create a text file with some data as below.
Step 2: Create a script file like below and save it with any name.
The below script will open the file /home/dsadm/Vignesh/table.txt and convert that file into HTML format and save that into the file /home/dsadm/Vignesh/report_testfile.html
Awk – It is the usual command for searching data from file, printing the file and it has many functionalities. In this script it will open the data file and read that file line by line and store that every line in the variable ‘$i’.
Step 3: Execute the created script file as below. After successful execution of the script, report_testfile.html file will be created in current directory and will be sent in email.
When we open the attachment, it will display like below.
Scenario 3: Converting the query’s output to HTML report
Here, we have taken a query to display the rejected records information in a html report.
Step 1: Create a file with the query that needs to be run.
Step 2: Create a script file as below.
Nzsql – Command for logging into netezza database. ( -f – is the option to give the query file path, -o is the option to give the path where the output should be stored, -h is for host name, -d is for database name, -pw is for password)
This will execute the query file and store the output of the query in mentioned file. Then, the HTML report will be sent in mail.
Step 3: Execute the created script file. This will create an output file (p.txt) & HTML file (report.html) and send that in email like below.
When we open the attachment, it will display as below
Hope this blog would be helpful for you to generate any kind of HTML report using UNIX scripts. Kindly provide your comments on the blog and meet you soon with another interesting blog…!
