If you would like to do active duty Civil Affairs, I suggest you pick a start-off MOS that will give you a decent career base to build from like 11B, 68W (if you wanted to be a CA medic after), or any of the other maneuvers and fires jobs that give you the great basics for a follow-on career in SOF. Before deciding on a SOF job in the army, take a careful look at what you want out of it as well. Civil Affairs, Psychological Operations, and Special Forces all frequently work together but their niche capabilities and responsibilities are very different. The common thread in all three is the fitness requirement, ability to pass psychological screening, mental toughness, and intellectual capacity required to do any of them. There are minimum scores required just to attend any of the three assessment programs at USAJFKSWCS as far as the ASVAB is concerned, and there is a bare minimum physical standard that will automatically clip most people wanting to try out. That is substantially more difficult if you are looking at Special Forces, but none of them are easy.
You can not enlist off the street to go Civil Affairs on active duty, and transitioning to active duty from the guar or reserves after the fact is next to impossible. If you did transition, you would have to already be an NCO/Captain, you would still have to attend Assessment and Selection, and you would attend the entire qualification course regardless of prior MOS training.
Off the street, your SOF options in the Army are the 75th Ranger Regiment or Special Forces. Both paths from enlistment to the actual maneuver unit are not easy. Attrition in the SFQC is tough and the course is long and challenging for almost anyone who tries it. Indoctrination to the 75th is an absolute ass-kicker, and if you wanted to do that, make sure you are the toughest, fastest, and smartest soldier out of the group all the way through initial entry training, because you will need it to make it there. 75th Ranger absolutely does not lower their standards for anyone.