Manipulate comma separated values?

Does anyone know if it is possible to change:

..."City St Zip","Country","Phone"...
to
..."City","St","Zip"...

via VB Script?  The only other option is to manually go through a large
comma delimted text file and make the changes.

Thanks,

Rich

"Rich Rekos" <RichRekos@discussions.microsoft.com> wrote in message
news:FC4ABAE5-09CD-45E8-9B70-DEE38351186E@microsoft.com...
> Does anyone know if it is possible to change:
>
> ..."City St Zip","Country","Phone"...
> to
> ..."City","St","Zip"...
>
> via VB Script?  The only other option is to manually go through a large
> comma delimted text file and make the changes.
>
> Thanks,
>
> Rich

Will this work for you?

MsgBox SplitCSZ("San Francisco, CA 98765")
Function SplitCSZ(strCSZ)
Dim arrCSZ(2)
arrCSZ(0) = Chr(34) & Mid(strCSZ,1,InStr(strCSZ,",")-1) & Chr(34)
arrCSZ(1) = Mid(strCSZ,InStr(strCSZ,",")+2,2)
arrCSZ(2) = Mid(strCSZ,InStrRev(strCSZ," ")+1)
SplitCSZ = Join(arrCSZ,",")
End Function

That's up the right alley, but I need something that can make changes to a
large text file.  Each instance of "city,state,zip" needs to be changed to
"city","state","zip".

Is that possible?

"McKirahan" wrote:

> "Rich Rekos" <RichRekos@discussions.microsoft.com> wrote in message
> news:FC4ABAE5-09CD-45E8-9B70-DEE38351186E@microsoft.com... 
>
> Will this work for you?
>
> MsgBox SplitCSZ("San Francisco, CA 98765")
> Function SplitCSZ(strCSZ)
>    Dim arrCSZ(2)
>        arrCSZ(0) = Chr(34) & Mid(strCSZ,1,InStr(strCSZ,",")-1) & Chr(34)
>        arrCSZ(1) = Mid(strCSZ,InStr(strCSZ,",")+2,2)
>        arrCSZ(2) = Mid(strCSZ,InStrRev(strCSZ," ")+1)
>     SplitCSZ = Join(arrCSZ,",")
> End Function
>
>
>
>

"Rich Rekos" <RichRekos@discussions.microsoft.com> wrote in message
news:8C3B9AE1-616C-4FDE-BE57-EC5491F22C3A@microsoft.com...
> That's up the right alley, but I need something that can make changes to a
> large text file.  Each instance of "city,state,zip" needs to be changed to
> "city","state","zip".
>
> Is that possible?

Of course it is, I just showed you how to do it for a single instance.

You would use FSO to read the file with each record calling this function
and writing out a new file.

One would have to know where in each record the City+State+Zip string
occurs.

Is this enough to get you going or, if not, can you post a couple of records
so I can craft a solution?

By the way, the code I showed you before produced:
"San Francisco",CA,98765

The version below produces:
"San Francisco","CA","98765"

MsgBox SplitCSZ("San Francisco, CA 98765")
Function SplitCSZ(strCSZ)
Dim arrCSZ(2)
arrCSZ(0) = Mid(strCSZ,1,InStr(strCSZ,",")-1)
arrCSZ(1) = Mid(strCSZ,InStr(strCSZ,",")+2,2)
arrCSZ(2) = Mid(strCSZ,InStrRev(strCSZ," ")+1)
SplitCSZ = Chr(34) & Join(arrCSZ,Chr(34) & "," & Chr(34)) & Chr(34)
End Function

BTW, probably you can also check out Regular Expression object.


McKirahan wrote:
> "Rich Rekos" <RichRekos@discussions.microsoft.com> wrote in message
> news:8C3B9AE1-616C-4FDE-BE57-EC5491F22C3A@microsoft.com...
> 
>
>
> Of course it is, I just showed you how to do it for a single instance.
>
> You would use FSO to read the file with each record calling this function
> and writing out a new file.
>
> One would have to know where in each record the City+State+Zip string
> occurs.
>
> Is this enough to get you going or, if not, can you post a couple of recor
ds
> so I can craft a solution?
>
> By the way, the code I showed you before produced:
>     "San Francisco",CA,98765
>
> The version below produces:
>     "San Francisco","CA","98765"
>
> MsgBox SplitCSZ("San Francisco, CA 98765")
> Function SplitCSZ(strCSZ)
>    Dim arrCSZ(2)
>        arrCSZ(0) = Mid(strCSZ,1,InStr(strCSZ,",")-1)
>        arrCSZ(1) = Mid(strCSZ,InStr(strCSZ,",")+2,2)
>        arrCSZ(2) = Mid(strCSZ,InStrRev(strCSZ," ")+1)
>     SplitCSZ = Chr(34) & Join(arrCSZ,Chr(34) & "," & Chr(34)) & Chr(34)
> End Function
>
>

On 12-Nov-2004, Jianchun Xu <xujianchun@yahoo.com> wrote:
 

I'm an original VB person, not VB.net. You could put the individual
characters
into an array, then run through the array to find the space chars. The ZIP
is
numeric chars, State is two spaces with two chars in between, so you
set up conditions and write the results of the tests to variables.
Hope this helps

Great thanks!!! I am a newbie, so I still need some more help ;)  Here is a
record from the text file:

"Tyler Pearson","Account Manager","Questionmark Corporation","5 Hillandale
Avenue","","Stamford, CT 06902","","","","","11/07/2004","01:50PM","211763"

"McKirahan" wrote:

> "Rich Rekos" <RichRekos@discussions.microsoft.com> wrote in message
> news:8C3B9AE1-616C-4FDE-BE57-EC5491F22C3A@microsoft.com... 
>
> Of course it is, I just showed you how to do it for a single instance.
>
> You would use FSO to read the file with each record calling this function
> and writing out a new file.
>
> One would have to know where in each record the City+State+Zip string
> occurs.
>
> Is this enough to get you going or, if not, can you post a couple of recor
ds
> so I can craft a solution?
>
> By the way, the code I showed you before produced:
>     "San Francisco",CA,98765
>
> The version below produces:
>     "San Francisco","CA","98765"
>
> MsgBox SplitCSZ("San Francisco, CA 98765")
> Function SplitCSZ(strCSZ)
>    Dim arrCSZ(2)
>        arrCSZ(0) = Mid(strCSZ,1,InStr(strCSZ,",")-1)
>        arrCSZ(1) = Mid(strCSZ,InStr(strCSZ,",")+2,2)
>        arrCSZ(2) = Mid(strCSZ,InStrRev(strCSZ," ")+1)
>     SplitCSZ = Chr(34) & Join(arrCSZ,Chr(34) & "," & Chr(34)) & Chr(34)
> End Function
>
>
>

"Rich Rekos" <RichRekos@discussions.microsoft.com> wrote in message
news:F28A5990-4F4C-431D-8A8C-B0378C53E153@microsoft.com...
> Great thanks!!! I am a newbie, so I still need some more help ;)  Here is
a
> record from the text file:
>
> "Tyler Pearson","Account Manager","Questionmark Corporation","5 Hillandale
> Avenue","","Stamford, CT
06902","","","","","11/07/2004","01:50PM","211763"

[snip]

Let me know if this works for you.

Watch for word-wrap; especially on "cSTA".

Just change the values of the input filename and output filename constants
"cOT1" and "cOT2", respectively, and let 'er rip.

'*
'*  1)  Reads a CSV file into an array; file must exist.
'*  2)  Reformats each "City, ST 12345" to "City","ST","12345".
'*  3)  Writes the array into a new CSV file; file must not exist after
delete.
'*
Option Explicit
'*
'*  Declare Constants
'*
Const cVBS = "CSZ.vbs"
Const cOT1 = "CSZ1.txt"
Const cOT2 = "CSZ2.txt"
Const cSTA =
" AK,AL,AR,AZ,CA,CO,CT,DC,DE,FL,GA,HI,IA,I
D,IL,IN,KS,KY,LA,MA,MD,ME,MI,MN,MO,
 MS,MT,NC,ND,NE,NH,NJ,NM,NV,NY,OH,OK,OR,P
A,RI,SC,SD,TN,TX,UT,VA,VT,WA,WI,WV,W
Y"
'*
'*  Declare Variables
'*
Dim arrOTF
Dim intOTF
Dim strOTF
'*
'*  Declare Objects
'*
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objOT1
Dim objOT2
'*
'*  Verify Files
'*
If Not objFSO.FileExists(cOT1) Then
MsgBox "File Not Found!" & vbCrLf & cOT1,vbExclamation,cVBS
WScript.Quit
End If
If objFSO.FileExists(cOT2) Then objFSO.DeleteFile(cOT2)
If objFSO.FileExists(cOT1) Then
MsgBox "File Exists!" & vbCrLf & cOT2,vbExclamation,cVBS
WScript.Quit
End If
'*
'*  Process
'*
Call GetIt()
Call FixIt()
Call PutIt()
'*
'*  Destroy Objects
'*
Set objFSO = Nothing
'*
'*  Finish
'*
MsgBox intOTF & " records processed.",vbInformation,cVBS

Sub GetIt()
'*
'*  Read File into Array
'*
Set objOT1 = objFSO.OpenTextFile(cOT1,1)
strOTF = objOT1.ReadAll
Set objOT1 = Nothing
arrOTF = Split(strOTF,vbCrLf)
End Sub

Sub FixIt()
'*
'*  Declare Variables
'*
Dim arrCSZ(2)
Dim strCSZ
Dim intINS
Dim intIN1
Dim intIN2
Dim intLEN
Dim strMSG
Dim arrSTA
arrSTA = Split(cSTA,",")
Dim intSTA
Dim strSTA
'*
'*  Process Each Array Item
'*
For intOTF = 0 To UBound(arrOTF)
strOTF = arrOTF(intOTF)
If strOTF = "" Then Exit For
'*
'*  Find State Code Offset
'*
intINS = 0
For intSTA = 1 To UBound(arrSTA)
If InStr(strOTF,arrSTA(intSTA)) > 0 Then
strSTA = ", " & arrSTA(intSTA) & " "
intINS = InStr(strOTF,strSTA)
End If
Next
'*
'*  No State Code Found
'*
If intINS = 0 Then
strMSG = "No State Code in record " & intOTF+1
strMSG = strMSG & vbCrLf & strOTF
MsgBox strMSG,vbExclamation,cVBS
WScript.Quit
End If
'*
'*  Reformat City, State Zip
'*
intIN1 = InStrRev(Left(strOTF,intINS),Chr(34))
intIN2 = InStr(Mid(strOTF,intINS),Chr(34))
intLEN = intINS - intIN1 + intIN2
strCSZ = Mid(strOTF,intIN1,intLEN)
arrCSZ(0) = Mid(strCSZ,1,InStr(strCSZ,",")-1)
arrCSZ(1) = Mid(strCSZ,InStr(strCSZ,",")+2,2)
arrCSZ(2) = Mid(strCSZ,InStrRev(strCSZ," ")+1)
strCSZ = Join(arrCSZ,Chr(34) & "," & Chr(34))
strOTF = Left(strOTF,intIN1-1) & strCSZ & Mid(strOTF,intIN1+intLEN)
'*
'*  Update Array Item
'*
arrOTF(intOTF) = strOTF
Next
End Sub

Sub PutIt()
'*
'*  Write File from Array
'*
strOTF = Join(arrOTF,vbCrLf)
Set objOT2 = objFSO.OpenTextFile(cOT2,2,True)
objOT2.WriteLine(strOTF)
Set objOT2 = Nothing
End Sub

I get "file already exists".  When I remove the verfy file sub, I get "no
state code for record 1".  Any ideas?

"McKirahan" wrote:

> "Rich Rekos" <RichRekos@discussions.microsoft.com> wrote in message
> news:F28A5990-4F4C-431D-8A8C-B0378C53E153@microsoft.com... 
> a 
> 06902","","","","","11/07/2004","01:50PM","211763"
>
> [snip]
>
> Let me know if this works for you.
>
> Watch for word-wrap; especially on "cSTA".
>
> Just change the values of the input filename and output filename constants
> "cOT1" and "cOT2", respectively, and let 'er rip.
>
>    '*
>    '*  1)  Reads a CSV file into an array; file must exist.
>    '*  2)  Reformats each "City, ST 12345" to "City","ST","12345".
>    '*  3)  Writes the array into a new CSV file; file must not exist after
> delete.
>    '*
>     Option Explicit
>    '*
>    '*  Declare Constants
>    '*
>     Const cVBS = "CSZ.vbs"
>     Const cOT1 = "CSZ1.txt"
>     Const cOT2 = "CSZ2.txt"
>     Const cSTA =
> " AK,AL,AR,AZ,CA,CO,CT,DC,DE,FL,GA,HI,IA,I
D,IL,IN,KS,KY,LA,MA,MD,ME,MI,MN,M
O,
>  MS,MT,NC,ND,NE,NH,NJ,NM,NV,NY,OH,OK,OR,P
A,RI,SC,SD,TN,TX,UT,VA,VT,WA,WI,WV
,W
> Y"
>    '*
>    '*  Declare Variables
>    '*
>     Dim arrOTF
>     Dim intOTF
>     Dim strOTF
>    '*
>    '*  Declare Objects
>    '*
>     Dim objFSO
>     Set objFSO = CreateObject("Scripting.FileSystemObject")
>     Dim objOT1
>     Dim objOT2
>    '*
>    '*  Verify Files
>    '*
>     If Not objFSO.FileExists(cOT1) Then
>         MsgBox "File Not Found!" & vbCrLf & cOT1,vbExclamation,cVBS
>         WScript.Quit
>     End If
>     If objFSO.FileExists(cOT2) Then objFSO.DeleteFile(cOT2)
>     If objFSO.FileExists(cOT1) Then
>         MsgBox "File Exists!" & vbCrLf & cOT2,vbExclamation,cVBS
>         WScript.Quit
>     End If
>    '*
>    '*  Process
>    '*
>     Call GetIt()
>     Call FixIt()
>     Call PutIt()
>    '*
>    '*  Destroy Objects
>    '*
>     Set objFSO = Nothing
>    '*
>    '*  Finish
>    '*
>     MsgBox intOTF & " records processed.",vbInformation,cVBS
>
> Sub GetIt()
>    '*
>    '*  Read File into Array
>    '*
>     Set objOT1 = objFSO.OpenTextFile(cOT1,1)
>         strOTF = objOT1.ReadAll
>     Set objOT1 = Nothing
>         arrOTF = Split(strOTF,vbCrLf)
> End Sub
>
> Sub FixIt()
>    '*
>    '*  Declare Variables
>    '*
>     Dim arrCSZ(2)
>     Dim strCSZ
>     Dim intINS
>     Dim intIN1
>     Dim intIN2
>     Dim intLEN
>     Dim strMSG
>     Dim arrSTA
>         arrSTA = Split(cSTA,",")
>     Dim intSTA
>     Dim strSTA
>    '*
>    '*  Process Each Array Item
>    '*
>     For intOTF = 0 To UBound(arrOTF)
>         strOTF = arrOTF(intOTF)
>         If strOTF = "" Then Exit For
>        '*
>        '*  Find State Code Offset
>        '*
>         intINS = 0
>         For intSTA = 1 To UBound(arrSTA)
>             If InStr(strOTF,arrSTA(intSTA)) > 0 Then
>                 strSTA = ", " & arrSTA(intSTA) & " "
>                 intINS = InStr(strOTF,strSTA)
>             End If
>         Next
>        '*
>        '*  No State Code Found
>        '*
>         If intINS = 0 Then
>             strMSG = "No State Code in record " & intOTF+1
>             strMSG = strMSG & vbCrLf & strOTF
>             MsgBox strMSG,vbExclamation,cVBS
>             WScript.Quit
>         End If
>        '*
>        '*  Reformat City, State Zip
>        '*
>         intIN1 = InStrRev(Left(strOTF,intINS),Chr(34))
>         intIN2 = InStr(Mid(strOTF,intINS),Chr(34))
>         intLEN = intINS - intIN1 + intIN2
>         strCSZ = Mid(strOTF,intIN1,intLEN)
>         arrCSZ(0) = Mid(strCSZ,1,InStr(strCSZ,",")-1)
>         arrCSZ(1) = Mid(strCSZ,InStr(strCSZ,",")+2,2)
>         arrCSZ(2) = Mid(strCSZ,InStrRev(strCSZ," ")+1)
>         strCSZ = Join(arrCSZ,Chr(34) & "," & Chr(34))
>         strOTF = Left(strOTF,intIN1-1) & strCSZ & Mid(strOTF,intIN1+intLEN
)
>        '*
>        '*  Update Array Item
>        '*
>         arrOTF(intOTF) = strOTF
>     Next
> End Sub
>
> Sub PutIt()
>    '*
>    '*  Write File from Array
>    '*
>         strOTF = Join(arrOTF,vbCrLf)
>     Set objOT2 = objFSO.OpenTextFile(cOT2,2,True)
>         objOT2.WriteLine(strOTF)
>     Set objOT2 = Nothing
> End Sub
>
>
>

"Rich Rekos" <RichRekos@discussions.microsoft.com> wrote in message
news:44D469F7-1FDF-4DE1-BF64-19501A3F4FB9@microsoft.com...
> I get "file already exists".  When I remove the verfy file sub, I get "no
> state code for record 1".  Any ideas?

[snip]

1)    Typo!

Change:

If objFSO.FileExists(cOT1) Then
MsgBox "File Exists!" & vbCrLf & cOT2,vbExclamation,cVBS
WScript.Quit
End If

to:

If objFSO.FileExists(cOT2) Then
MsgBox "File Exists!" & vbCrLf & cOT2,vbExclamation,cVBS
WScript.Quit
End If

2)    What does record 1 look like?  Please post it.

Does it contain the string ", XX "? That is:

comma + space + 2 character state code + space