Tag: excel

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';