How to use LOAD DATA INFILE to import fixed width data in MySQL

How to use LOAD DATA INFILE to import fixed width data in MySQL

It is very easy to import fixed width CSV data into your mysql database and LOAD DATA INFILE is the fastest method i have seen. If you are on shared hosting be sure you first upload the csv file to your hosting server and specify LOCAL if not you will get permission denied error.

LOAD DATA LOCAL
INFILE '<file name>' INTO TABLE <table>
(@var1)
SET
 `name`=SUBSTR(@var1,1,25),
 `address`=SUBSTR(@var1,26,25),
 `zip`=SUBSTR(@var1,51,10),
 `phone`=SUBSTR(@var1,61,10)
IGNORE 35 LINES

If you want to just update the database using latest CSV data, specify REPLACE.  The SUBSTR() function extracts the data and it accepts first variable, then position starting from and then no of characters to extract. If you want to skip useless data in first 35 lines, you can specify IGNORE 35 lines at the bottom.

LOAD DATA LOCAL
INFILE '<file name>'
REPLACE INTO TABLE <table>
(@var1)
SET `name`=SUBSTR(@var1,1,25), `address`=SUBSTR(@var1,26,25),
`zip`=SUBSTR(@var1,51,10), `phone`=SUBSTR(@var1,61,10)
IGNORE 35 LINES