Importing MYSQL table from text file wo column delimiters

Latios · 06-13-2011, 03:09 AM

I am trying to import a data file from old DOS application into MYSQL table

The file is clear text file with fixed-width columns, without column delimiters

Example file :

Code:

4444333666666
2222666555555
iiiiwwwcccccc
ttttxxxaaaaaa

Code:

mysql> create table t (col1 char(4) , col2 char(3) , col3 char (6) ) ;
Query OK, 0 rows affected (0.01 sec)

mysql> load data infile '/home/ash/1.txt' into table t fields terminated by '' enclosed by '' ;
Query OK, 4 rows affected, 8 warnings (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 8

mysql> select * from t ;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| 4444 | 6    |      |
| 2222 | 5    |      |
| iiii | c    |      |
| tttt | a    |      |
+------+------+------+
4 rows in set (0.00 sec)

How to import it properly ?

I tried to change char to varchar and it did not change anything

druuna · 06-13-2011, 03:45 AM

Hi,

I'm not sure if you can tackle this using mysql, but you might consider changing the input file and make it comma separated:

Code:

$ cat infile
4444333666666
2222666555555
iiiiwwwcccccc
ttttxxxaaaaaa

$ sed -r 's/([[:alnum:]]{4})([[:alnum:]]{3})(.*)/\1,\2,\3/' infile
4444,333,666666
2222,666,555555
iiii,www,cccccc
tttt,xxx,aaaaaa

Hope this helps.

Latios · 06-13-2011, 03:52 AM

Thanks !

druuna · 06-13-2011, 04:03 AM

You're welcome