In the case of Sir Arthur Conan Doyle, his extraordinary gullibility came to him late in life, after he suffered from terrible depression due to the deaths of several close relatives, including one of his sons, either in World War One or the worldwide flu pandemic immediately afterwards (which actually killed more people than WWI, and is the reason doctors still get paranoid a century later whenever a new strain of flu pops up somewhere). Like so many others in those terrible times, he was willing to clutch at any straw if it gave him hope that his loved ones, though gone forever, could somehow reassure him that they still existed in some way and were happy in the afterlife. So he wasn't a stupid man, just a very human one. Though I'm afraid Sherlock Holmes certainly wouldn't have approved.
Harry Houdini's motives for being aggressively skeptical were surprisingly similar. He was very close to his mother, so her death affected him greatly, and he went along to a séance because, like Conan Doyle, he desperately wanted to be convinced that his mother in some sense still existed and was happy. Sadly, that's not what he got. Because he didn't look particularly Jewish or make a big thing of it, very few people other than his personal friends knew he was a Hungarian Jew called Erik Weisz who moved to the USA with his family at the age of 4, but grew up in a household where in private the family spoke an amazing mélange of languages including German, Hungarian and Yiddish that no outsider would understand (and which no medium could possibly fake). His mother never did get around to learning English, and although Houdini wasn't particularly religious, she presumably was, what with being married to a rabbi and all.
So you can imagine Houdini's reaction when the medium put on her best old lady voice and had the late Mrs. Weisz tell her son in perfect English that she was blissfully happy to be with Jesus at last just like she'd always hoped she finally would be! This really hurt and offended him in the worst possible way, and he never, ever forgave Spiritualism for taking financial advantage of his and many, many other peoples' grief in this appallingly cynical fashion. Hence not only his private debunking of fraudulent mediums (Houdini was always a far better escapologist than magician, but he was easily good enough to spot their painfully crude tricks), but also the section of his show in which he replicated the marvels of the séance-room live on stage, only better, then announced at the end that it was all trickery, and the so-called "real" mediums were doing it in just the same way.
Conan Doyle, on the other hand, had fallen for the con in the first place because when the medium assumed that his son was an English-speaking Christian, she got it right, and the usual platitudes sufficed. He continued to communicate with his deceased son and many other people for the rest of his life as a coping mechanism to deal with the desperate, crushing grief that otherwise might have killed him, and I'm sure nobody with a heart would think any less of him for that. Though when it got to the stage where his favorite medium, who channelled a spirit called Pheneas, insisted on not only going on holiday with the Doyles so that Pheneas could continue to advise him on any little matters that might crop up, but living with him and his wife, and furthermore, claimed that the completely incorporeal Pheneas needed his own room, and it had to be painted purple, clearly things had gone a bit far! And as for those fairies...
The reasons he accused Houdini to his face of actually having psychic powers but lying about it aren't quite as insane as you'd think. Although Houdini desperately wanted to expose the methods of fraudulent mediums as convincingly as he possibly could, there was one step he wasn't willing to take, and that was to do their tricks himself, and then explain to the audience how he'd done it. At the time (and this was still true until very recently), if you wanted to have a career as a stage magician in Britain or the USA, you absolutely had to belong to the Magic Circle, and they had very strict rules indeed. Any breach led to permanent expulsion, meaning that your career was dead. The big no-no was revealing anything whatsoever about how magicians did their tricks to a non-member.
This meant that he couldn't risk explaining his methods even to a friend like Conan Doyle. Usually he didn't have to explain himself anyway, since if he, a professional illusionist and conjurer, faked séance phenomena onstage and then announced at the end that it was all a trick, the audience members usually took his word for it without needing to know the exact details. There was, however, a rival act called the Davenport Brothers (Houdini didn't like them too well) who did the same thing even better. But unlike Houdini, their entire schtick was to leave it ambiguous as to whether they were just clever tricksters or really were summing spirits from the Great Beyond - that way they got the biggest possible audience. And of course, unlike Houdini, they hadn't been personally hurt by mediums, and therefore didn't give a fig how fake they were.
So Conan Doyle could sort-of-logically persuade himself that, since at least one other high-profile act existed which might be using psychic powers on stage, and that part of Houdini's act was pretty much identical to what they were doing, maybe he was using supernatural abilities to fake being a fake, but lying about it for some reason (you know, there's a graphic novel in there somewhere...). Still not very logical, but it was that or watch his belief-system crash down in ruins and be left with no defense against the soul-destroying depression it kept at bay. You can't really blame him. But you can't blame Houdini either for being offended when his friend accused him to his face of secretly being the thing he hated most and living a lie.