Excel: Removing duplicates but keeping blank spaces

So you have finally gotten the 65,000 row spreadsheet from your boss that absolutely positively MUST be done before next weeks big meeting--but it has duplicate numbers in one column. You need every row--but you only want one of those column values to show for that row. What to do?

Example given data

John Project Manager
John Director of Human Resources
John Technology Supervisor

Desired data

John Project Manager
        Director of Human Resources
        Technology Supervisor

You could either manually erase all the extra column values and replace them with blank spaces--OR you could get create with Excel and run a formula to sift out all the extraneous data.

Copy your column data into another worksheet and put it into column A. In column B, write this formula:

=IF(COUNTIF(A$1:A1,A1)>1,"",A1)

You will see the first value in column B populate. Stretch this column all the way down and paste the contents into Wordpad or Notepad. Why not paste it back into your original Excel worksheet, you ask? Well--Microsoft Excel has this nasty habit of wanting to reference an equation instead of showing the equation results when you copy it into Excel. The work a round is to copy the data elsewhere first--then copy the data from Notepad or Wordpad BACK into your original excel, replacing your duplicated column.

It takes some practice--but is far better than the alternative manual erase method.

Finding the modification date of an Oracle table

So a change has been applied to your Oracle database by your somewhat secretive database administrator or vendor--and you would like to know which tables/views to keep an eye on--but how?

Never fear... there is a query for that...

select * from all_objects

You are looking for a column called last_ddl_time. THAT is the last date that the item was modified. The created column is... well... the day the item was created. The timestamp column is the long form of this creation date

To see all the tables that have been modified simply type:
select * from all_objects where object_type='TABLE'

Similarly, to see all the views the query would be
select * from all_objects where object_type='TABLE'

And if we wanted to narrow the query down further to see the exact changes to the database from our database administrator, we might try something like this:

select * from all_objects where last_ddl>=(sysdate-1)

Trust me--copy this post out and save it... you will end up needing it...

Converting a minutes to hours, minutes,seconds format

So you have finally reached the point when 145.8 minutes simply will not work for your boss... But how to convert to Hours, Minutes, Seconds format in Crystal Reports?

Everyone knows how to convert minutes to days and seconds--but telling your program is an different matter entirely. The secret is the Mod operator. When we convert from minutes to hours minutes and seconds, the first thing we do is divide our total number of minutes by 60 and shed the remainder as minutes. This is done via the Mod operator. It returns the remainder after division. So 144/60 = hours and 144 Mod 60=minutes and seconds is (144 mod 60)/60... In Crystal Reports, we have to multiply the number of seconds time 100 to avoid unsightly decimal points.

The code is fairly straightforward once you get the hang of it:

Pseudo-Code:
If you code is large enough to contain hours
then show the number without decimal places as hours
show the number of minutes without decimals
show the seconds
Otherwise, if your total does not contain hours
then the total number of seconds is found after the decimal place in the minutes column

Code:
if({your_total}/60>1) then                          
  cstr(({your_total}/60,0,'')+' Hours, '         
  + cstr({your_total} mod 60,0,'')+' Minutes' 
  +cstr(({your_total}mod 60,0,'')/60*100,0,'') + 'Seconds'   

else                                                      
  cstr({your_total}mod 60,0,'')+'Minutes '+cstr((({your_total}mod 60)-{your_total})*100,0,'')+'Seconds'

And that is the basic idea. You will need to make modifications if you need to break down by day/month/year... but this should get you started.

The secret to date subtraction in Oracle

Psst... Hey you--yes, you! The one aimlessly searching Google trying to find a way to subtract two Oracle datetime fields for your query or for connecting to Crystal Reports. The code is actually quite straight forward--I cannot believe so many websites are obtuse about it.

     trunc(nvl(end_date,sysdate),-begin_date) will return the difference in days

     trunc(nvl(end_date,sysdate),-begin_date)*24 will return the difference in hours

     trunc(nvl(end_date,sysdate),-begin_date)/356 will return the difference in years

It is that simple. If you try to wait until after the query and use Crystals Datediff("d",end,beginning) you are going to be in for a surprise. DateDiff doesn't really work well...

A simple C# XML to CVS converter

The purpose of this post is to create a simple converter from an XML file to a CVS file. This program was originally created to convert a DotNetNuke log file to a CVS file to be read by Crystal Reports. It accomplishes this by first scanning the XML file and removing any unwanted XML code, then saving as a CVS file named "mytest.cvs" in the C: drive.

Basically, this program is an automated text editor. But, since I use this code and variations of it all the time for everything from HTML stripping to XML stripping, I thought it might be useful to share.

The first step is to open up Microsoft Visual Studio. I use the Express 2010 version. From the toolbar (should be on the left side of your screen if you are using Microsoft Visual Studio), select a RichTextBox object, an OpenFileDialog and a button to start with. Drag them onto your form.

This first button is going to allow users to select the XML file to open. (Actually, any text file will open--but for simplicity I am going to stick with XML for the moment). I named my button "Open File".

Double click on the "Open File" button and paste in the following code:

        //fetch the XML file and show it in the rich text box
        private void button1_Click(object sender, EventArgs e)
        {
            //open the file structure
            using (System.Windows.Forms.OpenFileDialog ofd = new OpenFileDialog())
            {
                //only choose text files
                ofd.Filter = "All files (*.*)|*.*";
                ofd.ShowDialog();

                //write text file to screen
                this.richTextBox1.Text = System.IO.File.ReadAllText(ofd.FileName);

            }
        }
 Notice the "All files(*.*)|*.*" statement. If you were to change it to "All files(*.*)|*.txt" you would be restricting the user to files to either select only text files or all files. You can also create your own proprietary file type extension and restrict the user to only opening those.

Also, by double clicking on the richtextbox, you could add some fancy text highlighting on certain terms (just like in the Notebook Editor). To do this, you would simply paste something like:

        private void richTextBox1_TextChanged(object sender, EventArgs e)
        {
            if (richTextBox1.SelectionFont != null)
            {
                is.CheckKeyword("phrase to find", Color.Red, 0);
                is.CheckKeyword("some other phrase", Color.Green, 0);
                is.CheckKeyword("yet another phrase", Color.BlueViolet, 0);
            }
        }

The function "CheckKeyword" would then be coded as follows:

        private void CheckKeyword(string word, Color color, int startIndex)
        {
            if (this.richTextBox1.Text.Contains(word))
            {
                int index = -1; int selectStart = this.richTextBox1.SelectionStart;
                while ((index = this.richTextBox1.Text.IndexOf(word, (index + 1))) != -1)
                {
                    this.richTextBox1.Select((index + startIndex), word.Length);
                    this.richTextBox1.SelectionColor = color;
                    this.richTextBox1.Select(selectStart, 0);
                    this.richTextBox1.SelectionColor = Color.Black;
                }
            }

        }

But I digress--what we want to do is search the richtextbox for XML code and strip that out. We do this by inserting another button into our project from the toolbar. Click on the button and type something like this. Please keep in mind that button4 is the name of my button in my project--the name of your button may be completely different. All you really care about is the ConvertContentsToString function call :

        private void button4_Click(object sender, EventArgs e)
        {
            ConvertContentsToString(richTextBox1);

        }


Now we need to be able to define the function ConvertContentsToString. The code is as follows:
void ConvertContentsToString(RichTextBox rtb)
        {

            string RTBString = rtb.Rtf;
            string quotes = "\"";

            //if there are any quotes in the text box, change to commas
            //remove all XML key words
            if (this.richTextBox1.Text.Contains(quotes))
            {
                RTBString = RTBString.Replace("\"", ",");
                RTBString = RTBString.Replace("=", "");
                RTBString = RTBString.Replace(">", "");
                RTBString = RTBString.Replace("<", "");
                RTBString = RTBString.Replace("/", "");
                RTBString = RTBString.Replace("LogEntries", "");
                RTBString = RTBString.Replace("log", "");
                RTBString = RTBString.Replace("LogFileID", "");
                RTBString = RTBString.Replace("LogTypeKey", "");
                RTBString = RTBString.Replace("LogUserID", "");
                RTBString = RTBString.Replace("PropertyName", "");
                RTBString = RTBString.Replace("IP", "From Computer: ");
                RTBString = RTBString.Replace("LogProperty", "");
                RTBString = RTBString.Replace("par", "");
                RTBString = RTBString.Replace("LogGUID", "");
                RTBString = RTBString.Replace("\\", "");
                RTBString = RTBString.Replace("LogServerName", "");
                RTBString = RTBString.Replace("LogConfigID", "");
                RTBString = RTBString.Replace("True", "");
                RTBString = RTBString.Replace("False", "");
                RTBString = RTBString.Replace("ByPassBuffering", "");
                RTBString = RTBString.Replace("LogCreateNumber", "");
                RTBString = RTBString.Replace("LogPortalName", "");
                RTBString = RTBString.Replace("LogPortalID", "");
                RTBString = RTBString.Replace(", ,", ",");
                RTBString = RTBString.Replace("PropertyValue", "");
                RTBString = RTBString.Replace("LogUserName", "Name: ");
                RTBString = RTBString.Replace("LOGIN_SUCCESS", "");
                RTBString = RTBString.Replace("LogProperties", "");
                RTBString = RTBString.Replace("SAFWEB01", "");
                RTBString = RTBString.Replace("LogCreateDateNum", "");
                RTBString = RTBString.Replace("BypassBuffering", "");
                RTBString = RTBString.Replace("LogCreateDate", "");

                //richtextbox contents have been stripped of dat
                //write them to a csv file
                File.WriteAllText("C:/mytest.csv", RTBString);
              
            }
           

           
        }


This function is the bulk of the work. Please modify as you see fit. The complete code for the XML to CVS convert is listed below. Cheers and happy coding!


//Created by: Olivia E Terrell
//Description: A simple log file XML to CVS and data stripper example
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions;


using System.IO;
using System.Drawing.Text;

namespace Translate_my_XML
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        //see what is currently living in the rich text box
        private void richTextBox1_TextChanged(object sender, EventArgs e)
        {
            if (richTextBox1.SelectionFont != null)
            {
                //is.CheckKeyword("LOGIN_SUCCESS", Color.Red, 0);
                //is.CheckKeyword("LogUserName=", Color.Green, 0);
                //is.CheckKeyword("extern", Color.BlueViolet, 0);
            }
        }


        //check to see if we have any keywords that signal the beginning of data collection
        private void CheckKeyword(string word, Color color, int startIndex)
        {
            if (this.richTextBox1.Text.Contains(word))
            {
                int index = -1; int selectStart = this.richTextBox1.SelectionStart;
                while ((index = this.richTextBox1.Text.IndexOf(word, (index + 1))) != -1)
                {
                    this.richTextBox1.Select((index + startIndex), word.Length);
                    this.richTextBox1.SelectionColor = color;
                    this.richTextBox1.Select(selectStart, 0);
                    this.richTextBox1.SelectionColor = Color.Black;
                }
            }

        }

  
        //convert the richtextbox to a string and search it
        void ConvertContentsToString(RichTextBox rtb)
        {

            string RTBString = rtb.Rtf;
            string quotes = "\"";

            //if there are any quotes in the text box, change to commas
            //remove all XML key words
            if (this.richTextBox1.Text.Contains(quotes))
            {
                RTBString = RTBString.Replace("\"", ",");
                RTBString = RTBString.Replace("=", "");
                RTBString = RTBString.Replace(">", "");
                RTBString = RTBString.Replace("<", "");
                RTBString = RTBString.Replace("/", "");
                RTBString = RTBString.Replace("LogEntries", "");
                RTBString = RTBString.Replace("log", "");
                RTBString = RTBString.Replace("LogFileID", "");
                RTBString = RTBString.Replace("LogTypeKey", "");
                RTBString = RTBString.Replace("LogUserID", "");
                RTBString = RTBString.Replace("PropertyName", "");
                RTBString = RTBString.Replace("IP", "From Computer: ");
                RTBString = RTBString.Replace("LogProperty", "");
                RTBString = RTBString.Replace("par", "");
                RTBString = RTBString.Replace("LogGUID", "");
                RTBString = RTBString.Replace("\\", "");
                RTBString = RTBString.Replace("LogServerName", "");
                RTBString = RTBString.Replace("LogConfigID", "");
                RTBString = RTBString.Replace("True", "");
                RTBString = RTBString.Replace("False", "");
                RTBString = RTBString.Replace("ByPassBuffering", "");
                RTBString = RTBString.Replace("LogCreateNumber", "");
                RTBString = RTBString.Replace("LogPortalName", "");
                RTBString = RTBString.Replace("LogPortalID", "");
                RTBString = RTBString.Replace(", ,", ",");
                RTBString = RTBString.Replace("PropertyValue", "");
                RTBString = RTBString.Replace("LogUserName", "Name: ");
                RTBString = RTBString.Replace("LOGIN_SUCCESS", "");
                RTBString = RTBString.Replace("LogProperties", "");
                RTBString = RTBString.Replace("SAFWEB01", "");
                RTBString = RTBString.Replace("LogCreateDateNum", "");
                RTBString = RTBString.Replace("BypassBuffering", "");
                RTBString = RTBString.Replace("LogCreateDate", "");

                //richtextbox contents have been stripped of dat
                //write them to a csv file
                File.WriteAllText("C:/mytest.csv", RTBString);
              
            }
           

           
        }

        //fetch the XML file and show it in the rich text box
        private void button1_Click(object sender, EventArgs e)
        {
            //open the file structure
            using (System.Windows.Forms.OpenFileDialog ofd = new OpenFileDialog())
            {
                //only choose text files
                ofd.Filter = "All files (*.*)|*.*";
                ofd.ShowDialog();

                //write text file to screen
                this.richTextBox1.Text = System.IO.File.ReadAllText(ofd.FileName);

            }
        }

        //clear the data
        private void button2_Click(object sender, EventArgs e)
        {
            richTextBox1.Clear();
        }

        //save the stripped data as a new file
        private void button3_Click(object sender, EventArgs e)
        {
            //ask for the new file name
            //saveFile1.DefaultExt = "*.txt";
            SaveFileDialog saveFile1 = new SaveFileDialog();
            saveFile1.DefaultExt = "*.rtf";
            saveFile1.Filter = "RTF Files|*.rtf|DSD Files|*.dsd";

            // Determine if the user selected a file name
            if (saveFile1.ShowDialog() == System.Windows.Forms.DialogResult.OK &&
               saveFile1.FileName.Length > 0)
            {
                // Save the contents of the RichTextBox into the file.
                richTextBox1.SaveFile(saveFile1.FileName, RichTextBoxStreamType.PlainText);
            }
        }

        //search the file for this text
        private void button4_Click(object sender, EventArgs e)
        {
            ConvertContentsToString(richTextBox1);

        }


    }
}

Crystal Reports String to Date Conversion

So, I have gotten this question a lot lately regarding Crystal Reports and converting strings to dates. Conversion from date to string is relatively easy--ToText(command.your_item, 'dd-MM-yyyy') usually does the trick, but a lot of confusion happens when converting in the other direction.

Those date functions you see up in your Crystal Reports function? Ya... those aren't really going to help you much... You are going to have to write your OWN formula. How? By simple counting. In my example, string_date looks something like this: "05/07/2012 05:05.0606" in other words MM/DD/YYYY mm:HH.ssss.' The first step, is to write out your string so you can see what you are working with. Then assign variables to each section of your string. To grab the year, I could count either 4 places from the 6th position of the string or add 2000 to the last 2 positions in the string beginning at place 9. It alls comes back to counting. Remember that empty spaces in your string count as a position, as do '/' or '-' symbols.

year=2000+2 places after the 9th position
month=the first 2 numbers
day=2 places beginning on the 4 position
... and so on
This is the formula I use--feel free to copy/paste/modify until you get the hang of it. Naturally, depending on what your string looks like, the numbers used for position by tonumber() may vary  ;)

numbervar y := 2000 + tonumber(mid({Command.string_date},9,2));
numbervar m := tonumber(mid({Command.string_date},1,2));
numbervar d := tonumber(mid({Command.string_date},4,2));
numbervar h := tonumber(mid({Command.string_date},12,2));
numbervar n := tonumber(mid({Command.string_date},15,2));
numbervar s := tonumber(mid({Command.string_date},18,2));

//datetime(y,m,d,h,s)

date(Y,M,D)

Hiding music in an image

So you have a song that you really would like to keep on your thumb-drive--but the thought of losing your thumb-drive and having everyone at the office know that you listen to "I'm a Barbie Girl" is just unthinkable. Never fear, there is a way to hide your song inside a picture!

Simply open a windows command prompt (go to start, run, type 'cmd'). You will need to have your song and your picture residing in the same folder. Navigate to this folder and type the following:

C:\Documents and Settings\God\Desktop>copy /b James_painting.jpg + Lover.mp3 out
put.jpg
For this example, my music file and image are located on the Desktop. Since the image file "James_painting.jpg" is what I want everyone to see, I put it first. The Lover.mp3 is my embedded song and the "output.jpg" is the file generated on my desktop.

If I click on Output.jpg and the image James_painting.jpg will appear. If I change the extension to .mp3, my song "Lover.mp3" will begin playing...

Yes--embedding is really just that easy--but for true secrecy, you will need to learn some decoding...