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 pmHi to all.
thnx for this superb macro.
It helps a lot.
Just one more question:
how could I do, that string would be left aligned (like now), and numbers would be right aligned in the same file?
thanks
March 15th, 2010 at 2:48 amI have looked for years to find something to do this with, this works great, thanks.
May 11th, 2010 at 3:08 pmAjay, thanks for the great code! Very helpful!
July 14th, 2010 at 10:45 amI have decimal values in the cells that I’m trying to extract values from, for e.g. 58.9 or -76.1 but this code truncates anything after the decimal point. Is there a way to extract all the decimal places as well? I tried with “Range” type but it didn’t work..
Joti
In regards to my post above, I somehow resolved the decimal problem, so it works fine now.
However, I have some cells that contain upto150 characters each. So when it exports to the excel file, half the text in the original cell is broken up into 3 different columns – A, B & C. I’ve tried manipulating the original text but still the same problem. What am I doing wrong??
Here’s an example from the exported excel file:
Column A: Headend Driver Amplifier (QRAM860(23dB) input = 15dBmV: outpu
Column B: t =
Column C: 38dBmV)
It should just appear in one column as:
Headend Driver Amplifier (QRAM860(23dB) input = 15dBmV: output =38dBmV)
Please help…
July 15th, 2010 at 7:49 amThanks – this macro is very helpful, much better than the other example I found for fixed width in the ease of dealing with blank cells. But I am new to macros and I can’t understand what is making the values also show up as a new worksheet with the same name as the file name, in addition to being written to the txt file?
July 27th, 2010 at 1:29 pmSorry – I guess I answered my own question
Workbooks.OpenText Filename:=DestinationFile
For some reason I could not see that last line of text until I entered some returns.
July 27th, 2010 at 1:50 pmGreat utility! Works great!
Is there a way I could specify my data to be right justified instead of left?
Cheers!
July 28th, 2010 at 11:41 pmThank’s from Indonesia…. this tool help me alot…..
September 16th, 2010 at 4:14 amThanks for this file. It has helped a lot. I was able to incorporate the code to truncate the characters of those cell values greater than the defined field length. I also included the right justified code. However, this code adds leading blank spaces in order to right justify it. How can I change the blank spaces to zeros?
September 23rd, 2010 at 9:42 amI ended up doing a macro to have a customized format for each column. This worked because the extraction to the text file maintains the format in which the excel is placed.
So, I first run the macro to add the leading zeros via the custom format, then the macro to export the data to the text file and it works great.
Thanks once again
September 23rd, 2010 at 12:27 pmMy first column always combined with 2nd column.. can you help?
October 14th, 2010 at 12:21 amThanks,that is very useful for me.
October 22nd, 2010 at 8:23 pmThank you Ajay for this nice little macro!
November 16th, 2010 at 7:36 amI has saved me a lot of time when implemented into mine macro.
The align right patch you wrote for Roger works fine btw
Excellent utility and very easy to use. Thanks a lot.Worked like a charm and saved a lot of work. Keep up the good work.
December 10th, 2010 at 6:22 amThis works PERFECTLY except in my application I need a way to change the justification
I have some columns that are left justified and some are right justified.
Is it possible to do this?
Thanks, this is excellent!
December 14th, 2010 at 3:11 pmThanks for the macro info. Just what I was looking for. I do have one dilemma though. I need to output two different record types in the same file. One is a header followed by detail. The header has different field lengths than the detail. Any suggestions?
January 5th, 2011 at 4:26 pmThat´s great¡¡¡
January 20th, 2011 at 12:21 pmThank you! Just what I was looking for.
Dan
January 24th, 2011 at 11:52 amMadison, WI
Hello, I have two questions: First, you said “select the data range that you want to export and click the ‘Export to Fixed Length Format’ button.” I’m using Excel 2007 and I can’t find a button “Export to Fixed Length…” Also, I have columns with headers which have just blanks in the cells below which have fixed lengths but no data. Will spaces get created for those columns = to the number used in the first cell? I’m sorry, I’m having problems, seems like everyone loves this program and I’m thrilled to have found it, just not off to a good start! Thanks,
February 16th, 2011 at 6:54 pmVicki
Thanks so much!!
What I really need though is to be able to have some fields left justified and others right justified.
Can you assist?
February 28th, 2011 at 8:41 pmGreat stuff, saved me weeks 🙂
March 31st, 2011 at 9:47 amThis is great code Ajay!! How would I alter the code so that the txt file can be archived by date so saveas ABC File Name 2011.4.7 rather than just saving it with the same file name each time?
April 7th, 2011 at 12:31 pmAwesome code, and about one tenth the length we used with a “string & fill” method.
July 22nd, 2011 at 12:05 pmYou saved me a ton of work. You’re a master!! Thanks for putting this tool together.
September 21st, 2011 at 10:59 am