Author: LizzyFin

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.

Sometimes we have a need to read data into an existing database. Knowing how to import a data file into an established table can save you a lot of time.

First, we’ll be working with MySQL and to make things easier we’ll use phpMyAdmin to speak to the database.

Second, obtain a delimited text file, which is basically a listing of table columns where each column of data is separated by, or marked off by, or delimited by a character. Common delimiting characters are the comma, semi-colon and quote marks, but any character may be used as long as you indicate what the delimiters are.

In this example the tilde (~) encloses each field of text and the carat (^) denotes the end of the fields.

In phpMyAdmin select your database and table into which you want to import data and click on the Import tab.

Browse to the location of your delimited text file and select the character set of the file if the default is not correct.

Under “Format of imported file”, click CSV, which stands for comma separated values file or character separated values file. An options form will open when CSV is chosen, but if SQL is checked the options form remains hidden.

Under “Options” you can tick off the boxes for ‘Replace table data with file’ and ‘Ignore duplicate rows’ if either function is desired.

Enter the characters that are appropriate for your CSV under ‘Fields terminated by’ and ‘Fields enclosed by’. In the example file above ‘^’ was used for the field termination character and ‘~’ for the field enclosure character.

Characters for escaping fields or terminating lines can be input here, too. Also, if you want to add column names enter the names separated by commas in the blank provided. When ready, hit “Go”.

If successful, you’ll get a message that X number of queries were executed, which means that X number of records were imported into your table. If not successful, check the number of columns and the characters that you’ve input for the CSV and try again.

Your system may limit the size of file that can be imported. If so, check out this LOAD FILE post for a little help.

As always, verify that the data was read into the database correctly. Check that the data for each column of the table matches up with the source delimited text file.