RE: Importing Fixed Length Text Files

[Allen Weeks]

Thanks for the response, it was timely and right on the money.

Allen
  -----Original Message-----
  From: Daniel Kasak [mailto:dkasak@stripped]
  Sent: Wednesday, May 05, 2004 6:22 PM
  To: Allen Weeks; MySQL List
  Subject: Re: Importing Fixed Length Text Files


  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: ([phone] / F: ([phone]
  email: dkasak@stripped
  website: http://www.nusconsulting.com.au