"Something happens between the time I decide to do something and the time to actually do it. I have all these ideas about how great and good for me it will be and all the benefits in the beginning, but when it's crunch time I only think about how hard and pointless and unnecessary it is. What's happening and how do I stop it?"
Have you ever heard anyone say you should go into marriage with both eyes wide open, and then after you're married to keep one eye closed? I think the meaning behind that advice is that you should consider all the potential problems and hardships realistically before making such a big decision, but after the decision is already made, you should do your best to ignore all the minor problems and focus on the positive (my wife must have had both eyes closed when we were engaged and plugged her ears after we were married). I know you're not asking about marriage, but the same principle applies to goals.
When you set a goal, it's important to consider all the potential obstacles. You should consider how difficult it will be, what sacrifices will have to be made, how long it will take, and excuses you may try using to abandon the goal before you even start. After taking a hard realistic look at the goal, you can then decide if the potential gain is worth the potential difficulty. If the answer is yes, you are ready to continue.
Now when it's time to work on the goal, be aware that these obstacles may pop up every now and again, but you're prepared for them and can focus on reaching your goal and it's associated benefits. An obstacle is a trial that must be overcome on your journey towards success. An obstacle is NOT a reason for you to abandon your journey.
In your case, you may only be considering the benefits in the beginning. You don't start thinking about what will be required until you are about to get started. You suddenly realize there is a ton of complicated and difficult work between you and your goal, and that becomes all you see. The benefits fade away and leave you with a sense of , "why am I even doing this?"
Next time you decide to do something, consider the benefits and requirements together. It will give you a more realistic view of your goal, and a better chance of succeeding.