Tag Archives for making it look easy

Fiddler & Wireshark

I’ve had occasion this week to use some new troubleshooting tools at work. I’ve been doing a lot more support of the production environments (instead of just Tier 3 support tickets), so I’ve expanded my toolbox.

Fiddler is an application that logs every HTTP request made from your computer. If you’re getting a script error, it can tell you exactly what data is being returned. Here’s a quick guide on getting started. Get Fiddler here.

Wireshark is a packet analyzer that tells you every piece of data being transferred and what port it’s using. If you’re getting blocked by a firewall, it can tell you exactly what traffic is being passed. Here’s a quick guide on getting started. Get Wireshark here.

Convert to Number in Excel

Common problem: you’ve opened up an excel workbook and a column of numerical values is being stored as text. There’s that annoying green earmark in the top left of the cell. If you’ve got 3000 rows, you don’t want to step through each value and click on the context menu to convert it to number. That would take forever. here’s a workaround that takes less than a minute.

  1. Create a new column to the right of the column of data that is number stored as text.
  2. In this new column, add the number one (1) to every row. You can enter it in the first few rows, select those rows, then double click on the bottom right of the cell to extend the value to last row.
  3. Select the row you wish to convert to number and Copy to the clipboard.
  4. Select the top cell of the newly created row, right click and choose “Paste Special”
  5. In the Paste section, choose “All”. In the Operation section, choose “Multiply”. Click Ok.

Because it is performing a mathematical operation, it will convert the data type to number. Because it’s multiplying by one (1), it won’t modify the value. Pretty sweet.

(via Blue Moose Technology)

30-Minute Meals [and by "Meals" I mean SQL Queries]

It was an unexpected call and it carved up a 45 minute divot in my afternoon.

On a monthly basis I will get an email from Emily [client reporting] and one from Amy [client billing] for reports out of the Point of Sale system. I could probably set them up with access to the POS app, but considering how finicky it can be, I’d rather just run the reports myself than commit to supporting it on more workstations than I have to.

So I get this call from Amy in Accounting because the cash logs from the store don’t match the bank statements and would it be possible to run that monthly report again. But maybe this time, can I run it individually for each day in January. And can she get it today. Sure, but not without some sarcasm. I told her I’d be happy to do it, but she’s going to owe me a steak dinner. She counter-offered with a cupcake. Turns out someone upstairs brought in a whole bunch of cupcakes and nobody was eating them. I demanded she personally deliver it. She said she didn’t know where I sit. Just go downstairs and walk around until you find someone who looks like me who is expecting to see someone who looks like you holding a cupcake.

The daily report for January 2nd wasn’t helpful at all, so it’s time to dig into the SQL database for a little TRANS JOIN SALE action. I told her I would have to dig into the backend. She giggled. Over the next 5 minutes I was grunting at my computer while trying to find what transaction types were for credit cards. She asked if my computer was being slow or if I just didn’t know what I was doing. So I took a screenshot of SSMS screen with the TRANS table open and sent it to her. Stunned silence. Amy, I’ll call you back. The next 30 minutes was putting together the query and prettying up the results. Probably the most gratifying part of my day. 6 months ago, I would have had the same reaction as Amy: stunned silence. I wouldn’t have had a clue what I was looking at and would have been more than a little intimidated. But today I made it look easy. Feels good.

I never did get that cupcake. But I saved the code. I named it “how-to-get-amy-from-accounting-off-your-back.sql”