Thanks for the response, it was timely and right on the money. Allen -----Original Message----- From: Daniel Kasak [mailto:[EMAIL PROTECTED] 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: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au
