MySQL : how to load data with fixed-row format into user variables

Question

I'm trying to load a file where are all the lines use the same rules. (assume HEADER is a single line)

HEADER1
HEADER2
.......

But unluckily when I try to use the LOAD DATA INFILE statement I get this error: Error Code: 1409 Can't load value from file with fixed size rows to variable.

This is the code I wrote:

USE test;
DROP TABLE IF EXISTS EXAMPLE_H;
CREATE TABLE EXAMPLE_H(
    ID CHAR(20),
    SP CHAR(3),
    IVA CHAR(11) PRIMARY KEY,
    NLP CHAR(6),
    DLP DATE,
    DUVI DATE,
    DELP CHAR(30),
    FILLER CHAR(39),
    VTLP CHAR(3),
    FILL CHAR(49)
);

LOAD DATA INFILE 'BTILSP.TXT' 
    INTO TABLE test.EXAMPLE_H
    FIELDS TERMINATED BY ''
    LINES TERMINATED BY '\n'
    (ID, SP, IVA, NLP, @var_date_one, @var_date_two, DELP, FILLER, VTLP, FILL)
    SET DLP = str_to_date(@var_date_one, '%Y%m%d',
        DUVI = str_to_date(@var_date_two, '%Y%m%d');

I had this idea reading the bottom of this page (comment by Ramam Pullella), and I found the same explained on some websites, but I can't understand why I'm getting this error.

If I don't use the @var_date_one and @var_date_two variables, and so the STR_TO_DATE function, the date isn't rendered as MySql needs - the date in the file is something like "20100701" - then that field would contain all zeros or a different date than what I'm expecting. If I change DLP and DUVI to be represented by CHAR(8), then it works, but I won't use the SQL DATE comparisons and similar tools.

Can you help me please? :) Thank you very much.

EDIT:

It seems the problem is given by the LINE TERMINATED BY '', since this kind of line is a "fixed row (undelimited)". Maybe it can't be assigned to variable for an unknown reason, but it's this way it works. The documentation says:

User variables cannot be used when loading data with fixed-row format because user variables do not have a display width.

Any suggestion?

RE-EDIT: I've read the comment by Ryan Neve at bottom of that page. He gives a trick to read fixed-row into variables:

LOAD DATA LOCAL INFILE '<file name>' INTO TABLE <table>
(@var1)
SET Date=str_to_date(SUBSTR(@var1,3,10),'%m/%d/%Y'),
Time=SUBSTR(@var1,14,8),
WindVelocity=SUBSTR(@var1,26,5),
WindDirection=SUBSTR(@var1,33,3),
WindCompass=SUBSTR(@var1,38,3),
WindNorth=SUBSTR(@var1,43,6),
WindEast=SUBSTR(@var1,51,6),
WindSamples=SUBSTR(@var1,61,4);

Do you think it's a good way to do it? :)

Answer 1

I'm no expert, but it seems to me that if the fields are terminated by an empty string, then they have to be fixed size instead; there has to be some way to determine the boundaries between fields, and if there is no terminator, then they pretty much have to be fixed size.

I observe that the MySQL 5.5 manual says:

• User variables cannot be used when loading data with fixed-row format because user variables do not have a display width.

It also (rather earlier on the page) says:

• If the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values are both empty (''), a fixed-row (nondelimited) 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.

Since your statement has no 'FIELDS ENCLOSED BY' and empty 'FIELDS ENCLOSED BY', that is why you have a fixed format. And hence you cannot do as you want.

---

Sometimes, it is easier to massage the data outside the DBMS - fixing the data representation might be one such operation. I do have program that I call DBLDFMT that I've not used for a few years now, but it can do a variety of operations, such as convert decimal numbers with implicit decimal points (a mainframe trick; the price field might be 0023199, representing the value £231.99). It can deal with date manipulations too (not necessarily using a particularly user-friendly notation, but it is able to deal with the problems I faced getting data from mainframes into a Unix DBMS - not MySQL; it didn't exist when I wrote this code. Contact me if that might be of any interest - see my profile.

Answer 2

In case someone else comes across this. If you just run

LOAD DATA LOCAL INFILE '<file name>' INTO TABLE <table> (@var1) SET ...

without specifying FIELDS TERMINATED BY, and your file contains commas MySQL will split on those by default.

In such case you can just tell MySQL that your field delimiter is something silly. eg:

FIELDS TERMINATED BY '@@@@@@@@@@@@'

This way the whole line gets put in the first "column" ie your user variable. You can then use it exactly as shown in your code at the top.

It's worth noting MySQL treats delimiter a string, so you can even have
FIELDS TERMINATED BY 'this_string_thoes_not_appear_in_my_file if you want