Professional Web Applications Themes

load data local infile - MySQL

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

  1. #1

    Default load data local infile

    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
    Lee Guest

  2. #2

    Default Re: load data local infile

    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

    Captain Guest

  3. #3

    Default Re: load data local infile

    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
    Lee Guest

Similar Threads

  1. LOAD DATA INFILE
    By Luca in forum MySQL
    Replies: 4
    Last Post: July 11th, 12:35 PM
  2. Complex LOAD DATA INFILE
    By stefaan.lhermitte@agr.kuleuven.ac.be in forum MySQL
    Replies: 6
    Last Post: May 18th, 06:34 PM
  3. help : LOAD DATA LOCAL INFILE
    By Dave in forum PHP Development
    Replies: 3
    Last Post: October 5th, 12:22 AM
  4. LOAD DATA LOCAL INFILE and Perl DBI/DBD
    By Jami Bradley in forum PERL Modules
    Replies: 1
    Last Post: June 25th, 03:08 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139