Lovable Nerd

Somewhere at the three-way intersection of the Hollywood Nerd, the Dogged Nice Guy and The Woobie, there is a character who is, quite frankly, a huge dork. He is socially awkward, not conventionally attractive, and a failure at his attempts to win the pretty girl (if he can even get a complete sentence out in her presence). And we root for him, because he's just so humble and adorable in between his flashes of brilliance. He might be capable of heroics if pushed, but he is generally not the Badass Bookworm or the Badass Labcoat (at least, not yet). He's smart but generally only in the hard sciences or obscure topics, giving him a possible foot in the door as a tutor to The Chick or an assistant to The Hero. Obviously, in a Five-Man Band, Lovable Nerd will be The Smart Guy. Often subject to geeky turnons.

For a lustable nerd, see Nerds Are Sexy. Has a high chance of being Adorkable.

Examples of Lovable Nerd include:

Anime and Manga

Jou from Digimon Adventure is a definite qualifier.
Madarame from Genshiken, though the entire cast could fall under this trope.
Kohta Hirano from Highschool of the Dead.
Eiichirou from Baby Steps, even in universe. Despite the fact that he's pretty nerdy and always at the top of his class, he seems to win the respect and admiration from the majority of his classmates.

Clark Kent in some incarnations, likely to set himself apart from his Superman alter ego.
"Gene" Strausser, who's essentially a big kid playing with Damage Control's collection of super-powered toys.

Most of the protagonists in the Revenge of the Nerds movies.
Michael Cera tends to be typecast as the Lovable Nerd in most of his movies.
Napoleon Dynamite.
Flynn Carsen, the title character in The Librarian series of movies. His nerdiness is a big selling point... considering he's an action hero. Who has 22 college degrees. And who's also the biggest nerd in the world. And, oh yeah... he has an adventuring partner.
Dave Stuttler in The Sorcerer's Apprentice. He can barely talk to the girl of his dreams, but HE MAKES MUSICAL LIGHTNING! (Sometimes he even uses a Tesla coil, too.)

Ponder Stibbons, to a point. He's a wizardly quantum physicist who looks like Harry Potter, age 25, for heaven's sake.
Waldo Butters of The Dresden Files. It's hard not to like the little guy, what with his cheery demeanor, polka obsession, and occasional flashes of pure awesome.
Tre from Stuck, as well as his even more nerdy friends Cale and Grassy.

Conner from Primeval.
Roy and, to an extent, Moss from The IT Crowd.
Leonard and Raj from The Big Bang Theory. Sheldon and Howard, not so much.
- Your Mileage May Vary - You only have to go on the show's Just Bugs Me or Your Mileage May Vary tabs to see that a lot of people think Raj is the least likeable in the group. Howard may have been annoying when the show began, but has developed to become more likeable, and many find Sheldon's quirks endearing. Leonard is really the only universally loved member of the gang.
- Your Mileage May Vary even further. Many people dislike Leonard as the seasons progress, and find Sheldon, Raj, and/or Howard much more palatable.
Daniel Faraday from Lost definitely counts.
The eponymous character in Chuck—at least before he starting Taking Levels In Badass. Also an example of Nerds Are Sexy.
Charlie Eppes in Numb3rs.
Spencer Reid from Criminal Minds—though he's been known to get the girl from time to time, and he's definitely capable of being a Badass. But he is very beloved, so you can't argue with that.
Both Tams in Firefly.
Bart from The Adventures of Shirley Holmes has quite a few admirers among the fandom. Also Matt, Shirley's genius Love Interest.
Marshall from Alias fits this trope to a T.
On 30 Rock Liz thought herself to have been one of these in high school. However, her classmates remembered her as a jerkass bully
Billy from Mighty Morphin Power Rangers. He's a fan favorite for a reason.

Otacon in Metal Gear Solid 1 (he turns into a Badass Labcoat later on in the series). When Snake first meets him, he is a computer hacker and an otaku, and so terrified by recent events (particularly an encounter with Grey Fox in which Snake intervenes) that he has wet himself. And he is definitely Captain Awkward with Naomi Hunter in MGS4.
Reese Worthington.
Shad from The Legend of Zelda: Twilight Princess just can't stop nattering on about his esoteric studies. And is adorable while doing it.
Jeff from EarthBound.

Drowtales: Yafein, the inspiration for the Designated Monkey trope.
Marten from Questionable Content starts out as one of these.
John Egbert of Homestuck is positively Adorkable in his awkward nerdiness and earnest, friendly personality.

Doctor Horrible is a Lovable Nerd trying to be a Badass Labcoat, at least at the beginning.
Tyler from Disappointing Gay Best Friend is a nerdy bookworm type who's constantly annoyed by his friend Mikala's attempts at making him a Pet Homosexual, but if the videos' comments are to be believed, he has built up more than a few admirers.
Most of That Guy With The Glasses are a mix between Hollywood Nerd and Adorkable. Despite their penchant for Accentuate the Negative, the Fan Boy and Fan Girl inside them come out for regular super-sonic Squee.

And Lexington from Gargoyles? (OK, so he'd really rather have the handsome guy than the pretty girl...)
Harold from Total Drama Island. Also Cody, with even more emphasis on the Dogged Nice Guy part.
Gretchen and Gus from Recess are both Adorkable and really sweet.
Twilight Sparkle from My Little Pony Friendship Is Magic. She's a Badass Bookworm who starts the series as an Ineffectual Loner. Even after she learns to socialize she's still depicted as being organized almost to a fault and as a compulsive studier who's joy for learning is absolutely adorable In one episode, she hosts a sleepover by reading a guidebook to slumber parties, in another she dresses up for Nightmare Night as an obscure historical character that no one recognizes (And is surprised by this), and in another she's so scared of bruising her study grades that she actually kicks off a moderately large crisis in her town.