On Wed, 1999-09-29 07:31:17 +0700, Cahyo Tri Nugroho wrote:
> I have a text file (size about 7 Mb) which between it's fields (more than
> 20 fields) there's no tab or space.
> How to load this file into table?
If you mean data with fixed-length fields, and if you can't or don't
want to convert the data into tab-seperated format first, you can
import such data in basically these two steps:
1st, import complete lines into an additional field of your table
2nd, update all other fields with the respective part of the data
For this method you need temporarily twice the disk space of the
actual data, of course.
Example:
----- data.txt --------------- This file holds lines
0001 Martin Ramsch 123 with four fields each
0002 Bob Kline 456 and the fields have fixed
------------------------------ lengths 4, 10, 10, 6.
CREATE TABLE mytable (
importline CHAR(30),
id INT,
firstname CHAR(10),
lastname CHAR(10),
msgid INT
);
LOAD DATA
LOCAL INFILE 'data.txt'
INTO TABLE mytable
FIELDS TERMINATED BY ''
ESCAPED BY ''
(importline)
;
UPDATE mytable
SET id = SUBSTRING(importline, 1, 4)
, firstname=TRIM(SUBSTRING(importline, 5,10))
, lastname =TRIM(SUBSTRING(importline,15,10))
, msgid = SUBSTRING(importline,25, 6)
;
ALTER TABLE mytable DROP COLUMN importline;
Regards,
Martin
--
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=5244 5EF3 B0B1 3826 E4EC 8058 7B31 3AD7
