Sunday, June 15, 2014

Table functions and cross apply

So recently at work I came across a table that had a column that contained multiple ID's that were comma separated.
It looked something like the students column below.


Now luckily we already have a table function built that will parse this out for me. Similar to here. All I have to do is pass in the string and the delimiter and it will spit out a table.


The question is how to use it.

In it's simplest form it works like below.



So I could pull out the value for each record, pass it into a variable, pass that variable into the function, record the results into a separate table so I know what I had, and then have it loop through until all were done.
It would look like something below, except that you would have to have this loop through until you got through everything. 


Now that would be a pain to setup, and doesn't sound very efficient. There has to be a better way. Especially if I wanted to join these values onto another table. That is where Cross Apply comes in handy.

Cross Apply works similarly to a Cross Join. Except it is made to play nice with table functions.
Instead of passing in a set string or a string variable, you simply join the two tables together and pass in the column name you want split.

You can see below that my table named class has a list of student numbers. It split out each separated value into the column called Value.







What makes this handy is that I can now join this onto other tables. In this case I created a table called students. If I join them together I get results like below.



The above method is much simpler than trying to loop through and do one at a time. It works like a standard join, except that you use Cross Apply instead of Join. And of course you don't enter the 'ON' part of the join, just enter the column name into the table function.



Tuesday, December 17, 2013

Windows commands

At work I sometimes have to move data from location to location. The simplest way is to use the copy and move steps.

Copy Command

For instance below, I copied a file called "moveTest1.txt" to the subdirectory called Backups. 










That works if you want to do one file, but what about multiple files? What if I want to copy everything that begins with MoveTest?


Then we can use * as a wildcard. Here you can see I have two files that begin with MoveTest.
I use the command "copy moveTest* \backups\MoveTest*"
Then when I check the backups directory I see that both files are now showing up there.



Move Command

The Move Command works similarly. But there is a difference. You cannot specify the new filename. It will just use the existing name. Otherwise you get an error like below.

Once I removed the destination filename it worked.


So why would we ever want to specify the destination filename? The copy step also works without it.
Try the below command.
















Notice how the filename changed from moveTest to TestMove? This only really works when the destination filename is the same length or shorter that the original filename. So I wouldn't be able to change it to ThisFileWasMoved1.txt and ThisFileWasMoved2.txt.


Combining files together

We can also combine files this way. Lets say you have a bunch of small .csv or text files that you want to combine into one big file.
In the below situation, I combined test1.csv and test2.csv into one file called TestCombined.csv. 
 

If you check the files themselves (using the type command here), you can see how the files were combined.




Dates and Substrings

I recently came across an issue where we were trying to import a file, but our system was hard coded to look for a specific filename. The vendor was not able to change it on their end without charging us a lot of money. It would take us lots of money and time to change it on our end also. The name of the file was supposed to have a combination of the date and time in it.
We can use the copy command to do some of this.



So what happened here? I moved the file, but the filename was changed from MoveTest1.txt to testResults12172013182930.txt
So what do these numbers mean. If you format it a bit you get:
12-17-2013 18:29:30
So you can see I did this on December 17th, at 6:29 pm.

The first time I did this, I searched google until I found someone who wanted something similar to me.  I took their solution and adjusted their code to meet my specifications. The individual commands were foreign to me though. So I did a little research. Of course if I am wrong let me know.I have just been figuring this out on my own.

The first this is that %date% and %time% are environmental variables. You can see that in the below screenshot.











We are using the colon and tilde commands. %date:~4,2% basically means "Take the %date% environmental variable, start at the 5th character(it starts at 0, not 1), and go forward and use the next 2 characters". This is somewhat similar to how a substring in SQL works.

Typing set /? will also bring up some documentation about how it works.


This person does a better job than me of explaining it, but this is where I got some of my information from.




Find Command

I don't use this one quite as often, but I did find it useful when working with log files that were too big for notepad.

Lets look at the file below that has the below text.










If I wanted to only return results that contain the word hello, I would do:

This returns every row where the word hello exists in it.


Where this can come in handy is when you have it output to a new file like below.



A personal example was how one time I needed to look through some log files that had several million rows of data. This is too large for excel or notepad. So I was having trouble even opening up the file. However I knew that each row had the particular date and time that the entry was created. I was able to use the find command to create a new file that only contained data for the particular minute I was looking for. This drastically cut down on the amount of data I had to look through, and also allowed me to open it up in my favorite text editor (Notepad++ of course).

Powershell - Find and Replace

I have to say this is probably the first time I have really used PowerShell. All I really knew is that it was blue, and I could do the same command prompt commands that I already knew. 
One time I came across a situation where we not only needed to move a file to a new location, but also do a find and replace for the data inside of it.

Lets look at the test1.csv file I just created. 
 






It has column headers, and two rows of data. How would I change the clientId of 1, to 2?

I used this command. 
"Get-Content test1.csv | ForEach-Object { $_ -replace "1,", "2," } | Set-Content test1Updated.csv"

You can see that when I view the updated file, all of the 1's were replaced with a 2. You can combine this with some of the above steps to also copy and rename files at the same time.

While that works well enough for me, I like knowing how and why something works. That knowledge makes it much easier to apply it to new situations I am put in.
I am a newbie to powershell so forgive me if my terminology is off. 

"Get-Content" just selects the test1.csv file (or a list of files if we used wildcards).
"ForEach-Object {....}" is a basically a FOR statement that loops through the array of objects or files selected. In this case there is only one file that was selected. 
"$_" stands for the current object that is being referenced in the FOR statement.
"-replace " is the command we are running on our object.
"1,","2," means we are replacing 1, with 2,. 
"Set-Content" means that we will output to test1Updated.csv
The pipe characters are a way of separating the commands. So we first call Get-Content, then we run a ForEach-Object command on the data we just received, and then we do the Set-Content command on the data returned from the ForEachObject command. 
This link was very helpful for understanding how piping works.


Why?

So why even bother with all of this? It probably takes me less time to copy paste a file using Windows explorer than it would to do it in powershell or the command prompt window. Same thing with renaming files, or potentially doing a find and replace. So why bother doing this when doing it in the GUI is faster?

For me, the reason is automation. If I have 100 different vendors sending me files several times a day, and I need to copy, rename, and update each of those, it would take forever to do that by hand. I have much better things to do. However I could put these into batch files, windows scheduled tasks, or for me sometimes even as part of a SQL job and have them run automatically. That is the reason why I have started to learn some of this. It saves me time, and makes me more flexible in the situations I encounter.