load data local infile - MySQL

[Lee]

I use the subject command quite often to load .cvs files into tables
and am very pleased with the speed.

The "terminated by ','" is, of course what is used. After some
additional study, it appears that practically any character can be
used here if that character was what was used when the "text" file was
created.

My question is, is there a way to do this on fixed length files?


A little background. I have hundreds of old BTrieve files that I need
to move to MySQL. Current process is to read each row in the BTrieve
file and construct a "properly formated .cvs" file. Then use "load
data local infile" to load the table.

BTrieve comes with a utitility called "butil" that has an option to
"-save" the data in an unformated text file, but this file is a fixed
length.

Butil is considerably faster than reading each line and formatting it;
however, unless "load data" or some other MySql command can be used to
load the fixed length data, then I'm sure any time gained will be lost
by having to read and format each line of the butil generated file.

Any ideas?

Lee

[Captain]

On 8 Feb, 12:31, Lee Peedin <rr.com> wrote: 
 
data.html):

If the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values are both
empty (''), a fixed-row (non-delimited) format is used. With fixed-row
format, no delimiters are used between fields (but you can still have
a line terminator). Instead, column values are read and written using
a field width wide enough to hold all values in the field. For
TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT, the field widths are 4,
6, 8, 11, and 20, respectively, no matter what the declared display
width is.

LINES TERMINATED BY is still used to separate lines. If a line does
not contain all fields, the rest of the columns are set to their
default values. If you do not have a line terminator, you should set
this to ''. In this case, the text file must contain all fields for
each row.

Fixed-row format also affects handling of NULL values, as described
later. Note that fixed-size format does not work if you are using a
multi-byte character set.

Note: Before MySQL 5.0.6, fixed-row format used the display width of
the column. For example, INT(4) was read or written using a field with
a width of 4. However, if the column contained wider values, they were
dumped to their full width, leading to the possibility of a "ragged"
field holding values of different widths. Using a field wide enough to
hold all values in the field prevents this problem. However, data
files written before this change was made might not be reloaded
correctly with LOAD DATA INFILE for MySQL 5.0.6 and up. This change
also affects data files read by mysqlimport and written by mysqldump --
tab, which use LOAD DATA INFILE and SELECT ... INTO OUTFILE

[Lee]

On 8 Feb 2007 04:49:48 -0800, "Captain Paralytic"
<com> wrote:
 
>data.html):
>
>If the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values are both
>empty (''), a fixed-row (non-delimited) format is used. With fixed-row
>format, no delimiters are used between fields (but you can still have
>a line terminator). Instead, column values are read and written using
>a field width wide enough to hold all values in the field. For
>TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT, the field widths are 4,
>6, 8, 11, and 20, respectively, no matter what the declared display
>width is.
>
>LINES TERMINATED BY is still used to separate lines. If a line does
>not contain all fields, the rest of the columns are set to their
>default values. If you do not have a line terminator, you should set
>this to ''. In this case, the text file must contain all fields for
>each row.
>
>Fixed-row format also affects handling of NULL values, as described
>later. Note that fixed-size format does not work if you are using a
>multi-byte character set.
>
>Note: Before MySQL 5.0.6, fixed-row format used the display width of
>the column. For example, INT(4) was read or written using a field with
>a width of 4. However, if the column contained wider values, they were
>dumped to their full width, leading to the possibility of a "ragged"
>field holding values of different widths. Using a field wide enough to
>hold all values in the field prevents this problem. However, data
>files written before this change was made might not be reloaded
>correctly with LOAD DATA INFILE for MySQL 5.0.6 and up. This change
>also affects data files read by mysqlimport and written by mysqldump --
>tab, which use LOAD DATA INFILE and SELECT ... INTO OUTFILE[/ref]

Thank you for the detailed explaination - all is well now and have cut
my BTrieve to MySQL transfer time by 75%!

Thanks
Lee