Professional Web Applications Themes

Trouble with truncation error when loading data from a file - MySQL

The table has many columns, but it includes a column like: CREATE TABLE f ( ... `x` DECIMAL(10,2) DEFAULT NULL, ... ) ENGINE=InnoDB; I use a statement like the following to load the data from a file. LOAD DATA INFILE "path to file" INTO TABLE f (list of columns); When I try to run this, I get an error 1265 "Data truncated for column "x" at row 3. There are thousands of rows in this file. The fields in the rows are tab delimited. Two consecutive tabs means there is no data for that specific field in that specific row. ...

  1. #1

    Default Trouble with truncation error when loading data from a file

    The table has many columns, but it includes a column like:

    CREATE TABLE f (
    ...
    `x` DECIMAL(10,2) DEFAULT NULL,
    ...
    ) ENGINE=InnoDB;

    I use a statement like the following to load the data from a file.

    LOAD DATA INFILE "path to file" INTO TABLE f (list of columns);

    When I try to run this, I get an error 1265 "Data truncated for column
    "x" at row 3.

    There are thousands of rows in this file.

    The fields in the rows are tab delimited. Two consecutive tabs means
    there is no data for that specific field in that specific row. The
    first two rows do not have any missing fields.

    I do not understand this. MySQL kows that data for field "x" may be
    missing, from the definition provided in creating the table, and that
    when this happens, the value ought to be set to null. So why is it
    crashing and burning on the first null value it encounters?

    How can I fix this so that I can reliably load data into my tables from
    these files, and be confident it properly handles missing values?

    Ted

    Ted Guest

  2. #2

    Default Re: Trouble with truncation error when loading data from a file

    Ted wrote:
    > LOAD DATA INFILE "path to file" INTO TABLE f (list of columns);
    >
    > When I try to run this, I get an error 1265 "Data truncated for column
    > "x" at row 3.
    >
    > How can I fix this so that I can reliably load data into my tables from
    > these files, and be confident it properly handles missing values?
    If you got data truncation while inserting to a DECIMAL column, I'd
    guess that the value contains illegal characters of some kind, that are
    not part of a numeric value. You should clean up your import file so
    that it contains legal values.

    One could apply some script (e.g. Perl) to the import file prior to
    loading it into the MySQL table. The script cleans up values to make
    sure they're legal for the format you're loading it into. Then it can
    either output a new data file for use with LOAD DATA, or else the script
    can use a database library to insert directly into the database (but
    LOAD DATA is undoubtedly faster for bulk loads).

    Another solution is to LOAD DATA to a temporary table that has only
    VARCHAR columns instead of DECIMAL, and then do some cleanup in SQL
    expressions, as you copy data from the temp table to your real table.

    In my experience, real-world data drops contains values that are not
    formatted consistently enough to be loaded directly into columns with
    strict format requirements, such as INTEGER, NUMERIC, DATE, ENUM, etc.,
    and there needs to be a "staging" load to provide an opportunity to
    clean up the data.

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default Re: Trouble with truncation error when loading data from a file

    If the file contains illegal characters, they are not visible when the
    file is opened in Notepad, and they appear to take up no space when
    using the right arrow key to move through a record one character at a
    time, but who knwos?

    I tried the option of creating a temporary file with the same column
    names, but all columns being defined as having type varchar(256) or
    varchar(45), depending on the field (I used the MEMORY engine and it
    didn't like the text type I occassionally use. But in this, too, I
    encountered trouble.

    The second column in the first table is a date with the format
    mm/dd/yyyy. We discussed this particular problem before. What I don't
    understand is that when loading this data into a table where everything
    is defined to be a varchar field, why would it care what the date
    format is. Should it not just treat that date field as any other
    string?

    As it is, no data gets loaded into the temporary tables.

    Do you have any simple examples of the perl scripts or "cleanup in SQL
    expressions" you mention?

    Thanks,

    Ted

    Ted Guest

  4. #4

    Default Re: Trouble with truncation error when loading data from a file

    Ted wrote:
    > The second column in the first table is a date with the format
    > mm/dd/yyyy. We discussed this particular problem before. What I don't
    > understand is that when loading this data into a table where everything
    > is defined to be a varchar field, why would it care what the date
    > format is. Should it not just treat that date field as any other
    > string?
    That is indeed odd. What error does it give?
    Can you insert one record to the table manually with an INSERT
    statement, using the values from one of the rows of the input file?

    Does this still get an error if you use a non-MEMORY table? A
    non-TEMPORARY table?
    > As it is, no data gets loaded into the temporary tables.
    >
    > Do you have any simple examples of the perl scripts or "cleanup in SQL
    > expressions" you mention?
    Something like the following (off the cuff, not tested).
    Supply the database name, user, and password where you see "...".

    #!perl -an

    use DBI;

    BEGIN {
    my $dsn = $dsn = "DBI:mysql:database=...";
    my $user = '...';
    my $password = '...';
    $dbh = DBI->connect($dsn, $user, $password);
    if (!defined($dbh)) {
    die("Error:" . $DBI::errstr . "\n");
    }
    $sth = $dbh->prepare("INSERT INTO mytable (a, b, c) VALUES (?, ?, ?)");
    if (!defined($sth)) {
    die("Error:" . $dbh->errstr . "\n");
    }
    }

    # Now the code executes for each line of input, thanks to "-n".
    # F is an array of the fields, auto-split by "-a".

    # modify elements of the array, for instance
    # reverse the characters of element 1:

    $F[1] = reverse($F[1]);

    # Execute our prepared INSERT statement, with the modified values:

    $sth->execute(F)
    or die("Error:" . $dbh->errstr . "\n");

    Regards,
    Bill K.
    Bill Karwin Guest

  5. #5

    Default Re: Trouble with truncation error when loading data from a file

    >> When I try to run this, I get an error 1265 "Data truncated for column
    >> "x" at row 3.
    >>
    >> How can I fix this so that I can reliably load data into my tables from
    >> these files, and be confident it properly handles missing values?
    >
    >If you got data truncation while inserting to a DECIMAL column, I'd
    >guess that the value contains illegal characters of some kind, that are
    >not part of a numeric value. You should clean up your import file so
    >that it contains legal values.
    It's easy to get data truncation inserting to a DECIMAL column.
    All it has to do is round the value. No illegal characters
    need be involved. For example:

    mysql> create table t1 ( a1 decimal(12,2) );
    mysql> insert into t1 values (4/3);
    mysql> show warnings;
    (OK, so I'm not drawing the cute boxes here)
    Note 1265 Data truncated for column 'a1' at row 1
    mysql> insert into t1 values (1.3456);
    mysql> show warnings;
    Note 1265 Data truncated for column 'a1' at row 1
    mysql>

    Gordon L. Burditt
    Gordon Burditt Guest

  6. #6

    Default Re: Trouble with truncation error when loading data from a file

    Well, so far I have been able to verify that the data is clean: no
    bogus characters.

    I loaded the file into Open Office'sspreadsheet. It properly detected
    that the data file is a tab delimited, plain text file, and loaded and
    displayed the data without incident.

    The error I get with the temporary files having only varchar fields is
    that 11/22/1999 is an invalid value for a date. I don't understand
    this since the table has only varchar fields.

    I CAN insert data, a record at a time using the query browser!

    I'll work on the other questions you asked over the weekend.

    Ted

    Ted Guest

  7. #7

    Default Re: Trouble with truncation error when loading data from a file

    In my case, the issue seems to be related to the fact the field is
    empty. All of the fields in all of the records prior to the problem
    record, and all of the fields on the problem record prior to the
    problem field, have proper data values. The field causing the problem,
    on the record causing the problem, is empty (represented in the data
    file by consecutive tabs).

    Are there known issues in MySQL WRT how LOAD DATA INFILE handles
    missing values?

    Ted

    Ted Guest

  8. #8

    Default Re: Trouble with truncation error when loading data from a file

    The problem occurs regardless of the DB engine I use or whether or not
    the table is temporary.

    The error message is "Incorrect DATETIME value 11/22/1999 at line 1"
    and the error code is 1292. Remember, this is when trying to load the
    data into a temporary table with ALL of the fields defined as VARCHAR
    of varying lengths.

    Since I can load the file into a spreadsheet with Open Office, I know I
    can create a Java program to load the data into memory, and then use
    the java program to load the data into MySQL from the java program, but
    I have been resisting that options since it is bound to be as slow as
    molasses in January. The largest table has 26 fields and several
    thousand records. I am now expecting to have to sanitize the data
    anyway since, on loading the data into a spreadsheet, I have learned
    that there are some problems including the fact that the date fields
    are inconsistently formatted (especially in regard to the number of
    characters used to represent the year).

    Ted

    Ted Guest

  9. #9

    Default Re: Trouble with truncation error when loading data from a file

    Hi Ted,

    Let me share my experience with you: I have stumbled on the exact same
    problem, with sometimes millions of warnings when loading geographical
    data into a table with decimal fields. In the end, it is no big deal:
    either the input data has more decimal digits than your destination
    field and it gets properly truncated, or it's empty and gets converted
    to 0. Depending on your application requirements, you might want to
    change the 0 values to NULL after loading the initial data.

    Warning 1265 is really just a note to get your attention at the loss of
    precision; after you yze the situation and understand what is
    happening, you can safely ignore it.

    Hope this helps,

    JFG

    jfgroff@gmail.com Guest

  10. #10

    Default Re: Trouble with truncation error when loading data from a file

    But how do you ignore it when the warning or error stops the process of
    loading the data entirely. This database has 28 fields and several
    thousand records. The third record has an empty value for one field,
    and that is the field that gives the message about data being
    truncated. After I read the error message, I look in the table and I
    find only the first two records!

    As I said before, I loaded the data into my spreadsheet application and
    examined the data. There are no illegal chacacters that I can find,
    but the field and record containing it are the first in the database.

    Ted

    Ted Guest

  11. #11

    Default Re: Trouble with truncation error when loading data from a file

    You can skip over problem records by saying "LOAD DATA INFILE IGNORE
    ....". Warnings and errors will be counted but will not halt processing.

    JFG

    JFG Guest

Similar Threads

  1. Loading data from xml file
    By sammyfb in forum Macromedia Flash Data Integration
    Replies: 1
    Last Post: January 28th, 02:08 PM
  2. Data Truncation error
    By d.acha in forum Coldfusion Database Access
    Replies: 12
    Last Post: October 5th, 01:57 AM
  3. Data truncation in SELECT
    By myersonconsulting in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 7th, 04:57 PM
  4. loading data through html file
    By Ivanek in forum Macromedia Flash Actionscript
    Replies: 5
    Last Post: February 24th, 04:10 PM
  5. Loading data from .txt file
    By chrism59 in forum Macromedia Flash Actionscript
    Replies: 2
    Last Post: February 10th, 01:52 AM

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