List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:October 4 1999 12:34am
Subject:Re: Load text file into table
View as plain text  
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
Thread
Load text file into tableCahyo Tri Nugroho29 Sep
  • Re: Load text file into tableBob Kline29 Sep
    • Re: Load text file into tableCahyo Tri Nugroho29 Sep
      • Re: Load text file into tableBob Kline29 Sep
  • Re: Load text file into tablebpaduraru29 Sep
  • Re: Load text file into tableMartin Ramsch4 Oct