Don’t Assume LOAD DATA Knows More than It Does
Problem
You think LOAD
DATA is smarter than it really is.
Solution
Don’t assume that LOAD
DATA knows anything at all about the format of
your datafile. And make sure you yourself know what its format is. If
the file has been transferred from one machine to another, its
contents may have been changed in subtle ways of which
you’re not aware.
Discussion
Many LOAD DATA frustrations
occur because people expect MySQL to know things that it cannot
possibly know. LOAD DATA makes
certain assumptions about the structure of input files, represented
as the default settings for the line and field terminators, and for
the quote and escape character settings. If your input
doesn’t match those assumptions, you need to tell
MySQL about it.
When in doubt, check the contents of
your datafile using a hex dump program or other utility that displays
a visible representation of whitespace characters like tab, carriage
return, and linefeed. Under
Unix, the
od program can display file contents in a
variety of formats. If you don’t have
od or some comparable utility, the
transfer directory of the
recipes distribution contains hex dumpers written
in Perl and Python (hexdump.pl and
hexdump.py), as well as a couple of programs
that display printable representations of all characters of a file
(see.pl and see.py). You may find them useful for examining files to see what they really contain. In some cases, you may be surprised to discover that a file’s contents are ...