Shawn, Thanks a lot for this explanation. It works perfectly. Best, Adam
[EMAIL PROTECTED] wrote:
While reading http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html
I found this passage
"
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 written and read using the "display'' widths of the columns. For example, if a column is declared as INT(7), values for the column are written using seven-character fields. On input, values for the column are obtained by reading seven characters. LINES TERMINATED BY is still used to separate lines. If a line doesn't contain all fields, the rest of the columns are set to their default values. If you don't 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 will not work if you are using a multi-byte character set. "
To help interpret that passage, if you have a fixed-width column file, you need to define an input table that matches that format. Then you can LOAD DATA INFILE from that file into that table. Once you get the records into MySQL you can copy them into a better designed table for permanent use.
CREATE TABLE fixedImport1 ( FIELD1 varchar(10), FIELD2 varchar(10), FIELD3 char(1), FIELD4 varchar(19), ... );
LOAD DATA INFILE 'myfile.data' INTO fixedImport1 FIELDS TERMINATED BY '' FIELDS ENCLOSED BY '';
At least that's how I read it. Let us know if you run into any problems, OK?
Shawn Green Database Administrator Unimin Corporation - Spruce Pine
news <[EMAIL PROTECTED]> wrote on 11/12/2004 12:08:25 PM:
Hi,
I have a text file with fixed-width columns that I'd like to get loaded into a table. Against Oracle the script would be as follow:
LOAD DATA INFILE 'myfile.data' append INTO TABLE MYTABLE ( FIELD1 POSITION(1:10), FIELD2 POSITION(11:20), FIELD3 POSITION(21:21), FIELD4 POSITION(22:40) )
Is there an equivalent to the Oracle "POSITION" parameter in MySQL? Or any MySQL add-on that makes such a load possible?
Adam
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
