Allen Weeks wrote:
>Hello All,
>
>Maybe I am not searching the documentation correctly or am I correct in
>finding there is not method of directing importing a fixed length text file
>into a MySQL table?
>
>I hope I just missed the reference. Could someone point me to it.
>
>Any assistance is greatly appreciated.
>
>Allen
>
>
It's possible, but it's a hassle.
It's documented in:
http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html
About half way down the page, it says:
> 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.
So you have to set up a table with the exact field lengths in the
'create table' definition, and then use 'load data infile' with the
options 'fields terminated by' and 'fields enclosed by' both empty
strings ( '' ).
Dan
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@stripped
website: http://www.nusconsulting.com.au
Attachment: [text/html]
Attachment: [text/html]
