Import Pipe Delimited File, Parse out certian Fields, create new f

[Guest]

In Excel 2000, how do I take a pipe delimited file, strip out column(field)
6, 10, 11, 21. Then take that data, create a new pipe delimited file, and put
the stripped 4 fields in columns 4,5,6,7 of a 15 column(field) new pipe
delimited file? I want to do this automatically on a file that contains
1000+ records.

 

[JE McGimpsey]

What goes in columns 1-3 and 8-15 of the new file?

 

[Guest]

Those columns will be hardcoded with text or left blank (null). The system
I'm sending the new file to has to receive a 15 column (piped) file.

 

[JE McGimpsey]

One way:

Public Sub PipeFileTransform()
Const sDELIMITER As String = "|"
Dim vArr As Variant
Dim nFileIn As Long
Dim nFileOut As Long
Dim sPre As String
Dim sPost As String
Dim sInput As String
Dim sOutput As String

sPre = String(3, sDELIMITER)
sPost = String(8, sDELIMITER)

nFileIn = FreeFile
Open "Test1.txt" For Input As #nFileIn
nFileOut = FreeFile
Open "Test2.txt" For Output As #nFileOut
Do While Not EOF(1)
Line Input #1, sInput
vArr = Split(sInput, "|")
sOutput = sPre & vArr(5) & sDELIMITER & vArr(9) & _
sDELIMITER & vArr(10) & sDELIMITER & _
vArr(20) & sPost
Print #2, sOutput
Loop
Close #nFileIn
Close #nFileOut
End Sub

Adjust sPre and sPost as desired. Note that Split() is a VBA6 function.
If you need this to work with WinXL97 or MacXL, you'll need to roll your
own Split function.

 

[JE McGimpsey]

It doesn't make any difference in this case, but for foolish
consistency, the line below should have been:

vArr = Split(sInput, sDELIMITER)