About Me

The Many Hats of a Business Owner As the saying goes, a business owner has to wear many hats. After you've owned a business for a while, you really come to understand how true this is. Even if you have employees who do a lot of your day-to-day marketing, ordering, and accounting, the responsibility for these tasks is ultimately your own. If you are starting to feel overwhelmed with the many hats you're required to wear, you have stumbled upon a good resource. We created this website to share knowledge about all of those hats — all of those roles you have to play as a business person. Start reading, and you'll not only gain helpful advice, but also realize that you're not alone.

Search

Categories

3 Common Issues With Converting Excel Sheets To Databases

The appeal of using software to convert Excel spreadsheets to database entries is one that's easy to understand, especially with the growing interest of even smaller businesses in using big data. There are, however, a number of challenges that come with the job. Take a look at a few of the things that may come up when you try to convert Excel spreadsheets to database formats.

Columns and Rows Don't Line Up

One of the biggest worries when you try to automate the conversion process is that the rows in the spreadsheets don't line up well. This can occur for a variety of reasons, but manual entries that weren't focused on maintainability and reuse are among the worse. Also, some entries can get dumped into the wrong columns, even if the sheets were generated using software built for the purpose.

Special Characters

Database entries are usually scrubbed before they go into the system. This applies whether they're coming in through keyboarding or being imported through automation. One unintended consequence of this is that special characters, such as accented letters in non-English languages and loan words, can get mangled. In the best-case scenario, such mangling reduces human readability. In the worst-case scenario, it may torpedo the usability of the database, causing accurate matches to miss because the special characters have been changed for storage and security reasons.

Data Types

For a database to be maximally useful, it's normal for columns to be typed by the data they contain. If you have nothing but non-decimal numbers in a column, for example, setting that column to nothing but integers can increase indexing speed and reduce storage and memory overhead. The downside to aggressively typing columns during automation is that poorly typed data can get turned into null entries. For example, the word "cat" sitting in a column that's only meant for integers will end up becoming a zero.

Identifying Problems

There are two ways these sorts of issues can be addressed. First, some degree of human quality control is always necessary to make sure things work out properly. A person simply scans the data and looks for common problems, such as garbled text or misaligned columns. They then rerun the conversion process using new parameters to account for problems.

Secondly, automation can be used to quickly detect errors. For example, the database entries can be dumped back out to Excel. If the output file doesn't match the original, that can flag potential problems.

Tags: