Some of the most common errors during importation of data and how to work around them

Most common error message when importing data and how to resolve them.

One of the hardest challenges when working with a database management system is mastering the art of importing data.

Databases have built in rules that prevent erroneous data form entering the system and a set of common error messages that are meant to guide the users when “dirty” data cannot be inserted into the system.

These error messages can be difficult to understand for a person that does not have a background in databases, and these are the most common error messages, what they mean and how to get around them.

1. PK violation (Primary Key Violation Error)
   
   
   
   
   1. As we can see from the error message above, databases unfortunately send very long and wordy error messages.
   2. But if we know what we are looking for, it can be very easy to know what is going wrong.
   3. The only part we need from the entire block of text is the following: "Violation of the PRIMARY KEY constraint, cannot insert duplicate key"
   4. This error message simply states that we cannot insert the current record, because it is already in the database and the database cannot creates duplicates.
      
      
2. DateTime format is incorrect
   
   
   1. Unfortunately, databases require date and time data in a specific format.
      
      
      
      
   2. As we can see from the error message, they database was unable to use the date in the format that we supplied it, in the file.
   3. To check what format is required, you can click on the Config button in Geobank and in the configuration manager look for the DataSource's item.
      
      
      
      
   4. Right click on the data source that is in use and select properties from the menu
      
      
      
      
   5. You can navigate on the left to the Environment Settings and see the date format at the top of the screen. 
   6. Update the data in the sheet to match the format and this will resolve the error.
      
      
3. String or Binary data will have to be truncated.
   
   
   
   
   1. The columns in a database table have a set number of characters that they can store.
   2. If we try to push data into a column that goes over the maximum number of characters that can be stored in that column, the database will produce this error.
   3. In the event that we receive this error, we will have to go back into our file and find the column where the characters are too much.
   4. The visual query builder in Geobank can be used to assist with this, as it does tell us the number characters allowed in every column of a table as can be seen below.
      
      

Want to learn more?

Online Help Manuals - Click here for the latest version

Learning Management System - Click here to login or here to request access