Tuesday, October 18, 2011
Many times I need to merge two separate datasets that have a common key column – for example:
dataset 1:
ID, Name, Weight, Height
dataset 2:
ID, Address1, Address2, Phone1, Phone2
I can’t just concat the data together, instead I need the merge to happen around the ID key, so that each ID only has a single row in the result. So, of course I wrote a powershell script to do this for me. Take a look at it below. I called it “Merge-TsvFiles”, but it takes a delimiter as one of its paramters, so you could use this for CSV or whatever.
If you have any thoughts as to how this can be further improved code-wise or speed-wise, please comment!
function Merge-TsvFiles {
param(
[string] $file1 = $(throw "file1 required."),
[string] $file2 = $(throw "file2 required."),
[string] $delimiter = "`t",
[string] $key = "ProfileID"
)
$data1 = import-csv -path $file1 -delimiter $delimiter
$data2 = import-csv -path $file2 -delimiter $delimiter
#write-host "$data1.Length total data rows, and $data2.Length total merge data rows."
$i = 0
#grab the new column names
$newcols = $data2 | gm -MemberType NoteProperty
Foreach ($datarow in $data1)
{
$i++
$data2 | where-object {$_.$($key) -eq $data1.$($key)} | %{
Foreach ($col in $newcols) {
if ($col.Name -ne $key) {
Add-Member -inputObject $datarow -name $col.Name -value $_.$($col.Name) -MemberType NoteProperty;
}
}
}
Write-Progress -activity "Merging data" -status "Merged $i of $($data1.Length) rows..." -percentComplete (($i / $data1.length) * 100)
}
write-host "Writing output..."
$data1 | export-csv "output.txt" -delimiter "`t"
}
Wednesday, August 31, 2011
First I found this blog post – which worked but only after some tinkering. There is a comment there that describes a better (simpler) solution, IMO.
Step 1: Create the following macro, which simply toggles (twice) the existing TrackActivityinSolutionExplorer command – this is the same command that happens if you turn on solution explorer tracking in Visual Studio options, except here we’re turning it on and then immediately off:
Public Sub SelectCurrentItemInSolutionExplorer()
DTE.ExecuteCommand("View.TrackActivityinSolutionExplorer")
DTE.ExecuteCommand("View.TrackActivityinSolutionExplorer")
End Sub
Step 2: Create a toolbar button to run this macro. To do this, right click on the toolbar and click customize. Click “New…” to create a new toolbar, I took the default name of “Custom 1”. Go to the “Commands” tab and click the “Toolbar” radio button, select your new toolbar from the dropdown list. Click “Add Command…”, select Macros from the categories list and select your new “SelectCurrentItemInSolutionExplorer” macro as the command. Click “OK'”, then you can “Modify Selection” to give it a custom name or image.
Sunday, June 19, 2011
If you would like to help me out, you can Tweet this story OR email Expedia and ask them to make things right for me and my wife.
This is the story of how Expedia made my new bride cry.
When Expedia followed up their failure with our honeymoon trip with a complete and total lack of acknowledgement of any responsibility for the problem and endless loops of explaining the issue over and over again - I swore that they would make it right. When they brought my new bride to tears, I got an immediate and endless supply of motivation. I hope you will help me make them make it right by posting our story on Twitter, Facebook, your blog, on Expedia itself, and when talking to your friends in person about their own travel plans. If you are considering using them now for an important trip - reconsider.
Short summary:
We arrived early for our honeymoon flight - but Expedia had made a mistake with the data they supplied to JetBlue and Emirates, which resulted in us not being able to check in (one leg of our trip was missing)! At the time of this post, three people (myself, my wife, and an exceptionally patient JetBlue employee named Mary) each spent hours on the phone with Expedia. I myself spent right at 3 hours (according to iPhone records), Lauren spent an hour and a half or so, and poor Mary was probably on the phone for a good 3.5 hours. This is after 5 hours total at the airport. If you add up our phone time, that is nearly 8 hours of phone time over a 5 hour period with little or no help, stall tactics (?), run-around, denial, shifting of blame, and holding.
Details below (times are approximate):
First, my wife and I were married on June 18th, the 3 year anniversary of our first date. She is awesome. She is the nicest person I have ever known, a ton of fun, absolutely beautiful in every way. Ok enough mushy - here are the dirty details.
June 19th, 2:30 AM - Early Check-in Attempt - we attempted to check-in for our flight online. Some sort of technology error on website, instructed to checkin at desk.
4:30 AM - Arrive at airport. Try to check-in at kiosk, get the same error. We got to the JetBlue desk at RDU International Airport, where Mary helped us. Mary discovered that the Expedia provided itinerary does not match the Expedia provided tickets. We are informed that when that happens American, JetBlue, and others that use the same software cannot check you in for the flight because. Why? Because the itinerary was missing a leg of our flight! Basically we were not shown in the system as definitely being able to make it home. Mary called Expedia and was put on hold by their automated system.
4:55 AM - Mary, myself, and my brand new bride all waited for about 25 minutes when finally I decided I would make a call myself on my iPhone while Mary was on the airport phone. In their automated system, I chose "make a new reservation", thinking they might answer a little more quickly than "customer service". Not surprisingly I was connected to an Expedia person within 1 minute. They informed me that they would have to forward me to a customer service specialist. I explained to them that we were already on hold for that and had been for nearly half an hour, that we were going on our honeymoon and that our flight would be leaving soon - could they please help us. "Yes, I will help you". I hand the phone to JetBlue Mary who explains the situation 3 or 4 times. Obviously I couldn't hear both ends of the conversation at this point, but the Expedia person explained what the problem was by stating exactly what Mary had just spent 15 minutes explaining. Mary calmly confirms that this is the problem, and asks Expedia to re-issue the itinerary. Expedia tells Mary that they'll have to transfer her to customer service. Mary asks for someone specific so that we get an answer this time, and goes on hold. Mary get's connected, explains the situation, and then Mary's connection gets terminated.
5:10 AM - Mary calls back to the Expedia automated system again, and we wait for about 5 minutes on hold this time before I pick up my iPhone and call Expedia again myself. Again I go to sales, a person picks up the phone in less than a minute. I explain the situation and let them know that we are now very close to missing our flight for our honeymoon, could they please help us. "Yes, I will help you". Again I give the phone to Mary who provides them with a call back number in case we get disconnected again and explains the situation again. More back and forth with Expedia doing nothing but repeating the same questions, Mary answering the questions with the same information she provided in the original explanation, and Expedia simply restating the problem. Mary again asks them to re-issue the itinerary, and explains that doing so will fix the problem. Expedia again repeats the problem instead of fixing it, and Mary's connection gets terminated.
5:20 AM - Mary again calls back to Expedia. My beautiful bride also calls on her own phone. At this point she is struggling to hold back her tears, stumbling through an explanation of all that has happened and that we are about to miss our flight. Please help us. "Yes, I will help". My beautiful bride's connection gets terminated. Ok, maybe this disconnection isn't an accident. We've now been disconnected 3 times on two different phones.
5:45 AM - I walk away and pleadingly beg a person to help me. They "escalate" the issue to "Rosy" (sp?) at Expedia. I go through the whole song and dance again with Rosy, who gives me the same treatment Mary was given. Rosy blames JetBlue for now having the correct data. Meanwhile Mary is on the phone with Emirates Air (the airline for the second leg of our trip), who agrees with JetBlue that Expedia's data isn't up to date. We are informed by two airport employees that issues like this with Expedia are not uncommon, and that the fix is simple. On the phone iwth Rosy, I ask her to re-issue the itinerary because we are about to miss our flight. She again explains the problem to me. At this point, I am standing at the window, pleading with Rosy to help us get to our honeymoon, watching our airplane. Then our airplane leaves without us.
6:03 AM - At this point we have missed our flight. Re-issuing the itinerary is no longer a solution. I ask Rosy to start from the beginning and work us up a new trip. She says that she cannot do that. She says that she needs to talk to JetBlue and Emirates and find out why we cannot check-in for our flight. I remind Rosy that our flight has already left - I just watched it taxi away - it no longer matters why (not to mention the fact that we already knew why, and have known why since 4:30 AM), and have known the solution since 4:30 AM. Rosy, can you please book a new trip? Yes, but it will cost $400. Excuse me? Now you can, but it will cost ME to fix your mistake? Rosy says that she can escalate the situation to her supervisor but that will take 1.5 hours.
6:15 AM - I told Rosy that if they had re-issued the itinerary as JetBlue asked (at 4:30 AM), my new wife and I might be on the airplane now instead of dealing with this on the phone and missing the beginning (and how much more?) of our honeymoon. Rosy said that it was not necessary to re-issue the itinerary. Out of curiosity, i asked Rosy if there was some financial burden on them to re-issue the itinerary. "No", said Rosy. I asked her if it was a large time burden on Expedia to re-issue the itinerary. "No", said Rosy. I directly asked Rosy: Why wouldn't Expedia have re-issued the itinerary when JetBlue asked? No answer. I asked Rosy: If you had re-issued the itinerary at 4:30, isn't it possible that I would be on that flight right now? She actually surprised me by answering "Yes" to that question. So I pointed out that it followed that Expedia was responsible for the fact that we missed out flight, and she immediately went into more about how the problem was with JetBlue - but now it was ALSO an Emirates Air problem as well. I tell Rosy to go ahead and escalate the issue again, and please call me back in that 1.5 hours (which how is about 1 hour and 10 minutes away).
6:30 AM - I start tweeting my frustration with iPhone. It's now pretty much impossible for us to make it to The Maldives by 3pm, which is the time at which we would need to arrive in order to be allowed service to the actual island where we are staying. Expedia has now given me the run-around for 2 hours, caused me to miss my flight, and worst of all caused my amazing new wife Lauren to miss our honeymoon. You think I was mad? No. Furious. Its ok to make mistakes - but to refuse to fix them and to ruin our honeymoon? No, not ok, Expedia. I swore right then that Expedia would make this right.
7:45 AM - JetBlue mary is still talking her tail off to other people in JetBlue and Emirates Air. Mary works it out so that if Expedia simply books a new trip, JetBlue and Emirates will both waive all the fees. Now we just have to convince Expedia to fix their mistake and get us on our way! Around this time Expedia Rosy calls me back! I inform her of the excellent work of JetBlue Mary - that JetBlue and Emirates both will waive the fees so Expedia can fix their mistake and get us going on our way. She says that she sees documentation of this in her system and that she needs to put me on hold "for 1 to 10 minutes" to talk to Emirates Air (why I'm not exactly sure). I say ok.
8:45 AM - After an hour on hold, Rosy comes on the line and asks me to hold more. I ask her to call me back.
9:35 AM - I put down the iPhone Twitter app and picks up the laptop. You think I made some noise with my iPhone? Heh
11:25 AM - Expedia follows me and sends a canned "We're sorry, DM us the details". If you look at their Twitter feed, 16 out of the most recent 20 tweets are exactly the same canned response. The other 4? Ads. Um - #MultiFAIL?
To Expedia: You now have had (as explained above) 8 hours of 3 different people explaining our situation, you know the email address of our Expedia account, you know my web blog, you know my Twitter address, you know my phone number. You also know how upset you have made both me and my new bride by treating us with such a ... non caring, scripted, uncooperative, argumentative, and possibly even deceitful manner. In the wise words of the great Kenan Thompson of SNL: "FIX IT!". And no, I'm NOT going away until you make this right. Period.
11:45 AM - Expedia corporate office called. The woman I spoke to was very nice and apologetic. She listened to me tell the story again, she says she understands the problem and she is going to work to resolve it. I don't have any details on what exactly that resolution might me, she said she will call me back in 20 minutes. She found out about the problem via Twitter. Thank you Twitter, and all of you who helped. Hopefully social media will win my wife and I our honeymoon, and hopefully Expedia will encourage their customer service teams treat their customers properly.
12:22 PM - Spoke to Fran again from Expedia corporate office. She has a flight for us tonight. She is booking it now. We will arrive at our honeymoon destination of beautiful Veligandu Island Resort only 1 day late. She cannot confirm today, but she expects that Expedia will pay for the lost honeymoon night. Thank you everyone for your help. I will reflect more on this whole situation and confirm its resolution after our flight is 100% confirmed. For now, I'm going to take a breather and go kiss my wonderful wife!
1:50 PM - Have not yet received the promised phone call. We did receive an email with a new itinerary for a flight but the booking is not for specific seats, so there is no guarantee that my wife and I will be able to sit together. With the original booking I carefully selected our seats for every segment of our trip. I decided to call into the phone number that Fran from the Expedia corporate office gave me. Its automated voice system identified itself as "Tier 3 Support". I am currently still on hold with them, I have not gotten through to a human yet.
1:55 PM - Fran from Expedia called me back. She confirmed us as booked. She called the airlines to confirm. Unfortunately, Expedia was unwilling or unable to allow us any type of seat selection. It is possible that i won't get to sit next to the woman I married less than a day ago on our 40 total hours of flight time (there and back). In addition, our seats could be the worst seats on the planes, with no reclining seat back or right next to the restroom. Despite this fact (which in my opinion is huge), the horrible inconvenience, the hours at the airport, and the negative Internet publicity that Expedia is receiving, Expedia declined to offer us any kind of upgrade or to mark us as SFU (suitable for upgrade). Since they didn't offer - I asked, and was rejected.
I am grateful to finally be heading in the right direction, but not only did Expedia horribly botch this job from the very beginning, they followed that botch job with near zero customer service, followed by a verbally apologetic but otherwise half-hearted resolution. If this works out favorably for us, great. If not - I'm not done making noise, Expedia. You owe us, and I expect you to make it right. You haven't quite done that yet.
Thanks - Thank you to Twitter. Thanks to all those who sympathize with us and helped us get the attention of Expedia, since three people (one of them an airline employee) using Expedia's normal channels of communication for many hours didn't help. Thanks especially to my PowerShell and Sharepoint friends, my local friends, and those connectors who encouraged me and spread my story.
5:15 PM - Love Wins - After all this, Lauren and I are exhausted. We both took a short nap, and when we woke up we talked about the last 24 hours. It was a big, amazing, story-filled 24 hours. I said that Expedia won, but Lauren said no. She pointed out how lucky we are. We are in love and married. We have wonderful family and friends. We are both hard-working successful people who love what they do. We get to go to an amazing exotic destination for our honeymoon like Veligandu in The Maldives... That's a lot of good. Expedia didn't win. This was (is) a big loss for Expedia. It is a public blemish for all to see. But Lauren and I did win, big time. Expedia may not have made things right - but things are right for us.
July 8th, 2011 - Expedia Hasn't Gotten The Message - After the Expedia nightmare was over and we made it to our honeymoon destination, we had a great time. We only lost the one night, but Expedia has not yet refunded us that cost yet. I am still expecting them to do so, so if you will, please tweet this story OR email Expedia and ask them to make things right for me and my wife.
10:19 am July 13th, 2011 - Fran from Expedia called my wife (I did not get to speak with her) stating that Expedia had been "diligently working on this" (so diligently, in fact, that we haven't heard anything from them in nearly a month) and they could find no fault on Expedia's side. After contacting Emirates Air and JetBlue, they decided that although JetBlue was showing a missing segment of an Emirates flight, that JetBlue should have allowed us to board and this is a JetBlue error. Per Fran, Expedia went out of their way and paid $400 per person to rebook our tickets after we missed our flight. This is interesting since on the nightmare day in question Expedia customer service confirmed to me over the phone that they did see notes in the system from JetBlue and Emirates where both airlines waived the rebooking or issue fees), and they are not refunding our 1st night because it is not their fault our flights were messed up. Is it their fault that they didn't re-issue the tickets in the first place so that we would NOT miss our flight? Is it their fault that their customer service personnel did nothing but talk 3 different people in circles for hours? I am absolutely infuriated yet again at the audacity of Expedia and their apparent complete lack of respect for the customer. I will continue my social media campaign and get JetBlue and Emirates involved if I can. Please join me by tweeting this this story OR by emailing Expedia and asking them to make things right for me and my wife.
Post in progress...
I will relay any further comments (or lack of) from Expedia soon, as well as an update on confirmation of their repayment of our lost resort room rates. I'll also post a picture of us on our honeymoon as soon as I can!
Final update...
A bit late on updating this. I tried for months to get Expedia to make up for their mistakes and horrible service by refunding us for the resort night that we lost. No luck. They continued to deny all responsibility. I will never use them again. Period.
Thursday, May 5, 2011
I know I don't usually blog about food - but it is one of my favorite things in life! So I just had to share this quick and easy recipe for vegetarian lasagna because it tastes amazing. It started as some recipe I found from a web search, but I didn't have certain ingredients and I wanted to add others, so here's what I ended up with:
In a large pot, sautee:
1 T olive oil
1 T dried thyme
1 T dried basil
2 cloves garlic, smashed
1 large chopped onion
1 10oz package of frozen spinach (add this last so the flavor of the herbs can come out in the oil)
Then add the following and simmer for 20 min. The sauce will be very chunky when finished:
32 oz fire roasted diced tomatoes
1 cup spagetti sauce of your choice
1/2 cup water
1 zucchini, sliced
2 jalapenos, sliced
In a separate bowl combine 8oz mozzerella, 1/2 cup grated parmesan, and 2 cups of ricotta (I used low fat).
In baking dish layer sauce, dry lasagna noodles, sauce, noodles. Then layer spinach, cheese, sauce, noodles and repeat, finishing with sauce on top. If you don't have enough sauce left over at the end to completely cover the noodles, don't worry about it too much. This makes great leftovers, and the microwave will soften those exposed oven hardened noodle edges.
Wow. :) I would post a picture but I ate it. Will add later.
Thursday, April 14, 2011
Here’s a quick start introduction to the ADO.NET Entity Framework Code First Development Pattern. For a more complete (but still intro) walkthrough, check out ScottGu’s blog.
At the time of this writing, you’ll need the EF 4.1 Release Candidate. After EF 4.1 is released you’ll no doubt be able to find it on the ADO.NET team blog.
1. Create a new empty ASP.NET MVC2 Web Application, add a reference to System.Data.Entity. If using the EF4.1 RC - add a reference to the EntityFramework dll that comes installed with it.
2. Add the model classes you’d like to represent your data objects. For example if you need to represent game nights, you might create a class called Game with properties that describe the game night date, location, and other information.
3. Create a “Context Class” that inherits DbContext, which contains nothing but DbSet<yourclassnamefromstep2> public properties. For example if you created in step 2 a class called “Game”, here you’d have a property defined as:
public DbSet<Game> Games { get; set; }
At this point, EF code first will make certain assumptions (which we can override later if necessary) that allow us to write less code:
- The context class will map its properties (those DbSet properties) to tables with the same names (a Games table, in our example).
- Each row in that table will map to an instance of the model class (a Game instance, in our example).
- Properties in the model class are columns in the table.
- A property in the model class with the name “Id” or “<classname>Id” will be made the primary key.
- Relationships between tables are inferred if one model class has a property with the name “<otherclassname>Id”.
4. Add a controller to see EF Code First in action. For example, add Index (to just return a list of our game nights) and Create actions (to create a new game night).
5. Add the views (Index: strongly typed IEnumerable of Game, Create: strongly typed Game – content Create).
6. Create the database. One of those EF Code First assumptions is that it will automatically look for a connection string with the same name as the context class (eg, Games). Scott Guthrie recommends the use of SQL CE 4 for early development, and moving to SQL Server or SQL Express closer to the time of deployment.
Important: IF you’re using SQL CE or SQL Express, and your connection string points to a database file that does not yet exist, it will be automatically created for you!
Updating The Model
If you update the model (lets say we add a new property to our Game class), you’ll get an error the next time you attempt to run the application (“context has changed since the database was created”. EF Code First can handle this too: in Global.asax, add the following to the Application_Start event handler:
Database.SetInitializer<<yourContextClass>>(new RecreateDatabaseIfModelChanges<<yourContextClass>>());
RegisterRoutes(RouteTable.Routes);
Gu gives a nice tip – create a custom class that implements RecreateDatabaseIfMOdelChanges<<yourContextClass)> and overrides the Seed function to add “seed” rows to the database with default data. This way when we recreate the database after a model update, the database isn’t empty – but is seeded with our default data. Use this new custom class in the call to SetInitializer instead of RecreateDatabaseIfModelChanges.
Thursday, March 10, 2011
Here is a quick notes about using regular expressions in the VS2010 Find Replace dialog.
1. To create a backreference, use curly braces (“{“ and “}” ) instead of regular parentheses.
2. To use the captured backreference, use \1\2 etc, where \1 is the first captured value, \2 is the second captured value, etc.
Example:
I want to find*:
info.setFieldValue(param1, param2);
and replace it with:
SetFieldValue(info, param1, param2);
To do this, I can use the following find/replace values:
Find what:
{[a-zA-Z0-9]+}.setFieldValue\({[a-zA-Z0-9., ]+}\);
Replace with:
SetFieldValue(\1, \2);
Use Regular Expressions is checked, of course.
*If you’re wondering why I’d want to do this – because I don’t have control over the setFieldValue function – its in a third party library that doesn’t behave in a very friendly manner.
Wednesday, January 26, 2011
Here is the simple function I added to my powershell profile:
To find the AreaId and IterationId for your project/versions, just look at a bug you know is setup correctly:
PS C:\> tfpt query /collection:http://3bs001vsteam:8080/tfs /wiql:"select AreaId,IterationId from workitems where ID = 879"
Next I hooked this into my existing "todo" function by adding a new case in its $target switch statement for “tfs”, which calls my create-tfstask function and returns. Updated todo function:
Tuesday, December 21, 2010
I can never remember how to rollback a check-in, and there all kinds of mess in search results about this (change between different versions of TFS etc), so I thought I’d just put this here so I won’t forget anymore. :) Thanks to @manningj, TFS genius.
Just drop to the command line and use tf.exe.
Example:
tf rollback /changeset:12345
For more on the tf.exe commands:
tf help
Thursday, December 16, 2010
With a little help from Shay Levy’s post on Stack Overflow and the MSDN documentation, I added this handy function to my profile to automatically recycle all IIS app pools.
Tuesday, October 26, 2010
I put the following function in my PowerShell profile so that I can just bring up my PowerShell console and type myself a quick “todo” note that automatically gets emailed to me for later. There are several things of note in this tiny script:
1. Note the use of the $args variable to effectively allow the user to enter $msg parameter without quotes even if it contains whitespace.
2. In this particular example I’m sending the email through Gmail. smtp.gmail.com, port 587, SSL and authentication required.
3. Check outhe $gmailcred variable, which gets set earlier in my profile. It gets read from a secure string file.