Fixed Width Text Files

Recently, I had to submit a fixed width text file to a state tax agency in lieu of sending them physical copies of W-2 forms. Fixed width files contain a number of columns and each column starts at a particular position on the line. If the data in that column is shorter than the width of the column, the column is padded with spaces so that the next column starts in the correct position.

I had the data in Excel, I just needed to get it to a text file. As it happens, I only have one employee for this state, so it would probably have been just as quick to type it manually. But I think we all know why I didn’t do that. The spec for the file is this:

Field Start Length
Full Name 1 50
SSN 51 9
Wages 60 8
Withholding 69 8

Here’s the monster formula in F1:

The basic structure is cell value & repeat a space to fill. The exception is B2 where I had to remove the dashes from the social security number. If I had more than one row to do, I probably would have put the field lengths above the data. Then I could fill down column F. Finally I would copy F1 to a blank Notepad file, and there’s my fixed-width text file.

Posted in Uncategorized

11 thoughts on “Fixed Width Text Files

  1. I prefer using the below snippet to pad cells out with spaces:

    =LEFT(A1&REPT(” “,50),20)& LEFT(A2&REPT(” “,50),20)

  2. I’m guessing this is another route. When I need a fixed width text file I just set the column widths to the number of characters I need (even if this hides some of my proportional font data) and then save the sheet as a space delimited *.prn file. Or am I missing something?

  3. @Nick:
    I have used the same technique with *.prn files. The only issue we had was with not checking that all data in long files conforms to the field widths (e.g. a 10 character value in an 8 character field gets truncated) – though I suppose all three of the above techniques will have issues with this. One advantage of Dick’s method is that the cell will return a “#VALUE” error, which I suppose is at least easy to spot..

  4. I’ve done this many times in the past. I would also format the worksheet with a fixed width font like courier and supply the first three rows with Hundreds (99 space 1, 99 space 2, ect), Tens (9 spaces 1, 9 spaces 2, ect), Units (12345678901234567890). This aided as a check for proper alignment.

  5. I’m still a big fan of separating “my” users from “their” data… meaning that I would employ a user form to input the data. All error checking is complete before the user can exit the form. If a cell in a column needs to be 50 characters, then the form will not let them enter more than that. If they enter less than 50 characters, then the code behind the form does the required string padding. We get the added benefit of being able to do any other needed error checking at that time too.

  6. The secret is Courier New, size 8. Do that and it becomes WYSIWIG, and you can save as *.prn to your heart’s content. I must done this literally tens of thousands of times.

    Here’s a routine I wrote to export the current sheet as a PRN file at the click of a button, without having to navigate through all of the messages Excel hurls at you.

    Sub ExportPRN()
    ‘Saves the active sheet is a text file
    ‘in prn format, without all the nag messages.

    Dim sPath As String
    Dim sFileName As String
    Dim sSheetName As String

    ‘Set application settings so that it runs without nag messages
    With Application
      .DisplayAlerts = False
      .ScreenUpdating = False
    End With

    ‘Default save location is the same folder as the workbook.
    sPath = ActiveWorkbook.Path

    ‘If it is a new (unsaved) workbook, save to the Desktop.
    If sPath = “” Then
        Dim WSHShell As Object
        Set WSHShell = CreateObject(“Wscript.Shell”)
        sPath = WSHShell.SpecialFolders(“Desktop”)
        Set WSHShell = Nothing
    End If

    ‘File name will be “Sheet Name.prn”
    sSheetName = ActiveSheet.Name
    sFileName = sPath & “” & sSheetName & “.prn”

    ‘Make a duplicate copy of the active sheet to a new workbook

    ‘Save the new, single-sheet workbook as a fixed-width text file (*.prn)
    ActiveWorkbook.SaveAs FileName:=sFileName, FileFormat:=xlTextPrinter, CreateBackup:=False

    ‘Close the workbook without saving
    ActiveWindow.Close (False)

    ‘Restore application settings
    With Application
      .DisplayAlerts = True
      .ScreenUpdating = True
    End With

    End Sub

  7. oops, looks like you’ll have to change the amp; to an ampersand to get it to work!

  8. The fixed text file I need to create has a total of 303 characters so saving as a .prn wraps the last columns to the bottom of the file.

    How do I get around this?

  9. In my limited experience, it doesn’t seem like the font is significant. The column width setting seems to adjust for it. Maybe not. My problem is that if I have a column at the end with nothing in it, Excel seems to insert the CR/FL at the end of the last column with data. So I’m asked for a 177 character record, but the last 48 character column (actually 3 or 4 “fields” that are all empty) is empty. When I look at the .prn file, it appears the CR/LF is at the end of the penultimate column, ignoring the 48 character column. I don’t know how to insert blanks in there (as opposed to spaces). I’m just doing this for a non-technical user who can’t tell me for sure if it matters what happens after the last bit of available data or if filling it with spaces is going to cause a problem. The last “field” is a date field and I don’t know what the import routine does. Does it just accept the data it’s given and ignore the missing fields? Does it fail if the record length is too short? Does it overwrite some field it shouldn’t if it finds a space instead of a blank?

    I was just wondering if there was a way to tell Excel to include 48 blanks, not spaces, before the CR/LF.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.