Import Pipe Delimited File, Parse out certian Fields, create new f
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. |
What goes in columns 1-3 and 8-15 of the new file?
In article , "StarBoy2000" wrote: 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. "JE McGimpsey" wrote: What goes in columns 1-3 and 8-15 of the new file? In article , "StarBoy2000" wrote: 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. |
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. In article , "StarBoy2000" wrote: 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. |
It doesn't make any difference in this case, but for foolish
consistency, the line below should have been: vArr = Split(sInput, sDELIMITER) In article , JE McGimpsey wrote: vArr = Split(sInput, "|") |
All times are GMT +1. The time now is 12:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com