Field delimiter formatting

[RomanR]

Hey all,

I need to format a text file that has about 2500 entries, set up in 3
fields like this:

111.22.33.44 machinename # a coment goes here

This file has gotten updated over the years, but the field delimiters
havent been kept constant: some are single tabs (good), others are
several spaces/tabs (bad). If I could format the file in such a way
that the 3 fields are separated by a single tab, that would make
importing the file into Excel far cleaner.

I've thought about adding a method to my macro that goes through the
text file and replaces all spaces with a single tab, but unfortunately
this would also be done to the comments, which I can't have happen. All
spaces, and multiple tabs separating the 3 fields need to be replaced
with a single tab, and anything after the '#' sign needs to be left as
is.

Is there a way that this delimiter formatting can happen after
importing into excel, or is it something that needs to be done into a
temp file before importing? Any and all help is appreciated.

Thanks in advance,

Roman

[Niek Otten]

Hi Roman,

Import into Excel as it is, all in one column.
Then Data>Text to columns, with # as the delimiter. Now your comment is safe.
Then insert enough blank columns and do another Data>Text to columns, check all possible delimiters and check "Treat consecutive
delimiters as one"

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"RomanR" <[email protected]> wrote in message news:[email protected]...
| Hey all,
|
| I need to format a text file that has about 2500 entries, set up in 3
| fields like this:
|
| 111.22.33.44 machinename # a coment goes here
|
| This file has gotten updated over the years, but the field delimiters
| havent been kept constant: some are single tabs (good), others are
| several spaces/tabs (bad). If I could format the file in such a way
| that the 3 fields are separated by a single tab, that would make
| importing the file into Excel far cleaner.
|
| I've thought about adding a method to my macro that goes through the
| text file and replaces all spaces with a single tab, but unfortunately
| this would also be done to the comments, which I can't have happen. All
| spaces, and multiple tabs separating the 3 fields need to be replaced
| with a single tab, and anything after the '#' sign needs to be left as
| is.
|
| Is there a way that this delimiter formatting can happen after
| importing into excel, or is it something that needs to be done into a
| temp file before importing? Any and all help is appreciated.
|
| Thanks in advance,
|
| Roman
|

[NickHK]

When you import the data into Excel (Data>Get external Data>Import Text), as
well as setting the delimiter, there is an option to treat multiple
delimiters as one.
So that should get the 3 columns into Excel. You can then use TRIM to remove
excess spaces.
Then .SaveAs a tab delimted file.
Would that work ?

NickHk

"RomanR" <[email protected]>
???????:[email protected]...
> Hey all,
>
> I need to format a text file that has about 2500 entries, set up in 3
> fields like this:
>
> 111.22.33.44 machinename # a coment goes here
>
> This file has gotten updated over the years, but the field delimiters
> havent been kept constant: some are single tabs (good), others are
> several spaces/tabs (bad). If I could format the file in such a way
> that the 3 fields are separated by a single tab, that would make
> importing the file into Excel far cleaner.
>
> I've thought about adding a method to my macro that goes through the
> text file and replaces all spaces with a single tab, but unfortunately
> this would also be done to the comments, which I can't have happen. All
> spaces, and multiple tabs separating the 3 fields need to be replaced
> with a single tab, and anything after the '#' sign needs to be left as
> is.
>
> Is there a way that this delimiter formatting can happen after
> importing into excel, or is it something that needs to be done into a
> temp file before importing? Any and all help is appreciated.
>
> Thanks in advance,
>
> Roman
>

[Tom Ogilvy]

Sub CleanFile()
Dim s1 As String, s2 As String
Dim iloc As Long
Dim SourceNum As Integer
Dim DestNum As Integer
Dim Temp As String

' If an error occurs, close the files and end the macro.
On Error GoTo ErrHandler

' Open the destination text file.

DestNum = FreeFile()
Open "C:\Data\DEST.TXT" For Output As DestNum

' Open the source text file.
SourceNum = FreeFile()
Open "C:\Data\SOURCE.TXT" For Input As SourceNum


' Read each line of the source file.
' clean it up and write it to the
' destination file.
Do While Not EOF(SourceNum)
Line Input #SourceNum, Temp
iloc = InStr(1, Temp, "#", vbTextCompare)
If iloc <> 0 Then
s1 = Left(Temp, iloc - 1)
s2 = Right(Temp, Len(Temp) - iloc)
Else
s1 = Temp
s2 = ""
End If
s1 = Application.Trim(s1)
s1 = Replace(s1, " ", vbTab)
Do While InStr(1, s1, vbTab & vbTab, vbTextCompare) > 0
s1 = Replace(s1, vbTab & vbTab, vbTab)
Loop
If Len(Trim(s2)) > 0 Then
Print #DestNum, s1 & "#", s2
Else
Print #DestNum, s1
End If
Loop

CloseFiles:

' Close the destination file and the source file.
Close #DestNum
Close #SourceNum
Exit Sub

ErrHandler:

MsgBox "Error # " & Err & ": " & Error(Err)
Resume CloseFiles

End Sub

--
Regards,
Tom Ogilvy


"RomanR" wrote:

> Hey all,
>
> I need to format a text file that has about 2500 entries, set up in 3
> fields like this:
>
> 111.22.33.44 machinename # a coment goes here
>
> This file has gotten updated over the years, but the field delimiters
> havent been kept constant: some are single tabs (good), others are
> several spaces/tabs (bad). If I could format the file in such a way
> that the 3 fields are separated by a single tab, that would make
> importing the file into Excel far cleaner.
>
> I've thought about adding a method to my macro that goes through the
> text file and replaces all spaces with a single tab, but unfortunately
> this would also be done to the comments, which I can't have happen. All
> spaces, and multiple tabs separating the 3 fields need to be replaced
> with a single tab, and anything after the '#' sign needs to be left as
> is.
>
> Is there a way that this delimiter formatting can happen after
> importing into excel, or is it something that needs to be done into a
> temp file before importing? Any and all help is appreciated.
>
> Thanks in advance,
>
> Roman
>
>