Fixed length field import from flat file into Mysql.

I have 10 flat files that each have over 100k lines.  2x a week, I need to drop my tables and reimport the updated data into the DB.   I was able to figure out how to import data if the fields were CSV, or used some other type of field delimiter, but how to I go about it with a fixed length file?  An example of that data file lines would be:

Smith, Harvey David              62M05/23/1949Married652208

The field data would be:
name starts at 1, width: 35
height feet: starts at 36, width:1
height inches: starts at 37, width:1
DOB: starts at 38, width 10
Maritalstatus: starts at 48, width 7,
emp ID:starts at 55, width 10

Since there are going to be 1 million lines imported in total, the most efficient method would be a great help.    Thanks to all!
jchristenaAsked:
Who is Participating?
 
snoyes_jwCommented:
So your name field is a varchar(35), the height_feet field is a varchar(1), the height_inches field is a varchar(1), the DOB is a varchar(10), etc?  Then you should be able to just to
LOAD DATA INFILE 'filename' INTO TABLE tablename FIELDS TERMINATED BY '';
0
 
snoyes_jwCommented:
The manual suggests that you define your tables using the width specified in the flat file, then use the LOAD DATA INFILE syntax.  If you don't want to change your table structure, you could create some temporary tables with the specified width, load data into that, then INSERT...SELECT from the temporary table to your real tables.

Another option would be to write a little script to parse your fixed length file into a delimited file, which you could then use with LOAD DATA INFILE or mysqlimport.
0
 
jchristenaAuthor Commented:
The table exactly matches the field with.  In other words, I constructed the tables based on the structure of the data files in the flat file.   Am I making this too complicated?  Sorry, I am very new to Mysql.  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.