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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
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.
 
It doesn't make any difference in this case, but for foolish
consistency, the line below should have been:

vArr = Split(sInput, sDELIMITER)
 
Back
Top