Fixed length field import from flat file into Mysql.

Posted on 2005-05-12
Last Modified: 2008-02-01
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!
Question by:jchristena
    LVL 33

    Expert Comment

    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.

    Author Comment

    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.
    LVL 33

    Accepted Solution

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (, A SQLite Tidbit: Quick Numbers Table Generation (…
    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now