Getting Information Into your Database
If you have decided to create a database you may realize that you have a huge volume of data that needs to be entered into your computer. The primary ways of getting information into your database is direct entry or an automated import.
Manually typing everything into database data entry forms is sometimes the only way to enter the needed information. Unfortunately, this is a time-consuming process and prone to errors. Accordingly, you want to do everything you can to speed up the process and mitigate the errors.
Automated import is great if you already have a reliable data source. However, before you can import you need to make sure your data source can export its contents in a format that your database is able to import.
Database Data Entry via Forms
A data entry form is used to enter information into your database. This information might be pre-existing or it could be new information. If you don’t use a data entry form you will not to work with raw database formats, and add information directly into tables.
Working with raw database table is not recommended as the information will need be entered in the exact manner that the computer understands. For example, if you are entering a list of addresses, your database will probably have different tables for the name and the street. The name table may have a pointer to the street table. If you were to enter the information directly into the database, you would need to add all the names in one place, and all the streets in another place. Then where you entered the name, you would need to add a pointer to the appropriate street in the street table. Obviously, this is not a very natural process. Accordingly, a more human friendly data entry form would be a better way to add information into our database.
Depending on the type of database you are using, you may need to design your own data entry form or you may need to use an existing interface. For example, in a Microsoft Access database you can custom program your own data entry forms. Even Microsoft Excel has mechanisms to create custom data entry forms that let you easily enter information into spreadsheets. Data entry forms can also be created for large scale commercial database systems like SQL and Oracle. However please note that creation of these forms often requires a skilled software developer, with front end user interface design skills.
In other databases, like our own Tracker Ten windows software application the data entry forms are predefined for you. With either approach, after you add information into your data entry form, your computer will automatically take the data from the data entry form and load it into the appropriate tables in your database. Your computer will also setup all needed relationships.
Data Entry Form with Data Validation
When you are entering data in a data entry form you want to make sure that the information you are inputting is validated. This means that the information has no errors, is in the correct format and has no logical inconsistencies.
Errors could be as simple as spelling mistakes all the way to completely incorrect values. For example, if you are entering information into a retail store inventory database, you want to make sure that there are no errors in the selling price (these types of errors could cost you a lot of money!). To prevent these types of errors you want to build as much intelligence as you can into your data validation functionality in your data entry form. To prevent a pricing error, you may out range checks in your price fields. If an entered price is outside an expected range, the computer can warn you or prevent the data entry.
Formatting errors can be easier to catch. For example, if you are trying to enter a number into a date field, this is something a computer can easily detect. Again, your data entry form could completely prevent you from making these types of mistakes.
Logical inconsistencies require the most work to prevent. An example of a logical inconsistency would be adding “Los Angeles” in a city field, then adding “New York” for the state. To prevent this type of mistake, the computer needs to know that Los Angeles is in California, not in New York. However, if you build these types of checks into your data entry form from the beginning, you can prevent larger problems with corrupted data in your database in the future.
Tips for Entering Data
Before you begin data entry you should make sure that the fields in your database are properly setup. You want to make sure that you are not tracking duplicate data. Also, database fields often let you setup default values and drop-down field choices. Setting up these defaults in advance can save you significant time in the future.
After you are sure that your fields are properly setup, here are some techniques you can use to speed data entry:
- Use drop-down fields wherever possible. Not only will this speed data entry, it will also prevent typing errors (assuming the drop-down field value is correct).
- Use copy and paste functions when possible. Obviously, this can make data entry go faster.
- Fill the data entry form in the order it appears on the screen. Often you can use the “Tab” key to move from one field to another. If you are filling fields in order, the process will go much quicker.
- Make sure your work area is properly setup. If you are inputting data from paper forms, it’s great to have plenty of space on your desk.
- Minimize distractions. If you are entering huge volumes of data, interruptions can really hamper your work flow.
- After you have entered data, take a bit of extra time to proofread. Ideally data validation on your data entry forms will catch most errors. But there is might be mistakes that your automated validation does not catch.
Hiring Data Entry Clerks
If you don’t have the time to perform data entry functions yourself, you can often inexpensively hire data entry clerks. Data entry clerks are experienced with transcribing data from a variety of sources and entering it into another system.
Finding a data entry clerk could be as simple as putting an ad in Kijiji or another online forum. You may also place your job posting at colleges and universities, as students may be the ideal candidates for data entry roles. Finally, you can look at offshoring your data entry, as overseas candidates may be able to provide data entry services for far less the domestic candidates.
Importing Data into Your Database
If you are migrating your data from a spreadsheet like Microsoft Excel, or if you are moving from one database system to another it’s often possible to import data directly. To perform this process there are 2 key requirements. First your existing data needs to be exported. Fortunately, most database systems and spreadsheets have the ability to export information.
A common export file format is CSV (comma delimited format). A CSV file is simply a text file where different data fields are separated by commas. Each record is usually on a different line. Since the file is text, it can be easily read by other programs.
Importing data into your database requires import functions in your database system. The import function in your database needs to be able to read the import file. You also need to tell the import function which fields the imported data needs to be entered in. For example, in our Tracker Ten program you can import a comma delimited file, and you can assign columns in the CSV file to go into specific fields in the Tracker Ten system using a simple drag and drop interface.
Some systems have API type features that you can interface with to extract information. These types of APIs let you programmatically make queries requesting data from the original data source. The extracted data is typically in JSON or XML format (i.e., machine readable formats). These file formats are similar to CSV in that the information is just text, but they allow for more sophisticated data structures. Interacting with these types of APIs can often safe you time, but the needed interaction often requires the services of a skilled software developer. If needed, we can provide API programming services.
If your existing data source cannot be automatically exported, it still might be possible to import it into your new database. However, this will require custom programming. If you have huge volumes of data, custom programming may be viable. If needed we can provide this type of custom programming. Please contact us for details.
Import via OCR (Optical Character Recognition)
If your data is on paper it may be possible to scan it into your computer, and use optical character recognition techniques to import information into your database. A scanner will take a picture of your document, and extract the text in the picture to a text file that can be automatically imported into another system. Feasibility of this approach depends on the exact format of your documents. If information in your documents is consistently entered in a spreadsheet type display, it will much easier to import. On the other hand, if it’s all free form text, it will be much more difficult to reliably import.
