Exporting a space delimited file from Excel

[KGee]

I have a custom program that reads data from a space delimited (padded?) text file and performs a number of tasks for us, such as importing the data into our system. Starting next month, I will be receiving a second file from a separate system and need to consolidate the two files into one before running our program. I've managed to do that with macros and code I've pulled from the forum so I'm all set on that piece. My problem is I need to export the results using the same space specifications as the source files or else our program wont be able to process the file.

Can this be done from Excel? The specs from the source file are shown below with the column or field name and the starting and ending position for each field. Each line is 132 characters long with a carriage return/line feed at the end. Spaces need to be padded to the right of the value for each field. For example, the ITEM is always 10 characters long and need to be padded with a space after the field value. The TYPE is always one character and needs a space after each value. The VAL is either one or two characters and must be padded accordingly with either 4 or 5 spaces up to the 19th character. And so one for the remaining fields.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ITEM[/TD]
[TD]TYPE[/TD]
[TD]VAL[/TD]
[TD]$[/TD]
[TD]QTY[/TD]
[TD]DESC[/TD]
[/TR]
[TR]
[TD]Start[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]14[/TD]
[TD]20[/TD]
[TD]33[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]End[/TD]
[TD]11[/TD]
[TD]13[/TD]
[TD]19[/TD]
[TD]32[/TD]
[TD]40[/TD]
[TD]132[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

If all your fields are set up to be Text fields, all the space padding will happen to the right.
Then just set the column widths to match how wide each field should be.
And then do a "Save As" using the "Formatted Text (Space delimited) (*.prn)" option.
You can actually enter any file extension you want, you just need to choose that "Save as Type".

 

[GR00007]

I've tried spacing the columns, and it's a bit tricky to get just right, since you're already in a macro, you could try using a sub or function and use it to pad each field to the width of the column.
Here's a function that I believe may fit your needs:

Function PadSpaces(r As Range, ln As Integer) As String
' PadSpaces Function
vOut = ""
vStr = r.Value
For i = 1 To ln
  If i <= Len(vStr) Then
    vOut = vOut & Mid(vStr, i, 1)
  Else
    vOut = vOut & " "
  End If
Next
PadSpaces = vOut
End Function

 

[Joe4]

I've tried spacing the columns, and it's a bit tricky to get just right

I have found it to be Font dependent. It usually works for me (making the column the width you want it to be). If it does not, I typically pick a different font and/or reduce the size of the font.
I cannot recall of the top of my head, but I think I used to use "Time New Roman" with a size of 9 or 10.

 

[GR00007]

Courier New is a fixed width font

 

[KGee]

Thanks for the suggestions. I had searched online and saw the prn suggestion but it didn't work for me. After reading your comment Joe, it makes me think I didn't define all of the fields as text. I need everything left aligned and the two numeric fields in the file came through aligned to the right if I remember correctly. So I will give that another try and I'll try the function GR00007 provided. I also found someone suggesting that you save the file in Excel format and do the export from Access. I tried that and it did give me the output I needed but I wanted to stay in Excel if possible hence my reasoning for posting here.

 

[Joe4]

I also found someone suggesting that you save the file in Excel format and do the export from Access.

I am a big fan of Access, but this looks like a totally unnecessary step. You should be able to do it pretty easily without having to invoke Access.

I need everything left aligned and the two numeric fields in the file came through aligned to the right if I remember correctly.

Assuming those columns are hard-coded numbers and not formulas, simply highlight those columns, select Format, and choose the "Text" option.
Then try saving the file as "PRN" option and it will work.

 

[Rick Rothstein]

I believe this macro will create you properly formatted text file directly from your existing data (no need to play around with column widths), simply change the red highlighted test path/filename to your actual path/filename for the output file)...

[table="width: 500"]
[tr]
	[td]Sub OutputSpaceDelimitedFile()
  Dim R As Long, C As Long, FileNum As Long
  Dim OneLine As String * 132, Result() As String
  Dim WriteAt As Variant, Data As Variant
  WriteAt = [{1,12,14,20,33,41}]
  Data = Range("A1:F" & Cells(Rows.Count, "A").End(xlUp).Row).Value2
  ReDim Result(1 To UBound(Data))
  For R = 1 To UBound(Data, 1)
    OneLine = ""
    For C = 1 To UBound(Data, 2)
      Mid(OneLine, WriteAt(C)) = Data(R, C)
    Next
    Result(R) = OneLine
  Next
  FileNum = FreeFile
  Open "[B][COLOR="#FF0000"]c:\temp\TestSpaceDelimited.txt[/COLOR][/B]" For Output As #FileNum 
    Print #FileNum , Join(Result, vbCrLf)
  Close #FileNum 
End Sub[/td]
[/tr]
[/table]

 

[KGee]

So I'm either losing my mind or something weird is happening. Earlier this morning I updated the code to select all six columns and formatted them as text with courier new as the font. I saved the output as formatted text and could have sworn the output looked fine. I had to make a few more changes just now so I modified the code and when I looked at the output I noticed the overall length varied on each line. The sixth field is the description and no longer has the padding after the description, the line just ends after the last character. I tried manually performing the steps to format the file and can't get the padding in the last field. So I removed those steps from the code and added Rick's code which I tested successfully several times. I'm planning to leave everything as is but I'm curious as to why I'm not getting the padding after the last field.

And thanks for all the help as always.

 

[Joe4]

I think it automatically drops spaces at the end, as spaces after the last character have no meaming in most programs (so they are dropped).
I haven't done it in a few years, but I sometimes would add an extra column at the end that is a single space wide, and put an "X" in it to denote the end of the line (and to maintain all the spaces before it).