Saving a fixed-width import layout

Excel has an in-built ability to import fixed-width text files. If you try to open a text file, a wizard will appear asking whether it's fixed-width or delimited.

Delimited files are easily processed. With fixed-width files, Excel has a go at figuring out where to insert the column breaks, but more often than not, you'll have to add some, delete others and move some more to get the desired result.

  • To insert a break, just click where you want it
  • To move a break, just drag it to where you want it
  • To delete a break, double-click it.

The frustrating part about it all is that there's no way of saving the layout that you've defined. If you're importing files of the same layout time and time again, this can be a pain.

But there is a solution.

Create a new workbook containing two sheets. One will house the raw data; the other will split it into columns.

Copy your raw data into the top of the first sheet using the traditional copy/paste method. This will all be stacked up in column A.

In the second sheet, we're going to create a frame for separating the data out into its constituent parts.

In row 3, put your column headers. In row 1 put that column's start position in the file. And in row 2, put the length of the field.

Now we'll use the MID function, together with dollars to hold our references, to put data in row 4.


You only need to create the formula in cell B4. Once you've done that, copy it across to the right-most column. Then copy that row down to cover as many records you copied into the original sheet.

I use a spreadsheet just like this regularly, calling it Template. And I pin it to my Excel Recent Documents list so that it's always only two clicks away.

This entry was posted in How to and tagged , . Bookmark the permalink.

24 Responses to Saving a fixed-width import layout

  1. Richard A says:

    After looking at several text import into Excel forums, I'm surprised at the extent to which it isn't known that Excel saves import queries and reruns them readily with the Refresh function. But my question is a little more esoteric. "With fixed-width files, Excel has a go at figuring out where to insert the column breaks." Is there any way, a setting or configuration, that keeps Excel text import form doing that? I'm exporting a dataset from Oracle, one of the columns is a CLOB sometimes over 3000 bytes. As I optimize my query, adjusting the output now and then, I've gotten tired of scrolling through a 3K+ column deselecting all those column breaks.

  2. Patrick R says:

    Dan...fantastic tip! I am faced with the task of importing a fixed width file on a regular basis that contains over a thousand columns. Your tip has saved me an immense amount of time!

  3. Henry Veldman says:

    Amazingly simple, that is once I realized what you are doing
    Saves me lots of time and allowed me to fully automate my import process

  4. Harvey Garrett says:

    You could also record a macro and replay it

  5. Pingback: Maintaining the same fixed width layout for columns for delimited text

  6. Ryan says:

    Great tip and easy to apply. Thank you.

  7. johno says:

    Fantastic tip Dan! So simple and clean.


  8. stylin19 says:

    I been looking for this. It will save my life, if I can make it work.
    When I copy: MID(Data!$A1,B$1,B$22) across cells, it doesn't change the cell reference.

    Also, the Formula does not seem to work. I am sure I am doing something wrong, but I can't see it.

    Using Excel 2007 (maybe I have something set wrong excel options)

    I renamed Sheet1 = Data
    Copy\paste data into Data A1
    Sheet 2 -
    added Start, Length and calculations for that.
    added the actual header name I want to use to line 3
    Applied the MID(etc...)to B4

    thanks for any insight

  9. stylin19 says:

    whoa...holy cipes batman ! Got it to work.
    I had the MID( etc..) only in B4. Not across cells not down the number of rows.
    I pasted after the formula was set and BINGO !

    I've got a record with approx. 300 columns that has been eating my lunch.
    Saved my life.

    Thank You

  10. stylin19 says:

    CRIPES..that didn't work either. I 'm back to my 2nd post.
    any help is appreciated.

  11. Ulf says:

    This is just not correct:
    "The frustrating part about it all is that there's no way of saving the layout that you've defined. If you're importing files of the same layout time and time again, this can be a pain."

    Just before leaving the wizard for importing the fixed width column based text file, click the properties button, then you can save the import settings.

    The solution offered here is a lousy way of doing something, that has been built in to excel for ages.

    See for the proper way to do it.

    • Curt says:

      There is a way to save the fixed format breaks in Excel using vb. Open up new workbook and turn on the "record macro". Import your file and set the columns breaks. After completing that, go to the developer tab in the workbook in which you created and turn off the "record macro". in the Visual Basic macro for that workbook, you will find the code that Excel recorded to parse out the data you manually imported. The macro will point to the file name that you selected to import. There is a way in visual basic to add more code to allow you to select the file that you want to import in. Will require some knowledge of vb but you can completely automate the import process.

      • TG says:

        Several things going on here...
        1) A fully-automated import process which Curt is well on the way to describing using VB,
        2) Saving your fixed-format import by creating a template excel file - which works an is for very large set files in the hundreds of columns - thanks for that,
        3) And then Dan's method which is great for small sets as you sometimes are given the format length so I just plug in the numbers - but note your data must be able to be in column A down the rows - using a text editor helps with this as stacks the rows for pasting easily.

        Padawan Excel Wizard - (second class)

  12. AnnieK says:

    Love it. I think better than the macros.

  13. KCK says:

    you can also import it into access, after yo parse the fixed field, go to Advanced during the import process and save the import layout
    import to access then export to excel.
    You can also copy and paste a layout right into access from an excel spread sheet by mimicing the name, type, start, width of the way Access does it

  14. Bryan says:

    Thanks so much. It's my second week at my new job and you just helped me look really good to my new boss.

  15. Marty says:

    Clever idea! Finally a way to save Import settings.

  16. Jan says:

    Thanks a lot! That saved me a lot of time. Greetings from Germany

  17. Susie says:

    Oh my gosh, I think I love you. I was pulling my hair out trying to pull in a file to compare it to IRS requirements and this did the trick. Thanks so much!

  18. Comment #1 is perfect. Did not know you can use the import(get external data) and refresh options under the Data tab to perform the same functionality.

  19. Garrett says:

    Thank you!!!!
    I added some additional features to what you had described above.

    For starters I added a 3rd worksheet called Specs where I call out Column Names, field lengths, and other pertinent info. The field lengths populate the second row on the second spreadsheet(Results).

    For row 1, starting with the second column of data, I use the following formula to auto populate the starting points to eliminate the "human" element "=$B1+SUM($B2:C2)" Where B1 = 1 and C2 will update as you drag the formula over to your right most column.

    For row 3, on the second spreadsheet(Results), I use the following formula to pull in the Column Name "=Specs!A6" The Cell ID(A6 in this example) I had to manually change as my column names were in Column A which wasn't too big of a deal.

    All of that was in addition to what was so graciously shared here in this forum post. All I need to do for future fixed width files is change the third spreadsheet(Specs) and all of the values for field lengths, starting points, and if kept in the same format the column names should populate after the first creation of the file. It has made data validation A-mazing! Thank you.

  20. Unknown says:

    Fantastic tips, thanks a ton!

  21. Wendy says:

    This worked perfectly for what I needed. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *