Today I needed to get some data I had in a fixed width text file that I wanted to put into a table in MySQL to make it easier to manipulate. It is relatively simple to import a CSV. However, there is no native support for fixed width file.
The file looked like this (but with 88,000 rows):
SMITH 1.006 1.006 1 JOHNSON 0.810 1.816 2 WILLIAMS 0.699 2.515 3 JONES 0.621 3.136 4 BROWN 0.621 3.757 5 DAVIS 0.480 4.237 6 MILLER 0.424 4.660 7 WILSON 0.339 5.000 8 MOORE 0.312 5.312 9 TAYLOR 0.311 5.623 10
Well it was easy enough to transform into a CSV. Excel can easily import a fixed width file.
Now that I had the data in Excel, I could also clean up the all caps items using the PROPER cell formula. Then all I need to do was save the file as a CSV file.
This being done, I ran into one more small issue. I had to save the file into a directory that could be read by my local MySQL instance. Since I already had created a table matching the columns of my file, I ran the following SQL command to get the data into my table:
load data infile '/dir-readable-by-mysql/data.csv' into table lastnames FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Importing Fixed Width File into MySQL