• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

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!
0
jchristena
Asked:
jchristena
  • 2
1 Solution
 
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
 
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now