Although the reverse is easy, export of an excel worksheet as a fixed width text file (.txt) file is sometimes is not. Now, Excel does provide you with the option to save a worksheet as a text (.txt) or a Comma Separated (.csv) file, but if you had to ensure that each of the fields have their own specified lengths, this job is far from easy.
I recently came across a situation where we had to exchange data with a client whose system had a set specification. Each of the fields had a specific width (# of chars) and a simple comma separated or plain text file would not board in their systems properly. So yours truly came up with this simple utility that can helps you export an excel worksheet as a fixed width text file.
All you need to do to convert the data in an excel spreadsheet into a fixed width text file is to just select the data range that you want to export and click the ‘Export to Fixed Length Format’ button. Just ensure that the length of the fields are specified in the first row of the worksheet. Essentially what happens in the back end is that the VBA code looks at each row in the selection and for each of the fields in that row, reads the width to be assigned from the column header. Just a disclaimer - This code is neither ‘fool-proof’ nor has been tested for performance. But I am sure this would work like a charm for most of your day to day needs.
In its current format, this tool uses a space (” “) as the filler incase the data in a cell is shorter than the width specified for that column. If you want to change that, go to the VBA code and edit the “Filler_Char_To_Replace_Blanks” variable and set it to the one that you’d like to use. Let me know how this works out for you.
hello!
May 21st, 2009 at 1:09 amgreat, tank you! just what I needed. one question: is there an easy way to modify so that the values would be right-bounded in the output file? I am trying to figure out, but couldn’t up to now.
sophie
Sophie,
Glad I could be of help. See if replacing the Print statement with the one given below in the VBA code works for you.
Print #FileNum, Format$(” “, “!” & String(FieldWidth, “@”)) & Format$(CellValue, “#”);
I promise I will look at this code in a little more detail when I get some more time but you’ll have to wait for that a little.
May 21st, 2009 at 2:42 pmAjay
Hi, thanks alot for sharing this. Really useful to me. Thanks
July 1st, 2009 at 10:05 pmHi Vincent ! Glad this was of help.
July 1st, 2009 at 10:51 pmWow…I’ve been looking for something that would do this and it worked like a charm! Thanks a million!
July 6th, 2009 at 1:07 pmLisa. Happy to see that it helped.
July 6th, 2009 at 2:44 pmcan u tell me how to set the fixed length?Is that just key in the number in every first row without any setting?
July 10th, 2009 at 5:04 amHi wax,
That’s true. Just enter the number in the first row and that will be picked up as the width of that particular field in the output text file. Just copy the workbook to a folder and the output text file will be stored in the same folder. You can import the text file into excel and check the width of the fields at the time of import.
July 10th, 2009 at 1:26 pmAjay,
very helpful - thanks - but like Sophie, I need to right justify. Tried the code you suggested but it garbled. Something like this is going to save me a lot of wasted time getting spreadsheet data into mathematical models that need particularly formatted data
August 25th, 2009 at 5:30 amRoger,
Haven’t spent too much time on hacking this out but can you try the code given below and check if the fixed width file comes out with leading spaces and right justified.
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
Dim DestinationFile, CellValue, Filler_Char_To_Replace_Blanks As String
Dim FileNum, ColumnCount, RowCount, FieldWidth As Integer
Dim sht As Worksheet
Filler_Char_To_Replace_Blanks = " "
If Selection.Cells.Count < 2 Then
MsgBox "Nothing selected to export"
Selection.Activate
End
End If
DestinationFile = ActiveWorkbook.Path & "/File_With_Fixed_Length_Fields.txt"
FileNum = FreeFile()
On Error Resume Next
Open DestinationFile For Output As #FileNum
' If an error occurs report it and end.
If Err <> 0 Then
MsgBox "Cannot open filename " & DestinationFile
Selection.Activate
End
End If
' Turn error checking on.
On Error GoTo 0
' Loop for each row in selection.
For RowCount = 1 To Selection.Rows.Count
For ColumnCount = 1 To Selection.Columns.Count
CellValue = Selection.Cells(RowCount, ColumnCount).Text
If (IsNull(CellValue) Or CellValue = "") Then CellValue = Filler_Char_To_Replace_Blanks
FieldWidth = Cells(1, ColumnCount).Value
If (ColumnCount = Selection.Columns.Count) Then
Print #FileNum, Application.WorksheetFunction.Rept(" ", Application.WorksheetFunction.Max(0, FieldWidth - Len(CellValue))) & CellValue & vbCrLf;
Else: Print #FileNum, Application.WorksheetFunction.Rept(" ", Application.WorksheetFunction.Max(0, FieldWidth - Len(CellValue))) & CellValue;
End If
Next ColumnCount
Next RowCount
' Close destination file.
Close #FileNum
Selection.Activate
Workbooks.OpenText Filename:=DestinationFile
End Sub
Regards,
August 25th, 2009 at 2:03 pmSuperb Macro…I am using this to import address information into a shipping system…this is exactly what I needed…Thanks very much.
Cheers
September 14th, 2009 at 1:57 pmDean,
Glad this was of help
and welcome to da TaB !
Regards,
September 14th, 2009 at 3:02 pmVery cool and exactly what we needed - you rock!
November 13th, 2009 at 12:58 pmMark - Glad it helped. Welcome to da TaB !
November 13th, 2009 at 1:05 pmi’m having difficulty.
my spreadsheet is 16 columns. when i convert, the file_with_fixed_length_fields.txt has some columns rearranged. column F has become 2 columns (F and G), columns G, H, I have been combined into column I, with a portion of column H being placed in column I, and columns J and K combined into column J. the other columns, both before and after the rearranged ones, are converted correctly.
any ideas what i may be doing wrong?
November 20th, 2009 at 1:49 pmadditional information related to first post.
despite the column issue, when the .txt file is displayed as a text file, the alignment is ok.
but looking at the problem closer, it appears that the left most characters are being truncated. it appears this is when the data within a cell is larger than the defined length. as example, if the column is defined as being 7 characters, but one cell within that column has 8 characters, such as Schedule, then the first character is truncated, displaying chedule.
if this analysis is correct, is there a way to have the extra characters on the right truncated instead? i’ve solved the problem by first editing the data using an excel LEFT function.
November 20th, 2009 at 4:58 pm@Gary - Interesting question. Here’s the VBA implementation of the what you’ve suggested. Just place this line right before the “On Error GoTo 0″ statement in the original code used to export the text file.
2
3
4
5
6
7
8
9
For Each cel In Selection
If Len(cel.Value) > Cells(1, cel.Column).Value Then
cel.Value = Left(cel.Value, Cells(1, cel.Column).Value)
End If
Next cel
'------usual code here--------
Essentially this will check if any cell has a value greater in length than specified in the column header and then truncate it by removing the rightmost characters. Hope this helps.
November 20th, 2009 at 10:51 pmRegards,
hello ajay.
thanks for feedback.
either i’m doing something wrong (most likely) or there is a bug.
the fields continue to be truncated on left.
November 23rd, 2009 at 3:42 pm@Gary - Try this file with right truncate and let me know if it works for you
November 23rd, 2009 at 10:38 pmhello again ajay.
perfecto!!!!
thanks alot.
November 25th, 2009 at 7:50 am@Gary - yahoo
November 25th, 2009 at 1:00 pmthe trick worked for me,
Thank you
fk
December 2nd, 2009 at 6:25 am@fidelis - Thanks and welcome to da TaB !
December 2nd, 2009 at 3:12 pmThis is great, the only thing i am lacking for my application is the ability to have a delimiter with the fixed width. Where would I put it in on the code?
January 19th, 2010 at 3:03 pm@JJ - You can assign a value to ‘Filler_Char_To_Replace_Blanks’ to be used as a the delimiter. See if that helps.
January 19th, 2010 at 11:07 pmRegards,
Hi,
March 7th, 2010 at 3:22 pmThanks a lot - just what I needed to make progress at work!
It is my first time with Macros and it ran so smoothly…amazing!
Thanks indeed for sharing.
@Hanna - Thanks. Glad it worked !!
March 8th, 2010 at 11:05 pm