MySQL Cookbook Example -

One of the tools included in the MySQL Cookbook recipes distribution is a Perl script named that diagnoses where problems occur in data files that you load with the LOAD DATA statement. The following example is adapted from MySQL Cookbook and illustrates how works. (You can obtain the recipes distribution from the downloads page.)

Assume you have a simple table named diag_test that contains string, date, and number columns:

     CREATE TABLE diag_test
         str     CHAR(10),
         date    DATE,
         num     INT

Assume you also have a data file named diag_sample.dat that you plan to load into the table:

     str1    01-20-2001      97
     str2    02-28-2002
             03-01-2002      64      extra junk

To see if the file will have any problems loading, check it like this:

     % cookbook diag_test diag_sample.dat
     line 1: 1 warning
       column 2 (date): bad value = (01-20-2001)
     line 2: 2 warnings
       too few columns
       column 2 (date): bad value = (02-28-2002)
       column 3 (num): missing from input line
       column 3 (num): bad value = () (inserting NULL worked better)
     line 3: 1 warning
       excess number of columns

     Number of lines in file: 3
     Warnings found when loading entire file: 4
     Lines containing too few column values: 1
     Lines containing excess column values: 1

     Warnings per column:

     Column    Times      Total     Warnings for     Improved
              missing    warnings   empty columns    with NULL
     str            0           0               0            0
     date           0           2               0            0
     num            1           1               1            1

The output from indicates on a line-by-line basis where problems occur in the data file, and also contains a general summary of the problems that were found.

Last modified: July 29, 2014
[MySQL Cookbook main page]
[MySQL Cookbook examples page]