Create CSV File

T

TriEssent

Hello All,
I would like to take the following file (180 Pages) and turn it into a true
CSV file. I am able to remove the uninteresting characters and insert commas
before the port numbers. I then search the document for a comma and do a
back space to get the the port number up on the same line as the IP
address/host name. My Question is how do I make this macro continue through
the whole document?

All Help is appreciated.

* + 144.32.1.10 mvsgw.MyCompany.com
|___ 1025 network blackjack
* + 144.32.3.254 ncc.MyCompany.com
|___ 135 DCE endpoint resolution
|___ 139 NETBIOS Session Service
|___ 445 Microsoft-DS
|___ 1025 network blackjack
|___ 5168 TrendMicroNormalServer
* + 144.32.4.244 h48mv31.MyCompany.com
|___ 135 DCE endpoint resolution
|___ 445 Microsoft-DS
|___ 2967 Symantec Antivirus
* + 144.32.4.245 cw2k.MyCompany.com
|___ 135 DCE endpoint resolution
|___ 139 NETBIOS Session Service
|___ 445 Microsoft-DS
|___ 1025 network blackjack
|___ 2967 Symantec Antivirus
* + 144.32.4.246 certman.MyCompany.com
|___ 135 DCE endpoint resolution
|___ 139 NETBIOS Session Service
|___ 445 Microsoft-DS
|___ 1025 network blackjack


144.32.1.10 mvsgw.MyCompany.com,1025 network blackjack
144.32.3.254 ncc.MyCompany.com,135 DCE endpoint resolution,139 NETBIOS
Session Service,445 Microsoft-DS,1025 network blackjack,5168
TrendMicroNormalServer
144.32.4.244 h48mv31.MyCompany.com,135 DCE endpoint resolution,445
Microsoft-DS,2967 Symantec Antivirus
144.32.4.245 cw2k.MyCompany.com,135 DCE endpoint resolution,139 NETBIOS
Session Service,445 Microsoft-DS,1025 network blackjack,2967 Symantec
Antivirus
144.32.4.246 certman.MyCompany.com,135 DCE endpoint resolution,139
NETBIOS Session Service,445 Microsoft-DS,1025 network blackjack
 
G

Graham Mayor

The following will make the first bit look like the second.

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "* + "
.Replacement.Text = ""
.MatchWildcards = False
End With
Selection.Find.Execute replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "[^13^l]*[0-9]{3,4}"
.Replacement.Text = ","
.MatchWildcards = True
End With
Selection.Find.Execute replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = ",(.[0-9]{2,}.[0-9]{1,})"
.Replacement.Text = "^p\1"
End With
Selection.Find.Execute
Selection.Find.Execute replace:=wdReplaceAll


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
T

TriEssent

Hello Graham and thanks for responding,
Here is what I get when I run your code. It seems to cut the first octet
from the IP address and then deletes the port numbers.

Donnie

..32.1.10 mvsgw.santeecooper.com, network blackjack
..32.3.254 ncc.santeecooper.com, DCE endpoint resolution, NETBIOS Session
Service, Microsoft-DS, network blackjack, TrendMicroNormalServer
..32.4.244 h48mv31.santeecooper.com, DCE endpoint resolution,
Microsoft-DS, Symantec Antivirus
..32.4.245 cw2k.santeecooper.com, DCE endpoint resolution, NETBIOS
Session Service, Microsoft-DS, network blackjack, Symantec Antivirus
..32.4.246 certman.santeecooper.com, DCE endpoint resolution, NETBIOS
Session Service, Microsoft-DS, network blackjack



Graham Mayor said:
The following will make the first bit look like the second.

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "* + "
.Replacement.Text = ""
.MatchWildcards = False
End With
Selection.Find.Execute replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "[^13^l]*[0-9]{3,4}"
.Replacement.Text = ","
.MatchWildcards = True
End With
Selection.Find.Execute replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = ",(.[0-9]{2,}.[0-9]{1,})"
.Replacement.Text = "^p\1"
End With
Selection.Find.Execute
Selection.Find.Execute replace:=wdReplaceAll


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Hello All,
I would like to take the following file (180 Pages) and turn it into
a true CSV file. I am able to remove the uninteresting characters
and insert commas before the port numbers. I then search the
document for a comma and do a back space to get the the port number
up on the same line as the IP address/host name. My Question is how
do I make this macro continue through the whole document?

All Help is appreciated.

* + 144.32.1.10 mvsgw.MyCompany.com
* + 144.32.3.254 ncc.MyCompany.com
* + 144.32.4.244 h48mv31.MyCompany.com
* + 144.32.4.245 cw2k.MyCompany.com
* + 144.32.4.246 certman.MyCompany.com


144.32.1.10 mvsgw.MyCompany.com,1025 network blackjack
144.32.3.254 ncc.MyCompany.com,135 DCE endpoint resolution,139
NETBIOS Session Service,445 Microsoft-DS,1025 network blackjack,5168
TrendMicroNormalServer
144.32.4.244 h48mv31.MyCompany.com,135 DCE endpoint resolution,445
Microsoft-DS,2967 Symantec Antivirus
144.32.4.245 cw2k.MyCompany.com,135 DCE endpoint resolution,139
NETBIOS Session Service,445 Microsoft-DS,1025 network
blackjack,2967 Symantec Antivirus
144.32.4.246 certman.MyCompany.com,135 DCE endpoint resolution,139
NETBIOS Session Service,445 Microsoft-DS,1025 network blackjack
Click to expand...
Click to expand...
 
T

TriEssent

Dave, This is what I have done so far. Thanks.

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "* + "
.Replacement.Text = ""
.MatchWildcards = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "^t|___ "
.Replacement.Text = ","
.MatchWildcards = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = ", 135"
.Replacement.Text = ",135"
.MatchWildcards = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = ", 139"
.Replacement.Text = ",139"
.MatchWildcards = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = ", 445"
.Replacement.Text = ",445"
.MatchWildcards = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
 
D

Dave Lett

Hi,

Borrowing heavily from what both you and Graham have provided, you could use
something like the following:

With Selection
.HomeKey Unit:=wdStory
With .Find
.ClearFormatting
.Text = "* + "
.MatchWildcards = False
With .replacement
.ClearFormatting
.Text = ""
End With
.Execute Replace:=wdReplaceAll

.Text = "^13|*([0-9]{3,4}*^13)"
.replacement.Text = ",\1"
.MatchWildcards = True
Do While .Execute(Replace:=wdReplaceOne)
Selection.HomeKey Unit:=wdLine
Loop
End With
End With


HTH,
Dave
 
G

Graham Mayor

The problem with this sort of thing is that the newsreader does not produce
an exact copy of the original file. It introduces oddities of formatting.
The principle remains good. You use replace in stages to remove the parts
you don't need without interfering with the parts you want to keep.

See http://www.gmayor.com/replace_using_wildcards.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Hello Graham and thanks for responding,
Here is what I get when I run your code. It seems to cut the first
octet from the IP address and then deletes the port numbers.

Donnie

.32.1.10 mvsgw.santeecooper.com, network blackjack
.32.3.254 ncc.santeecooper.com, DCE endpoint resolution, NETBIOS
Session Service, Microsoft-DS, network blackjack,
TrendMicroNormalServer .32.4.244 h48mv31.santeecooper.com, DCE
endpoint resolution, Microsoft-DS, Symantec Antivirus
.32.4.245 cw2k.santeecooper.com, DCE endpoint resolution, NETBIOS
Session Service, Microsoft-DS, network blackjack, Symantec
Antivirus .32.4.246 certman.santeecooper.com, DCE endpoint
resolution, NETBIOS Session Service, Microsoft-DS, network
blackjack



Graham Mayor said:
The following will make the first bit look like the second.

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "* + "
.Replacement.Text = ""
.MatchWildcards = False
End With
Selection.Find.Execute replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "[^13^l]*[0-9]{3,4}"
.Replacement.Text = ","
.MatchWildcards = True
End With
Selection.Find.Execute replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = ",(.[0-9]{2,}.[0-9]{1,})"
.Replacement.Text = "^p\1"
End With
Selection.Find.Execute
Selection.Find.Execute replace:=wdReplaceAll


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Hello All,
I would like to take the following file (180 Pages) and turn it into
a true CSV file. I am able to remove the uninteresting characters
and insert commas before the port numbers. I then search the
document for a comma and do a back space to get the the port number
up on the same line as the IP address/host name. My Question is how
do I make this macro continue through the whole document?

All Help is appreciated.

* + 144.32.1.10 mvsgw.MyCompany.com
___ 1025 network blackjack
* + 144.32.3.254 ncc.MyCompany.com
___ 135 DCE endpoint resolution
___ 139 NETBIOS Session Service
___ 445 Microsoft-DS
___ 1025 network blackjack
___ 5168 TrendMicroNormalServer
* + 144.32.4.244 h48mv31.MyCompany.com
___ 135 DCE endpoint resolution
___ 445 Microsoft-DS
___ 2967 Symantec Antivirus
* + 144.32.4.245 cw2k.MyCompany.com
___ 135 DCE endpoint resolution
___ 139 NETBIOS Session Service
___ 445 Microsoft-DS
___ 1025 network blackjack
___ 2967 Symantec Antivirus
* + 144.32.4.246 certman.MyCompany.com
___ 135 DCE endpoint resolution
___ 139 NETBIOS Session Service
___ 445 Microsoft-DS
___ 1025 network blackjack


144.32.1.10 mvsgw.MyCompany.com,1025 network blackjack
144.32.3.254 ncc.MyCompany.com,135 DCE endpoint resolution,139
NETBIOS Session Service,445 Microsoft-DS,1025 network
blackjack,5168 TrendMicroNormalServer
144.32.4.244 h48mv31.MyCompany.com,135 DCE endpoint
resolution,445 Microsoft-DS,2967 Symantec Antivirus
144.32.4.245 cw2k.MyCompany.com,135 DCE endpoint resolution,139
NETBIOS Session Service,445 Microsoft-DS,1025 network
blackjack,2967 Symantec Antivirus
144.32.4.246 certman.MyCompany.com,135 DCE endpoint
resolution,139 NETBIOS Session Service,445 Microsoft-DS,1025
network blackjack
Click to expand...
Click to expand...
Click to expand...
 
G

Graham Mayor

If you get stuck, send me a sample of the actual data to my e-mail address.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

Graham said:
The problem with this sort of thing is that the newsreader does not
produce an exact copy of the original file. It introduces oddities of
formatting. The principle remains good. You use replace in stages to
remove the parts you don't need without interfering with the parts
you want to keep.
See http://www.gmayor.com/replace_using_wildcards.htm

Hello Graham and thanks for responding,
Here is what I get when I run your code. It seems to cut the first
octet from the IP address and then deletes the port numbers.

Donnie

.32.1.10 mvsgw.santeecooper.com, network blackjack
.32.3.254 ncc.santeecooper.com, DCE endpoint resolution, NETBIOS
Session Service, Microsoft-DS, network blackjack,
TrendMicroNormalServer .32.4.244 h48mv31.santeecooper.com, DCE
endpoint resolution, Microsoft-DS, Symantec Antivirus
.32.4.245 cw2k.santeecooper.com, DCE endpoint resolution, NETBIOS
Session Service, Microsoft-DS, network blackjack, Symantec
Antivirus .32.4.246 certman.santeecooper.com, DCE endpoint
resolution, NETBIOS Session Service, Microsoft-DS, network
blackjack



Graham Mayor said:
The following will make the first bit look like the second.

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "* + "
.Replacement.Text = ""
.MatchWildcards = False
End With
Selection.Find.Execute replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "[^13^l]*[0-9]{3,4}"
.Replacement.Text = ","
.MatchWildcards = True
End With
Selection.Find.Execute replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = ",(.[0-9]{2,}.[0-9]{1,})"
.Replacement.Text = "^p\1"
End With
Selection.Find.Execute
Selection.Find.Execute replace:=wdReplaceAll


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

TriEssent wrote:
Hello All,
I would like to take the following file (180 Pages) and turn it
into a true CSV file. I am able to remove the uninteresting
characters and insert commas before the port numbers. I then
search the document for a comma and do a back space to get the the
port number up on the same line as the IP address/host name. My
Question is how do I make this macro continue through the whole
document? All Help is appreciated.

* + 144.32.1.10 mvsgw.MyCompany.com
___ 1025 network blackjack
* + 144.32.3.254 ncc.MyCompany.com
___ 135 DCE endpoint resolution
___ 139 NETBIOS Session Service
___ 445 Microsoft-DS
___ 1025 network blackjack
___ 5168 TrendMicroNormalServer
* + 144.32.4.244 h48mv31.MyCompany.com
___ 135 DCE endpoint resolution
___ 445 Microsoft-DS
___ 2967 Symantec Antivirus
* + 144.32.4.245 cw2k.MyCompany.com
___ 135 DCE endpoint resolution
___ 139 NETBIOS Session Service
___ 445 Microsoft-DS
___ 1025 network blackjack
___ 2967 Symantec Antivirus
* + 144.32.4.246 certman.MyCompany.com
___ 135 DCE endpoint resolution
___ 139 NETBIOS Session Service
___ 445 Microsoft-DS
___ 1025 network blackjack


144.32.1.10 mvsgw.MyCompany.com,1025 network blackjack
144.32.3.254 ncc.MyCompany.com,135 DCE endpoint resolution,139
NETBIOS Session Service,445 Microsoft-DS,1025 network
blackjack,5168 TrendMicroNormalServer
144.32.4.244 h48mv31.MyCompany.com,135 DCE endpoint
resolution,445 Microsoft-DS,2967 Symantec Antivirus
144.32.4.245 cw2k.MyCompany.com,135 DCE endpoint resolution,139
NETBIOS Session Service,445 Microsoft-DS,1025 network
blackjack,2967 Symantec Antivirus
144.32.4.246 certman.MyCompany.com,135 DCE endpoint
resolution,139 NETBIOS Session Service,445 Microsoft-DS,1025
network blackjack
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MS Word Macro with Loop 4
PS2007 and WSS2007 Bandwidth Monitoring and Analysis 2
Outlook crashes with Outlook connector 2002 0
Outlook connector crashes outlook 0

Top