Category Archives: Uncategorized

Comparing Excel files with Python

A quick Python program to compare two excel files and create an output file.


from tkinter import filedialog
from tkinter import *
import pandas as pd

root = Tk()
df1=pd.read_excel(filedialog.askopenfilename(title = "Select the file you wish to compare", filetypes = (("Excel files", "*.xls;*.xlsx"), ("Text files", "*.txt;*.csv;*.tsv"),("all files","*.*"))))
df2=pd.read_excel(filedialog.askopenfilename(title = "Select the file with which you wish to compare", filetypes = (("Excel files", "*.xls;*.xlsx"), ("Text files", "*.txt;*.csv;*.tsv"),("all files","*.*"))))
df3=pd.merge(df1, df2)
writer = pd.ExcelWriter(filedialog.asksaveasfilename(title = "Select the folder where you wish to save the file", filetypes = (("Excel files", "*.xls;*.xlsx"), ("Text files", "*.txt;*.csv;*.tsv"),("all files","*.*"))), engine='xlsxwriter')
df3.to_excel(writer)
writer.save()

Preparing NULL counts for SQL and Oracle data tables

I am posting this one after a very long time. Just thought this information will be useful for someone.

Here is the T-SQL for SQL Server that will fetch the columns in a table and provide you the NULL, ZERO, NEGATIVE and UNIQUE count of each table columns.

USE <database>
GO

BEGIN
	SET NOCOUNT ON
	DECLARE @TAB NVARCHAR(100) = <table_name>
	DECLARE @SQL NVARCHAR(500)
	DECLARE @RECORDS INT, @TOTRECORDS INT
	DECLARE @OBJECT_ID INT,
		@TOTAL INT
	DECLARE @COL NVARCHAR(100),
		@COLTYPE NVARCHAR(100),
		@TS NVARCHAR(100)
	SELECT @OBJECT_ID = OBJECT_ID FROM SYS.TABLES WHERE NAME = @TAB

	DECLARE col_cursor CURSOR FOR 
	SELECT	COLUMNS.NAME COLNAME, SYSTYPES.NAME COLTYPE
	FROM SYS.COLUMNS 
		INNER JOIN SYS.systypes ON SYSTEM_TYPE_ID = XUSERTYPE
		WHERE object_id = @OBJECT_ID;

	OPEN col_cursor
	FETCH NEXT FROM col_cursor INTO @COL, @COLTYPE

	set @sql = N'SELECT @ptotrecords = count(1) FROM ' + @TAB 
	EXECUTE sp_executeSQL
		@sql,
        N'@ptotrecords INT OUTPUT', @ptotrecords=@totrecords OUTPUT

    WHILE @@FETCH_STATUS = 0
    BEGIN
		set @sql = N'SELECT @precords = count(1) FROM ' + @TAB + ' WHERE ' + @COL + ' IS NULL'
		EXECUTE sp_executeSQL
			@sql,
            N'@precords INT OUTPUT', @precords=@records OUTPUT
		PRINT(convert(varchar, CURRENT_TIMESTAMP) + CHAR(9)+ @TAB + '.' + @COL + '[' + @COLTYPE + ']' + CHAR(9) + 'NULL' + CHAR(9) + convert(varchar, @records) + CHAR(9) + convert(varchar, @totrecords)+ CHAR(9) + convert(varchar, @records*1.00/@totrecords*100))

		set @sql = N'SELECT @precords = count(distinct ' + @COL + ') FROM ' + @TAB  + ' WHERE ' + @COL + ' IS NOT NULL'
		EXECUTE sp_executeSQL
			@sql,
            N'@precords INT OUTPUT', @precords=@records OUTPUT
		PRINT(convert(varchar, CURRENT_TIMESTAMP) + CHAR(9)+ @TAB + '.' + @COL + '[' + @COLTYPE + ']' + CHAR(9) + 'UNIQUE' + CHAR(9) + convert(varchar, @records) + CHAR(9) + convert(varchar, @totrecords)+ CHAR(9) + convert(varchar, @records*1.00/@totrecords*100))

		IF @COLTYPE IN ('MONEY', 'NUMERIC', 'INT') 
		BEGIN
			set @sql = N'SELECT @precords = count(1) FROM ' + @TAB + ' WHERE ' + @COL + ' = 0'
			EXECUTE sp_executeSQL
				@sql,
				N'@precords INT OUTPUT', @precords=@records OUTPUT
			PRINT(convert(varchar, CURRENT_TIMESTAMP) + CHAR(9)+ @TAB + '.' + @COL + '[' + @COLTYPE + ']' + CHAR(9) + 'ZERO' + CHAR(9) + convert(varchar, @records) + CHAR(9) + convert(varchar, @totrecords)+ CHAR(9) + convert(varchar, @records*1.00/@totrecords*100))
			set @sql = N'SELECT @precords = count(1) FROM ' + @TAB + ' WHERE ' + @COL + ' < 0'
			EXECUTE sp_executeSQL
				@sql,
				N'@precords INT OUTPUT', @precords=@records OUTPUT
			PRINT(convert(varchar, CURRENT_TIMESTAMP) + CHAR(9)+ @TAB + '.' + @COL + '[' + @COLTYPE + ']' + CHAR(9) + 'NEGATIVE' + CHAR(9) + convert(varchar, @records) + CHAR(9) + convert(varchar, @totrecords)+ CHAR(9) + convert(varchar, @records*1.00/@totrecords*100))
		END
		FETCH NEXT FROM col_cursor INTO @COL, @COLTYPE
    END
	CLOSE col_cursor;
	DEALLOCATE col_cursor;
END

PL/SQL code below does a similar job in Oracle.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
   L_ROW_COUNT                        PLS_INTEGER;
   L_TOTAL_COUNT                        PLS_INTEGER;
   COL  VARCHAR(200);
BEGIN
  FOR Y IN (SELECT * FROM ALL_TABLES WHERE TABLESPACE_NAME = '<table space name>' AND TABLE_NAME LIKE '<table name>%'  ORDER BY OWNER, TABLE_NAME ) LOOP
    EXECUTE IMMEDIATE    'SELECT COUNT(*)FROM ' || Y.OWNER || '.' || Y.TABLE_NAME INTO L_TOTAL_COUNT;
     FOR x IN (SELECT * FROM all_tab_columns WHERE table_name = Y.TABLE_NAME and owner = Y.OWNER ORDER BY  column_id ) LOOP
        COL := X.TABLE_NAME || '.' || X.COLUMN_NAME || '[' || X.DATA_TYPE || ']' || CHR(9) ;
  
        EXECUTE IMMEDIATE    'SELECT COUNT(*) FROM ' || X.OWNER || '.' || X.TABLE_NAME || ' WHERE ' || X.TABLE_NAME || '.' || X.COLUMN_NAME || ' IS NULL' INTO L_ROW_COUNT;
        DBMS_OUTPUT.PUT_LINE (COL || 'NULL' || CHR(9) || TO_CHAR (l_row_count, 'fm999,999,999,990') || CHR(9) || TO_CHAR (L_TOTAL_COUNT, 'fm999,999,999,990') || CHR(9) || TO_CHAR (L_ROW_COUNT*1.0/L_TOTAL_COUNT*100.0, 'fm999,999,999,990'));
  
        EXECUTE IMMEDIATE    'SELECT COUNT(DISTINCT ' || X.TABLE_NAME || '.' || X.COLUMN_NAME || ') FROM ' || X.OWNER || '.' || X.TABLE_NAME INTO L_ROW_COUNT;
        DBMS_OUTPUT.PUT_LINE (COL || 'UNIQUE' || CHR(9) || TO_CHAR (L_ROW_COUNT, 'fm999,999,999,990')|| CHR(9) || TO_CHAR (L_TOTAL_COUNT, 'fm999,999,999,990') || CHR(9) || TO_CHAR (L_ROW_COUNT*1.0/L_TOTAL_COUNT*100.0, 'fm999,999,999,990'));
        IF X.DATA_TYPE IN ( 'NUMBER') THEN
          EXECUTE IMMEDIATE    'SELECT COUNT(*) FROM ' || X.OWNER || '.' || X.TABLE_NAME || ' WHERE ' || X.TABLE_NAME || '.' || X.COLUMN_NAME || ' = 0' INTO L_ROW_COUNT;
          DBMS_OUTPUT.PUT_LINE (COL || 'ZERO' || CHR(9) || TO_CHAR (L_ROW_COUNT, 'fm999,999,999,990')|| CHR(9) || TO_CHAR (L_TOTAL_COUNT, 'fm999,999,999,990') || CHR(9) || TO_CHAR (L_ROW_COUNT*1.0/L_TOTAL_COUNT*100.0, 'fm999,999,999,990'));
          
          EXECUTE IMMEDIATE    'SELECT COUNT(*) FROM ' || X.OWNER || '.' || X.TABLE_NAME || ' WHERE ' || X.TABLE_NAME || '.' || X.COLUMN_NAME || ' < 0' INTO L_ROW_COUNT;
          DBMS_OUTPUT.PUT_LINE (COL || 'NEGATIVE' || CHR(9) || TO_CHAR (L_ROW_COUNT, 'fm999,999,999,990')|| CHR(9) || TO_CHAR (L_TOTAL_COUNT, 'fm999,999,999,990') || CHR(9) || TO_CHAR (L_ROW_COUNT*1.0/L_TOTAL_COUNT*100.0, 'fm999,999,999,990'));
        END IF;
     END LOOP;
  END LOOP;
END;

Basic Mudras

Gyan Mudra – Mudra of Knowledge

Method: Touch the tip of the thumb to the tip of the index finger, with the other three fingers stretched out.

Specialty: A mudra of knowledge, it enhances the knowledge. The tip of thumb correlates to the pituitary and endocrine glands. When you press these centers with the index finger the two glands work actively.

Time duration: No particular time duration for this mudra. You can practice while sitting, standing or lying on a bed whenever and wherever you have time.

Benefits: Increases memory power and sharpens the brain, Enhances concentration and prevents insomnia, when practiced regularly, it will relieve psychological disorders like Hysteria, Anger and Depression

Prithvi Mudra -Mudra of Earth

Method: Tip of the ring finger touches the tip of the thumb with the other three fingers stretched out.

Specialty: Reduces all physical weaknesses.

Time duration: Has no particular time duration. Practice it any time.

Benefits: Helps to increase weight for weak life-force people, Improves the complexion of skin and makes the skin glow, Promotes body functionality

Varuna Mudra – Mudra of Water

Method: Tip of little finger touches the tip of thumb with the other three fingers stretched out.

Specialty: Balances the water content and prevents all diseases which come due to lack of water.

Time duration: Has no specific time duration.

Benefits: Retains clarity in blood by balancing water content in the body

Prevents the pain of Gastroenteritis and Muscle Shrinkage

Vayu Mudra – Mudra of Air

Method: Keep the index finger on the base of the thumb and press with the thumb keeping the other three fingers straight.

Specialty: Prevents diseases that occur due to imbalances in the air.

Time duration: The practice of this mudra for 45 minutes reduces the severity of disease in 12 to 24 hours. For better results practice for 40-60 days.

Benefits: Relieves Rheumatism, Arthritis, Gout, Parkinson’s disease and paralysis without any medicine, Useful for Cervical Spondilytis, paralysis of the face, and pressure on nerves in neck, Relieves gas pressure in the stomach

Shunya Mudra – Mudra of Emptiness

Method: Keep the middle finger at the mount of Venus and press with the thumb.

Specialty: Reduces the dullness in the body.

Time duration: One can practice this for 40 to 60 minutes daily until relieved.

Benefits: Relieves an earache within 4 or 5 minutes, Useful for the deaf and mentally challenged.

Surya Mudra – Mudra of the Sun

Method: Bend the ring finger and press it with thumb.

Specialty: Correlates to the thyroid gland.

Time duration: Practice twice daily for 5 to 15 minutes.

Benefits: Reduces cholesterol in body and helps in reducing weight

Reduces anxiety, Corrects or relieves indigestion

Prana Mudra – Mudra of Life

Method: Bend ring finger and little finger and touch the tip of thumb with their tips keeping the remaining two fingers stretched.

Specialty: As it is the mudra of life, this improves the life force. The weak become strong. Reduces clumps in blood vessels. Practiced regularly this promotes physical activity.

Time duration: No specific time duration. One can practice it any time.

Benefits: Improves immunity, Improves the power of the eyes and reduces eye related issues, Relieves vitamin deficiency and fatigue

Apana Mudra – Mudra of Digestion

Method: The tips of middle finger and ring finger touch the tip of thumb while the other two fingers are stretched out.

Specialty: Plays an important role in our health as it regulates the excretory system. Note: Your health and wellness is absolutely dependant on the rapid removal of systemic waste. Toxicity can be mitigated if removed from the body rapidly.

Time duration: Practice daily for 45 minutes, but practicing for longer period yields more benefits.

Benefits: Regulates diabetes, Relieves constipation and piles, Helps establish regular excretion of solid waste

Apana Vayu Mudra – Mudra of the Heart

Method: The tips of the middle finger and ring finger touch the tip of thumb, while the index finger touches the base of thumb and the little finger is stretched out.

Specialty: Benefits the heart and works like an injection in the reduction of heart attack. Reduces the gas content in body.

Time duration: Practice as many times as you can. Heart patients and BP patients can practice twice for 15 minutes daily for better results.

Benefits: Strengthens the heart and regularizes palpitation, Regulates excretory system, Relieves gastric issues

Linga Mudra – Mudra of Heat

Method: Interlock the fingers of both hands and keep the thumb of the left hand vertically straight and encircle it with the thumb and the index finger of the right hand.

Specialty: Generates heat in the body. Take milk, ghee, more water and fruit juices in addition to the practice of this mudra for many benefits.

Time duration: Practice any time. But do not over-practice as it produces heat in the body. It can cause excessive sweating even in winter.

Benefits: Stops production of phlegm and gives power to lungs, Relieves severe cold and bronchial infections, Invigorates the body

Thumb – Fire; Index – Air; Middle – Ether or Space; Ring – Earth; Pinky -Water.

Can be performed at any time in any way, sitting, standing, walking or travelling. Maximum benefit is by doing it in sitting posture, especially in sukhasana (easy pose), vajrasana, or padmasana.

In the initial phase mudras should be performed for at least 10 minutes and then can be extended to 30 minutes to 1 hour. It is not necessary to leave all your normal work for 1 hour to perform mudras as it can be done at any time, any place, and in any posture, even if you can’t perform all at one stretch you can practice in 2-3 installments.

FOR EYES:

Vayu Mudra prevents heavy eyelids – 3 times a day before meals for 15 min.

Varuna Mudra for burning, dry eyes – Sit (palms on knees) and practice for 3x a day, 10-15 min

Gyan Mudra for weak eyesight and retina – 3x a day 15 min

Surya or Prithvi Mudra (Cataract) – 3x at least 10 min, seated facing east

Jala Samhar Mudra (excess tears, phlegm)- Press down on little finger, 2-3x – 15 times per session

MaryEllen Tribby: The Success Indicator

The Success Factor Indicator

Successful People

  • Have a sense of gratitude
  • Forgive others
  • Accept responsibility for their failures
  • Compliment
  • Read everyday
  • Keep a journal
  • Talk about ideas
  • Want others to succeed
  • Share information and data
  • Keep a “to-be” list
  • Exude joy
  • Keep a “to-do/project” list
  • Set goals and develop life plans
  • Embrace change
  • Give other people credit for their victories
  • Operate from a transformational perspective

Unsuccessful People

  • Have a sense of entitlement
  • Hold a grudge
  • Blame others for their failures
  • Criticize
  • Watch TV everyday
  • Say they keep a journal but really don’t
  • Talk about people
  • Secretly hope others fail
  • Horde information and data
  • Don’t know what they want to be
  • Exude anger
  • Fly by their seat of their pants
  • Never set goals
  • Think they know it all
  • Fear change
  • Take all the credit of their victories
  • Operate from a transactional perspective

If you are ready kick up the success meter a bit, make a conscious effort to eliminate the traits on the right hand side of the chart above.

via MaryEllen Tribby: The Success Indicator.

Granting Group permission to Windows Services

NT windows groups can be given permission to windows services using the SubInACL[1] utility.
 
Example:
subinacl  /service “service” /grant=”Domain Admins

Notifying and Renewing Web sessions without AJAX

Someone who tried to implement Notifying and Renewing Web sessions using AJAX using the Master pages would have learn’t that master pages doesn’t PageMethods and javascript will throw an error something like this

PageMethods is undefined

To overcome this, an easier way to retain the session is to simply use the server resource like calling an image in the server. So instead of using

PageMethods.RefreshSession(); //make the server call

we can simple do this

function triggerRefresh() {
var myImg = document.getElementById("imgDummy");
if (myImg) myImg.src = myImg.src.replace(/\?.*$/, '?' + Math.random());
}

which is to simply have an <img> tag. And assign a source to the image in my loadform() function like this:

            var myImg = document.getElementById("imgDummy")
            myImg.src = document.url + "?";

and so my complete script in the .aspx page will become like this:


            <!-- script added to implement session timeout logic
    Logic:  1.  If no keys were pressed by the user for (session time out period - 1) minutes, an alert will be
                shown one minute before the session time out, if the user responds to the message, the session
                refreshes, else the session expires eventually
            2.  If keys were pressed by the user before (session time out period - 1) minutes, then the session
                automatically refreshes.
        -->
    <script type="text/javascript" >
        var actionFlag; //flag to monitor user key actions
        var timeoutPeriod; //holder for the server session timeout period (in minutes)
        var int; //variable for Interval
        var seconds = 60 //constant
        var nanoseconds = 1000 //constant

        //Uses the PageMethods to get the session timeout from the server
        function GetSessionTimeout() {
            //PageMethods.GetTimeoutPeriod(function (result) { timeoutPeriod = result; }); //todo: make it synchronous instead of async
            timeoutPeriod = parseInt(document.forms[0].inttop.value);  //session time out is available in a hidden field

        }

        //Calculates the retrigger time
        function getRetriggerTime() {
            if (timeoutPeriod == 1) return timeoutPeriod;
            return timeoutPeriod - 1;
        }

        //function handler for form's load event
        function loadForm(oEvent) {
            actionFlag = false; //reset the flag
            GetSessionTimeout(); //get the session timeout
            int = setInterval("notifyTimeOut()", getRetriggerTime() * seconds * nanoseconds); //renew the interval
            var myImg = document.getElementById("imgDummy")
            myImg.src = document.url + "?";
        }

        //function that resets the actionFlag
        function keyDown(oEvent) {
            if (actionFlag) return; //skip if the flag is already true
            switch (oEvent.keyCode) {
                case 17: //skip if key is ctrl
                case 18: //skip if key is alt
                    return;
                    break;
            }
            //else
            actionFlag = true;
        }

        //function that refreshes the session without an alert
        function autoRefreshSession() {
            triggerRefresh();
            int = clearInterval(int); //clear the interval, todo: check if this line is required
            int = setInterval("notifyTimeOut()", getRetriggerTime() * seconds * nanoseconds);  //renew the interval
            alert('auto refresh');
        }

        //function that executes when the user triggers to refresh the session
        function manualRefreshSession() {
            triggerRefresh();
            int = setInterval("notifyTimeOut()", getRetriggerTime() * seconds * nanoseconds); //renew the interval
        }

        function triggerRefresh() {
            var myImg = document.getElementById("imgDummy");
            if (myImg) myImg.src = myImg.src.replace(/\?.*$/, '?' + Math.random());
        }
        //function thats executed at the scheduled time interval
        function notifyTimeOut() {
            int = clearInterval(int); //clear the interval, todo: check if this line is required
            if (actionFlag == true) autoRefreshSession(); //if action was taken, perform auto refresh
            else {
                alert('Your Session is about to expire! Click OK to refresh your session!'); //show alert to the user
                manualRefreshSession(); //if control returns refresh session, if the session has already expired a new session will start
            }
            actionFlag = false; //reset the flag
        }

    </script>

and I don’t have a single line of server code and most importantly no use for the ajax <scriptmanager>. Thanks to the author of Keep session alive with Javascript

Moved to WordPress

Ah ah, I moved from BlogEngine.net to WordPress. So I won”t be posting my blogs anymore to http://prabhuram.com/blogs. Here after all my blogs will be available at http://prabhuram.com/articles. I have to find out if there is a way to change the address to blogs instead of articles.

B&N Sale

The Barnes and Noble 'After Holiday Sale' is still on. 

Send a $10 Donation by Texting ‘Haiti’ to 90999

Wanted to support Haiti Relief through American Red Cross, start by sending a $10 Donation by Texting ‘Haiti’ to 90999

More here.