Format of Numerical Values Matters to Your MySQL Database

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”.

Example of DECIMAL and INT numeric formats.

Then, I imported the data again and ta-da! All the data was there in the proper format.

How to Import a Data File into MySQL via phpMyAdmin

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.

Example character separated values or CSV file.

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.

The Import tab of phpMyAdmin.

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

The Import page in phpMyAdmin.

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.

Select the CSV button to open this part of the import page.

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.

MySQL LOAD FILE Statement Rescues Huge Database File

A client needed to use an existing database, which turned out to be a big gargantuan thing, but to be able work with it on the Web I needed to convert it from MS Access to MySQL.

Instead of trying to convert the Access files to MySQL I chose to work with delimited text files that were available. Ten text files were used to represent ten tables for the database. It was easy enough to read in the delimited text files into OpenOffice Calc by identifying the delimiting characters and importing the new .csv file into MySQL, but one file in particular was really big and presented a problem.

I tried to work with the big text file in Notepad and HTML-Kit, but it was too cumbersome due to the file size – over 500,000 records! Not enough copy and paste operations could be done in a day to make me want to go through it again when the original database gets updated and I have to convert the scary big text file into MySQL format again. Knowing that the original database can be updated at any time, I needed a way to get that huge file into MySQL with a minimum of effort.

After looking around a bit, I downloaded a piece of conversion software called Full Convert from SpectralCore.

Installation went smoothly and I noted there was an uninstall feature in case the software wasn’t a good fit for my needs. It seemed promising in that there are several databases that can be converted back and forth, but the function to convert a text file to MySQL tables did not work for me. The text file used the characters ‘^’ to terminate fields and ‘~’ to enclose fields. However, the software did not give a choice for the field-enclosing character.

Since it didn’t work for me I uninstalled it, but the Full Convert software may be promising if you’re moving files from one database system to another. Here’s the list of DBs they work with:

Supported source databases
– Microsoft Access, dBase, FoxPro, Microsoft Excel documents, Interbase / Firebird, Lotus 1-2-3, MySQL, Oracle, PostgreSQL, ODBC sources, Paradox, SQL Server, Delimited text files, XML

Supported target databases
– MySQL, SQL Server, Oracle, PostgreSQL, Access, Interbase/Firebird

Further digging lead me to a solution for this problem of not being able to import a really big, delimited text file into a table in MySQL.

LOAD DATA INFILE

The solution is to read the file directly into MySQL using the MySQL statement “LOAD DATA INFILE”.

For my situation the code that worked was this –

LOAD DATA INFILE ‘data.txt’ INTO TABLE databasename.tablename FIELDS TERMINATED BY ‘^’ ENCLOSED BY ‘~’;

Don’t forget the trailing semi-colon and don’t use single quotes around the database and table names (and leave the semi-colon in the space provided for the Delimiter).

How did I find my solution? Searching at WebmasterWorld for “convert text to mysql” did the trick.

A simple post by a fellow developer that was written to help someone else out a couple years ago helped lead me to the LOAD_DATA function. Thanks, dreamcatcher!

Calendar Pick-A-Date Scripts Reviewed

This week I needed to make a form where the user needed to enter two dates in order to produce a report with the selected start and end dates. What were my options?

I considered writing my own php script where I would have separate pull-down boxes for the day, month and year to assure that there was no mistake as to the actual dates. That would take a little extra format massaging to create the date in MySQL format, so I looked for scripts that others had already written. Who wouldn’t?

Formatting dates can drive a person crazy. Does ’06-08-2000′ mean June 8th or the 6th of August? Writing for the Web should be universal. The month-day format that we tend to use in the U.S. is confusing to everybody else who uses the day-month format, and vice-versa.

Writing by hand I like to use this format ’12-Mar-2000′ so there is no ambiguity, but that format needs to be converted to ‘YYYY-MM-DD’ format when sending dates to MySQL.

So, what are my requirements for such a Date-Picker Script?

  • easy, intuitive for the user
  • easy for me with output in MySQL format (YYYY-MM-DD)
  • unrestricted license would be nice

I tried a few different date picker scripts and decided upon SpiffyCalendar because it’s the most advanced, yet easily modified to suit my purposes. Also, there is an established support system via a Yahoo! user group. That’s really nice, and unexpected.

Here’s the scripts I tried and the pros and cons for each…most weren’t as user-friendly as SpiffyCalendar.

 

PHP Calendar

    PROS

  • I like the look of this calendar, but it’s difficult to determine how to modify the calendar other than for css of calendar appearance.
  • Date output is already in MySQL format: YYYY-MM-DD
    CONS

  • Can’t select year from calendar unless you click back or forward to the actual month.
  • Ugly and excessively long query strings in links.

DatePick

    PROS

  • I like the little calendar icon.
    CONS

  • Can’t select year from calendar unless you click back or forward to the actual month.
  • Timestamp is included. Don’t need it.
  • Relies on javascript and pop-up window to select date.
  • Calendar doesn’t update well when moving from month to month. Part of the calendar doesn’t get loaded in so all days are not selectable. Have to manually widen window to see all days.
  • Can’t select year directly, have to click through months to get to next year.
  • No directions on how to modify output.

DateTimePick

    PROS

  • I like the little calendar icon.
  • Can modify output via provided directions.
    CONS

  • Can’t select year from calendar unless you click back or forward to the actual month.
  • Relies on javascript and pop-up window to select date.
  • Calendar doesn’t update well when moving from month to month. Part of the calendar doesn’t get loaded in so all days are not selectable. Have to manually widen window to see all days.
  • Can select year directly, but calendar doesn’t update well.

SpiffyCalendar

Sign up to the Yahoo! Group to download the script. Script used to be offered at the now defunct GeoCities. The above link is the first place I found it posted afterwards.

    PROS

  • Calendar pops into page, not a separate window.
  • Can modify output via provided directions.
  • Can get help at Yahoo! Groups.
  • Unrestricted license.
  • Can refresh page containing calendars as link is not modified after picking a date or two.
  • Highlights holidays, although will have to remove Dec 1st developer’s birthday. (OK, modified holiday array in script on lines 86-87.)
    CONS

  • Format of date will have to be modified for MySQL. (OK, Added "cal1.dateFormat="yyyy-MM-dd"; after instantiation in header for calendar named ‘cal1’.)
  • Relies on javascript.
  • Had to modify the images folder location in the .js file to an absolute address, although things worked fine on my local server with a relative address.

I’m sure there are plenty of these date-picker scripts floating around the Web. Do you have a different one to share with us? Leave a comment!