removing delimiters
Posted by Mike Kleimeyer on April 26, 2001 5:39 AM
After importing a text document delimited with pipe symbol, and placing the values in seperate colums, I then sort the data to locate several bad rows of data. Then after deleting a couple of rows I now want to save the file as a text document with the original format of a pipe symbol seperating the values.
Thanks in advance!
Posted by Kevin James on April 26, 2001 6:56 AM
Hi Mike,
If I know Dave, he'll reply with the fantastic piece of VBA that I can neither read nor understand.
In the meantime, how many records are we talking about? If it is just a couple hundred, I have two possible solutions.
However, if we're talking a major some major mainframe belch, then I'll have to defer to the master (Dave).
Posted by mike on April 26, 2001 7:36 AM
delimeters
2500 lines approx tot 150kb
Posted by Mark W. on April 26, 2001 7:50 AM
So, why don't you save the file as a tab-delimited
text file, and then take it into an editor and replace
tabs with |.
Posted by Kevin James on April 26, 2001 7:58 AM
2.5gigawatts!! ("Back to the Future")
Mark's suggestion was one I was going to make.
The other is: concat the four fields into one with the pipe character. After writing the formula and copying it down 2500 lines, highlight the whole column of the formula, choose copy then choose Paste Special / Values.
Delete the orginal columns and save the new (single column data) as UNICODE text.
VBA would be quicker. I'm still studying that very hard.
Posted by Jerid on April 26, 2001 11:40 AM
Hi Mike
This is what I would do, now I'm sure there is a better way, and Dave won't like this because it has loops in it, but it's the only way I could think of.
After making your changes in Excel, Save the file as a Comma Separated file (.csv) and run this procedure.
Sub ChangeComma2Pipe()
Dim sData As String
Dim sOrgFile As String
Dim sDestFile As String
Dim iX As Integer
'Assign File Names
sOrgFile = Application.GetOpenFilename
sDestFile = Application.GetSaveAsFilename
'Open Files
Open sOrgFile For Input As #1 ' Open file for input.
Open sDestFile For Output As #2 ' Open file for output.
'Start
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, sData ' Reads each line into a var
For iX = 1 To Len(sData)
If Mid(sData, iX, 1) = "," Then
Mid(sData, iX, 1) = "|"
End If
Next iX
Print #2, sData ' Writes the current value of sData to a new file
sData = ""
Loop
Close #1 ' Close file.
Close #2 ' Close file.
End Sub
I hope this helps.
Jerid
Posted by Jerid on April 27, 2001 5:34 AM
Re: 2.5gigawatts!! ("Back to the Future")
See - 15766.html
