Corey Coogan

Python, .Net, C#, ASP.NET MVC, Architecture and Design

  • Subscribe

  • Archives

  • Blog Stats

    • 110,193 hits
  • Meta

Archive for the ‘Business’ Category

Use Excel to Automate Data Entry

Posted by coreycoogan on January 27, 2011


Over the course of my career, I have frequently been given random excel spreadsheets that contain data that needs to be either inserted or updated to a database. There are many routes one can go with this task, most of which are more complicated then necessary. I find the simplest solution is to use Excel formulas to generate my SQL statements for me.

  1. Create a formula for the SQL statement in a blank cell of the spreadsheet’s first row of data.
  2. Copy and paste the formula to the same blank cell in every other row. This fastest way I know to do the paste is to: select the first cell you wish to paste.  Hold down CTL+SHIFT+END.  This will select everything from the point of selection to the last row and last cell in the spreadsheet.  At this point, continue to hold down shift and arrow back to the left to get to the original column and hit enter.

    With the cursor on the cell with the formula, Excel outlines the cell with a tiny square on the bottom right corner.  Double click on that tiny square and your formula is repeated all the way to where the data ends next to it.
    (Thanks to Todd Boehm for posting this to the comments)

    The formula will now be copied to each row.

  3. Select every row that contains the sql and paste into your database query window.
  4. Run the queries and you are done.

An Example

Given the following spreadsheet for some customers, I will show a simple formula to do an insert into the customer table.

A B C D
1 NAME PHONE SIGNUP_DATE
2 Joe Smith (920) 555-1112 5/21/2010 = “insert into CUSTOMER (name,phone,signupDate) values (‘” & A2 & “‘, ‘” & B2 & “‘, ‘” & TEXT(C2,”M/dd/yyyy”) & “‘)”

Convert Excel Date to Text or String

Notice the use of the TEXT function against the value for SIGNUP_DATE.  This is necessary with dates because Excel will spit that value out in a serial format, which isn’t what we want in our database.  Use the TEXT function to convert the Excel Date to text for the insert.

Posted in Business, Database, Helpful Tips | Tagged: , , , | 1 Comment »

Good Developers Know When to Write Bad Code

Posted by coreycoogan on September 22, 2010


I read a blog post last week that stated plainly if you “knowingly create a bad design or write bad code”, then you are a bad developer. The post went on to list 4 criteria that must be followed if you break this rule that would save an offender from earning the “Bad Developer” badge of shame.  (UPDATE: I misquoted.  The post actually said “one or more of the following)

  1. Get upset because they know they are writing less than perfect code/design.
  2. Leave a comment stating why the code/design was done in this factor.
  3. Add a ToDo for refactoring later.
  4. Will do the imperfect code/design in such a way that the refactoring effort will require less effort.

I read this post and had to disagree with such a dogmatic, black-and-white view. So here’s my take on the whole thing, breaking it down as simply as possible.

  • Time is finite.
  • Resources are finite.
  • Requirements are seemingly infinite.
  • Deadlines exist, sometimes arbitrarily and other times for real reasons like market pressures or legal diligence.
  • To meet deadlines when you are in the hole there are 2 options:
    – Cut features and scope.
    – Cut corners.
  • Sometimes features and scope can’t be cut (see Deadlines).
  • Some tasks require commodity code – simple crud forms for updating contact information for example.
  • Some tasks require specialization code – getting to the meat of the domain to add real value.
  • Many clients, who are paying the bills, don’t care about the commodity code’s design principles as much as they care about their deadlines and commitments.
  • Refactoring takes time, and given that time is finite, must be prioritized from highest ROI to lowest ROI.
  • When commodity code is poorly written but works just fine and causes zero friction, choosing to refactor it over specialty code can come with a hefty opportunity cost

Given this information, I say that a person can still be a good developer even if they knowingly write bad code. To me, a good developer knows when to write bad code and where to focus their time and energy.  In a perfect world, this would never happen – we would always do things the best way.  Obviously this isn’t reality and sometimes sacrifices have to be made. I believe that the good developer understands this and knows to sacrifice that which offers little value for that which offers great value.

Nobody is going to lose in the market because their “contact us” form is saving to the database in the code-behind. But choosing to devote an hour “doing it right” that could be spent refining the domain model or refactoring real friction points that cause actual pain can come at a high price. Like the old saying goes – Good, Fast or Cheap… pick any two.

Posted in Business, Developer Life | 5 Comments »

Reviewing “Getting Real” and “REWORK” by 37 Signals

Posted by coreycoogan on September 15, 2010


NoMojo I’ve recently been in a funk.  I felt as though a big part of my Mojo was missing.  I’ve been in a bit of a dead-end gig lately where the work brings me little to no joy, so that could have something to do with it.  On the other hand, I have a fun side project that I’m trying to get off the ground, but it’s been almost impossible to get motivated in the last several months.  Part of this is due to the side work I do at night, but mostly it’s as simple as a loss of Mojo.

I’m fascinated with 37 Signals (highly recommend their blog). Not only from a technical perspective, but also from a UI Design and business standpoint as well.  I have been poking around the free eBook version of Getting Real for while.  One day, while wallowing in my funk, I decided to buy Getting Real and REWORK from Amazon and search for some inspiration.

Inspiration

I found my lost Mojo!  Reading these books helped revive my passion for software development.  They got me excited about the side project again – so much so that I removed the half completed features and deployed what I have so I could launch the blog to start building relevance and begin driving traffic.

Getting Real

Getting Real says it’s about building “a successful web application “, but it’s about so much more.  It covers a wide range of business concepts as well and was really fun to read.  This book emphasizes simplicity.  Keep things simple and deploy.  Don’t get bogged down in analysis paralysis.  That’s what happened to me with RoomParentsOnline.com.  I got so consumed with the “simplest” way of making the classroom portion self governing, yet safe and secure for the students, that I ended up conceiving a solution that was so complex and difficult to start, my brain just shut down and found excuses not to work on the project.

After reading Getting Real, my whole thought process of handling features changed.  I reevaluated what I was doing and found a solution that is 1000 times simpler and probably just as effective.  It also helped get things in perspective about how cheap and easy it is to try something and deploy.  Get people using your product.  If it sucks, change it.  In many cases, the details we obsess over are meaningless to the end user.  I’ve always tried to follow the Agile mantra of “Simplest Solution that could Possibly Work”, but I somehow drifted.

I highly recommend this book for anyone doing web development or involved in web development.  It’s an eye opener and a breath of fresh air.

REWORK

This is another amazing book.  Unlike Getting Real, REWORK is not specifically about developing web applications, but about business and succeeding in it.  The back cover says a lot of what this book is all about:

ASAP is poison
Underdo the competition
Meetings are toxic
Fire the workaholics / emulate drug dealers
Pick a fight
Planning is guessing
Inspiration is perishable

Before reading the book, I subscribed to most of these ideas.  I hate wasting time in meetings and nothing drives me more insane than documentation for the sake of documentation.  BDUF is a proven failure, yet it continues to dominate big Corporate America.  After reading REWORK, I was so happy to hear that breaking away from this mentality can work – and there’s proof.

A fair amount of content from Getting Real is repeated in REWORK, but not enough for me to recommend one book over the other.  Since reading it, I’ve been recommending it all over the place.  This book is a must-read and I wish I could send every client, every partner and every consultant I work with a fresh new copy.

The theme is the same as Getting Real.  Do things simple and do them fast.  Say NO to new features until they come up again and again – then you know they have real value.  The authors do a great job of citing their own successes, as well as those of other companies.  They also use simple metaphors that are easy to understand and fun to read.  I strongly encourage anyone reading this blog to get your copy immediately and read it.

Conclusion

Getting Real and REWORK are fast and easy reads and I found the casual writing style felt like I was a conversation with the authors.  Both books are phenomenal and capable of being real game changers when read with an open mind.  So please, when you do read them, be open.  Don’t hold on to your old way of thinking.  Do yourself the favor of entertaining the ideas in these books so you can appreciate the true merit.

Posted in Business, Review | Tagged: , , | Comments Off on Reviewing “Getting Real” and “REWORK” by 37 Signals