MySQL – import fixed width/row format

[adsense id=”0514458240″ width=”468″ height=”60″]

Imagine you have a file that contains data in fixed row format. By that I mean that on a single row, column 1-10 contains one field, 11-15 another field etc. and this file needs to be uploaded into your MySQL database. How can this be achieved in MySQL.

The LOAD DATA INFILE command allows fixed format to be uploaded. However, there is one caveat with this and that is the content uploaded must be all CHAR or VARCHAR. The LOAD DATA INFILE will use the field dimensions to determine the length of each field.

Consider the following:

CREATE TABLE testimport (
	name VARCHAR(10),
	age VARCHAR(2)
);

And with this we want to import the following file:

TOM        3
DICK       7
HARRY     14

Then we need to issue the command and using the “FIELDS TERMINATED BY ”” clause.

mysql> load data infile '/home/rcashell/mysql/fixed/testimport.txt' into table testimport fields terminated by '';
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from testimport;
+------------+------+
| name       | age  |
+------------+------+
| TOM        |  3   | 
| DICK       |  7   | 
| HARRY      | 14   | 
+------------+------+
3 rows in set (0.00 sec)

[googleplus]