On Wed, 29 Sep 1999, 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?
> > >
> >Do you mean fixed-length fields?
> >
>
> Yes,
> I've read the manual that fixed-sized rows not supported by LOAD DATA
> INFILE syntax, maybe there's another way to load the file into table.
Cahyo:
OK. Try something along these lines, using Perl and mysqlimport:
$ cat emp.dat
00001SMITH, JOHN 042500.00
00002TRAN, THUI 063750.00
00003GOMEZ, LUIS 054321.99
00004WEIL, KURT 049875.43
$ perl -e \
> 'while (<>) { print "$1|$2|$3\n" if /(.{5})(.{20})(.{9})/; }' \
> < emp.dat > emp.txt
$ mysql -e \
> 'CREATE TABLE emp(id INT, name VARCHAR(20), salary DECIMAL(8,2));' \
> test
$ mysqlimport --fields-terminated-by="|" --local test emp.txt
$ mysql -e 'SELECT * FROM emp;' test
+------+-------------+----------+
| id | name | salary |
+------+-------------+----------+
| 1 | SMITH, JOHN | 42500.00 |
| 2 | TRAN, THUI | 63750.00 |
| 3 | GOMEZ, LUIS | 54321.99 |
| 4 | WEIL, KURT | 49875.43 |
+------+-------------+----------+
The Perl script would look like this if you put it in a file (instead of
running from the shell, as I have done above):
#!/usr/bin/perl -w
while (<>) { print "$1|$2|$3\n" if /(.{5})(.{20})(.{9})/; }
It has one occurrence of (.{n}) for each field in your fixed-length
lines, where n is the number of columns occupied by that field, and it
writes the fields back out with the delimiter |, which is identified in
the --fields-terminated-by option for mysqlimport. You can use any
delimiter which doesn't occur in your data.
Hope this helps.
--
Bob Kline
mailto:bkline@stripped
http://www.rksystems.com
