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:
=A1&REPT(" ",A2-LEN(A1))&SUBSTITUTE(B1,"-","")&REPT(" ",B2-LEN(SUBSTITUTE(B1,"-","")))&C1&
REPT(" ",C2-LEN(C1))&D1&REPT(" ",D2-LEN(D1))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.
fxp:
I prefer using the below snippet to pad cells out with spaces:
=LEFT(A1&REPT(” “,50),20)& LEFT(A2&REPT(” “,50),20)
28 February 2008, 11:38 amNick Hodge:
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?
28 February 2008, 12:39 pmFixed Width Text Files | MS Office Security Watch:
[…] Fixed Width Text Files automation blogroll book list bugs dose dose of excel downloads files fixed width text printing […]
28 February 2008, 1:19 pmRick Williams:
@Nick:
28 February 2008, 4:39 pmI 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..
Thomas:
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.
29 February 2008, 7:15 amDan Maxey:
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.
29 February 2008, 4:01 pmMatt H:
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.
'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
Sheets(sSheetName).Copy
'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
Matt H:
oops, looks like you'll have to change the amp; to an ampersand to get it to work!
3 March 2008, 11:28 amExcel Help:
Excellent tip. Working with pure text files is often cumbersome because of the lack of formatting.
17 March 2008, 11:09 am