In using databases we tend to move around a lot of data. So much data, in fact, that it’s impossible to verify every single datum in a manual way. What we can and should do is verify that the beginning, middle and ends of the data are intact.
I ran into a solution for a problem that I didn’t know I had until I checked to see that data I had imported into an existing MySQL table was imported properly. Data at the beginning of the new table mirrored the CSV nicely, but near the middle of the table I found that certain columns of data were whole numbers, not the decimal figure I was supposed to find.
It’s important to check the format of numerical values. By using the wrong data type when setting up your database you stand to lose precision of your numbers.
For example, when I set up a database I erroneously used the INT data type for a couple of values that needed to show the decimal places. Integers are whole numbers, and therefore, a value like 5.78 in the CSV showed up as 6 in the database table. The software did exactly what I asked it to do, which is a good reason to always verify the data after any manipulations.
The fix was to change the structure of the fields by changing the data ‘Type’ from INT to DECIMAL and specifying the ‘Length/Values’ in the format of ‘N,n’ where N is the total number of digits and n is the number of decimal places. So, for my example of 5.78, I entered “DECIMAL” and “3,2”.
Then, I imported the data again and ta-da! All the data was there in the proper format.