How to upload a fixed width column data file into MySQL

Below are the steps and some example code that will help you upload a fixed width column text file into MySQL.   

1.) Determine the width of your columns in your text file.  In this example, we are using a text file named listings.txt.  This text file is an attachment to this post.  The attached text file has teh following columns:

1. LISTING_SEQ_NO   NOT NULL   NUM(7)  COL: 1-7
2. LBLCODE          NOT NULL   CHAR(6) COL: 9-14
3. PRODCODE NOT NULL CHAR(4) COL: 16-19
4. STRENGTH NULL CHAR(10) COL: 21-30
5. UNIT NULL CHAR(10) COL: 32-41
6. UNIT NULL CHAR(10) COL: 32-41
7. TRADENAME NOT NULL CHAR(100) COL: 45-144

From here, based on the number of characters and integer values and their respective widths, we create a table with the SQL below:

CREATE TABLE `listings` (

  `listing_seq_no` int(7) NOT NULL,

  `lblcode` char(6) NOT NULL,

  `prodcode` char(4) NOT NULL,

  `strength` char(10) default NULL,

  `unit` char(10) default NULL,

  `rx_otc` char(1) NOT NULL,

  `tradename` char(100) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

Next, we upload our text file into our table by issuing the following SQL statement.  In this example I stored my listings.txt file under my C:\Text\fda\ folder.  

Note  - Wherever you store your text file, be sure that you precede your backslashes with another backslash so that  you prevent your initial backslash from from becoming some sort of escape character.

So anyway, what is goes on in the SQL statement below is the following, we are assigning each line in the text file to @var1.  Then we parse the @var1 variable, using the substring function, into values and set each column equal to a substring of @var1.

LOAD DATA INFILE 'C:\\Text\\fda\\listings.TXT'

INTO TABLE fda.listings

(@var1)

SET

`listing_seq_no` = substr(@var1, 1, 7),

`lblcode` = substr(@var1, 9, 6) ,

`prodcode` = substr(@var1, 16, 4) ,

`strength` = substr(@var1, 21, 10) ,

`unit` = substr(@var1, 32, 10) ,

`rx_otc` = substr(@var1, 43, 1) ,

`tradename` = substr(@var1, 45, 100);

That is all you have to do.  You can copy and paste the code to test out the process before you begin running it.