MySQL: how to load data in fixed line format into user variables

Question

I am trying to upload a file where all lines use the same rules. (suppose HEADER is the only line)

HEADER1 HEADER2 ....... 

But unfortunately, when I try to use the LOAD DATA INFILE statement, I get this error: Error code: 1409 It is not possible to load a value from a file with fixed-size strings into a 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 (Ramam Pullella's comment), and I found the same explanation on some sites, but I cannot understand why I am getting this error.

If I do not use the variables @var_date_one and @var_date_two, and therefore the function STR_TO_DATE, the date is not displayed as MySql needs - the date in the file is similar to "20100701" - then this field will contain all zeros or a different date than I expect. If I modify DLP and DUVI to be represented by CHAR (8), then it works, but I will not use SQL DATE comparisons and similar tools.

Can you help me? :) Thank you so much.

EDIT:

The problem seems to be set by LINE TERMINATED BY '', since this type of string is "fixed string (unsupported)". It may not be assigned to a variable for an unknown reason, but this is how it works. The documentation states:

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

Any suggestion?

RE-EDIT: I read Ryan Neve's comment at the bottom of this page. It gives a trick for reading a fixed string 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 this is a good way to do this? :)

Answer 1

I am not an expert, but it seems to me that if the fields end with an empty string, then they should be a fixed size; there must be some way to define the boundaries between the fields, and if there is no terminator, then to a large extent they should be fixed.

I observe that the MySQL 5.5 manual says:

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

He also (rather on the page) says:

• If the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values ​​are empty (''), the fixed-line format (unshared) is used. In the fixed-line format, no separators are used between the 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 the values ​​in the field. For TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT, the field width is 4, 6, 8, 11, and 20, respectively, regardless of the declared display width.

Since your operator does not have "FIELDS ENCLOSED BY" and empty "FIELDS ENCLOSED BY", so you have a fixed format. And so you cannot do what you want.

---

Sometimes it’s easier to massage the data outside the DBMS - correcting the presentation of the data can be one of such operations. I have a program that I call DBLDFMT, which I have not used for several years, but it can perform various operations, such as converting decimal numbers with implicit decimal points (mainframe tap, price field can be 0023199, representing a value of £ 231.99). It can also deal with date processing (not necessarily using a particularly user-friendly notation, but it is able to cope with the problems that I had to get data from mainframes in Unix DBMS and not MySQL, and did not exist when I wrote this contact with me, if that might be interesting - 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 that MySQL will by default separate.

In that case, you can just tell MySQL that your field separator is something stupid. eg:

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

Thus, the entire row is placed in the first "column", that is, in your user variable. Then you can use it exactly as shown in your code above.

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