Importing Data the WTF Way

AwesomePro 2.0 had it all – the low price of a mom-and-pop software company, 24/7 support, and a surprisingly robust feature set. Their presentation to the higher-ups couldn't have gone better, with the bigwigs ready to whip out the checkbook before it was even over. AwesomePro were light years ahead of the competition at literally a fifth of the price. It would be the perfect fit to replace the aging and temperamental in-house ERP solution in use by one of Stewart T.'s clients.

Implementation

Getting the software in the building was to be Stewart's top priority – his client was like a child on Christmas begging his dad to set up the Wii. "Is it done yet?" No. "When can we use it?" Um, soon...? "But we wanna use it to prioritize our initiatives and do some claims processing nowwwwwwww!"

Being pinged for the status of the install was a constant distraction, as were a few last-minute bumps in the road encountered in any large-scale software integration. And strangely, the "crack" team of support staff was often unable to answer most of the questions about their own software. Stewart brushed it off, as he was able to work around or figure out most of the issues on his own.

Before long, they had their system up and running, including a full import of data from the previous system. And it was glorious! The bosses were happy, the ERP company was happy, and Stewart was happy. There were a few curious issues with the data migration however. Typically, issues with data migration come in the form of incomplete or null data, a data element missing a parent data element, or columns lined up incorrectly. I'm sure we've all encountered a "Mrs. 123 Oakview Ave" living in the beautiful city of "female, 555-867-5309".

These errors, however, were subtler and more alarming. "John Oxford" became "John Ocford." "44 Front St" became "444 front st." The live system showed no such typos, and when Stewart reviewed a small sample of records, 25% had at least one typo! Including, yes, the record for Stewart (or should I say "Stewert").

Fixing the Typos

Spirits were still high when Stewart brought the ERP company in to discuss some of the data migration issues. "I need you to walk me through the import process," he insisted. Stewart and the technician ("John") sat at a PC.

John immediately opened Excel, and Stewart's heart sank. He had the data extracted in dozens of CSV files, which John would manipulate quite a bit, and then finally import it into the new system. So the process wasn't the crime of the century, but it wasn't reassuring, either. Still, it wasn't yet evident where the typos had come from.

"So that's what we do," John said, still smiling. "Does that answer your question?"

"Well, no. What happens to move the Excel data into the actual database?"

John froze. "Well, that part's a little more involved..." Stewart remained silent, and John stared back blankly.

"The thing is, it's a relational database," John stammered.

"And...?"

"You can't just insert data from Excel into a relational database!"

Stewart sighed. "Well, yeah, it's not a direct insert, but there are ways to import, you just have to do it in the right order. As long as you know what the constraints ar-"

In a Shyamalanian everything-comes-together moment, Stewart had a rush of understanding. The team not knowing the software well, the exact same featureset of the six figure system, oblivity to the constraints – they didn't build the software at all! It was a knockoff from somewhere else all along! Also, John was actually a ghost the whole time! (spoiler warning if you haven't seen My Big Fat Greek Wedding)

Stewart swallowed hard, his career flashing before his eyes. "So how exactly are you entering those 35,000 records?"

"It's a process of... er..." John stared at the table. He mumbled, "it's a... the process..."

Stewart raised his voice slightly, and more assertively repeated his question. John looked up, cleared his throat, flipped into confidence mode, and tried to put a good spin on it.

"Our process is to turn over the data to a team of highly-skilled data insertion analysts who are responsible for importing the data." He smirked, satisfied with his answer. No way Stewart was sharp enough to decode the marketing spin!

"So," Stewart pressed, "you've got people retyping this directly into the database."

Immediately, John's bit switched back to "nervous stammering," and his eyes shot back down to the table. After a long pause, he quietly muttered, "kind of, yeah..."

Thanks to Stewart's expertise, he was able to provide detailed guidance on how the data could be imported from Excel. "Google for 'sql import from excel,'" he advised. Still, he couldn't bring himself to ask how much money the highly-skilled data insertion analysts would see from the sale.

[Advertisement]
BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!