I am attempting to load personal files where are the lines make use of the same rules. (assume HEADER is really a single line)
HEADER1 HEADER2 .......
But unluckily after i use the burden DATA INFILE statement I recieve this error: Error Code: 1409 Can't load value from file with fixed size rows to variable.
This is actually the code I authored:
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'd this concept reading through the foot of this page (comment by Ramam Pullella), and that i found exactly the same described on some websites, however i can't realise why I am getting this error.
Basically avoid using the @var_date_one and @var_date_two variables, so the STR_TO_DATE function, the date is not made as MySql needs - the date within the file is one thing like "20100701" - then that area would contain all zeros or perhaps a different date than I am expecting. Basically change DLP and DUVI to become symbolized by CHAR(8), it works, however i will not make use of the SQL DATE evaluations and other alike tools.
Are you able to assist me to please? :) Thanks greatly.
It appears the issue is distributed by the road Ended BY '', because this type of lines are a "fixed row (undelimited)". Maybe it cannot be designated to variable to have an unknown reason, but it is by doing this it really works. The documentation states:
User variables can't be used when loading data with fixed-row format because user variables don't have a display width.
RE-EDIT: I have browse the comment by Ryan Neve at bottom of this page. He provides a trick to see 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 consider it's a great way to get it done? :)
I am no expert, however it appears in my experience when the fields are ended by a clear string, then they need to be fixed size rather there needs to be a way to look for the limitations between fields, and when there's no terminator, they virtually need to be fixed size.
I realize that the MySQL 5.5 manual states:
- User variables can't be used when loading data with fixed-row format because user variables don't have a presentation width.
Additionally, it (rather previously the page) states:
- When the FIELDS Ended BY and FIELDS ENCLOSED BY values are generally empty (''), a set-row (nondelimited) format can be used. With fixed-row format, no delimiters are utilized between fields (however, you can continue to possess a line terminator). Rather, column values are read and written utilizing a area width wide enough to carry all values within the area. For TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT, the area sizes are 4, 6, 8, 11, and 20, correspondingly, regardless of what the declared display width is.
As your statement doesn't have 'FIELDS ENCLOSED BY' and empty 'FIELDS ENCLOSED BY', that's how you get a set format. And therefore you can't do as you would like.
Sometimes, it's simpler to massage the information outdoors the DBMS - fixing the information representation may be one particular operation. I actually do have program which i call DBLDFMT that I have not employed for a couple of years, however it can perform a number of procedures, for example convert decimal amounts with implicit decimal points (a mainframe trick the cost area may be 0023199, representing the worthiness £231.99). It may cope with date manipulations too (not always utilizing a particularly user-friendly notation, but with the ability to cope with the issues I faced getting data from mainframes right into a Unix DBMS - not MySQL it did not exist after i authored this code. Get in touch if that could be associated with a interest - see my profile.