Replace() problems

Hi,
I have a file which is a list of French first-names in four CSV-type
columns

Agnès-Céline;Agnès Céline;AGNES-CELINE;AGNES CELINE
Pat;Pat;PAT;PAT
etc

The first column is correctly spelt.
But 2nd 3rd and 4th columns are mis-spellings.
2=No hyphen. 3=Block capitals. 4=Block capitals and no hyphen.

The idea is to use this first-names file to correct another file of names
and adresses where first-names may be mis-spelt..
The idea is to scan through the adresses file line by line checking for the
prescence of strings identical to data in columns 2, 3 or 4.
Any such data should then be replaced by the data from column 1.

This works BUT !! ...
Incidences of PATTON (as in PATTON STREET) were getting replaced by PatTON.
etc.
BANNER became BAnneR, (i.e. the ANNE rplaced by Anne.)

So I decided to check for whole words, thinking that as my data in the
adress file is tab-separated I could look for
chr(9)&ANNE&chr(9) and replace that with chr(9)&Anne&chr(9)

But that doesn't work and I can't figure out why.
Is it Unicode/Ascii ?
Is there no way to use Replace() on only whole words ?

It's driving me nuts.
Almost there but not quite!
Here's the script.
TIA

Scottie.
---------------------
'Create a File System Object
on error resume next
Const ForReading = 1, ForWriting = 2, ForAppending = 8

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
'Get the file contents
Dim MyPrenoms
Set MyPrenoms = fso.OpenTextFile( "C:\VBS STUFF\prénoms.csv",ForReading,
False)

'Loop through counting the lines
LineCount=0
Dim MyArray (4000, 4)

Do While Not MyPrenoms.AtEndOfStream
MyLine = MyPrenoms.readLine

'msgbox Myline
LineCcontents = Split(MyLine, ",", -1, 1)
'MSGBOX LineCcontents(0) & LineCcontents(1) & LineCcontents(2)&
LineCcontents(3)
MyArray(LineCount,0) = LineCcontents(0)
MyArray(LineCount,1) = LineCcontents(1)
MyArray(LineCount,2) = LineCcontents(2)
MyArray(LineCount,3) = LineCcontents(3)

' msgBox MyArray(LineCount,0) & MyArray(LineCount,1) &
MyArray(LineCount,2) & MyArray(LineCount,3)

LineCount = LineCount + 1
Loop

msgbox "LineCount Prénoms : " & LineCount


Dim MyFileAdressesIn
Set MyFileAdressesIn = fso.OpenTextFile("C:\VBS STUFF\lapat1.txt",
1, False)

Set MyFile = fso.GetFile("C:\VBS STUFF\lapatOut.txt")
MyFile.Delete
Dim MyFileAdressesOut
Set MyFileAdressesOut = fso.OpenTextFile ("C:\VBS
STUFF\lapatOut.txt", ForAppending, True)

do while RL < 20 '---------for testing
MyAdresse = MyFileAdressesIn.readLine
RL = RL + 1
MyOldAdresse = MyAdresse
For i = 0 to Linecount
For j = 1 TO 3
' If Instr(1,Myadresse, chr(9) &trim( MyArray(i,j) )&
chr(9), 0 ) then
' MsgBox "Found" & vbcrlf & MyAdresse & vbcrlf &
MyArray(i,j)
' End if

MyAdresse = Replace( MyAdresse, _
chr(9) & MyArray(i,j) & chr(9), _
chr(9) & MyArray(i,0) & chr(9), 0 )

MsgBox MyOldAdresse & vbcrlf & _
MyAdresse & vbcrlf & _
chr(9)& MyArray(i,j)& chr(9)& vbcrlf & _
chr(9)& MyArray(i,0)& chr(9)

Next 'i
Next 'j
MyFileAdressesOut.writeline(MyAdresse)

Loop

'Cleanup


MyMyPrenoms.Close
MyFileAdressesIn.Close
MyFileAdressesOut.Close
Set MyFileContents = Nothing
Set fso = Nothing
msgbox "Finished" & "LineCount " & LineCount & vbcrlf & _
" i " & i & vbcrlf & _
" j " & j

McKirahan
Thu Dec 02 11:59:44 CST 2004

"Scottie Hannigan" <Scottie.Hannigan@wanadoo.fr> wrote in message
news:41af4f90$0$9029$8fcfb975@news.wanadoo.fr...
> Hi,
> I have a file which is a list of French first-names in four CSV-type
> columns
>
> Agnès-Céline;Agnès Céline;AGNES-CELINE;AGNES CELINE
> Pat;Pat;PAT;PAT
> etc
>
> The first column is correctly spelt.
> But 2nd 3rd and 4th columns are mis-spellings.
> 2=No hyphen. 3=Block capitals. 4=Block capitals and no hyphen.
>
> The idea is to use this first-names file to correct another file of names
> and adresses where first-names may be mis-spelt..
> The idea is to scan through the adresses file line by line checking for
the
> prescence of strings identical to data in columns 2, 3 or 4.
> Any such data should then be replaced by the data from column 1.
>
> This works BUT !! ...
> Incidences of PATTON (as in PATTON STREET) were getting replaced by
PatTON.
> etc.
> BANNER became BAnneR, (i.e. the ANNE rplaced by Anne.)
>
> So I decided to check for whole words, thinking that as my data in the
> adress file is tab-separated I could look for
> chr(9)&ANNE&chr(9) and replace that with chr(9)&Anne&chr(9)
>
> But that doesn't work and I can't figure out why.
> Is it Unicode/Ascii ?
> Is there no way to use Replace() on only whole words ?
>
> It's driving me nuts.
> Almost there but not quite!
> Here's the script.
> TIA
>
> Scottie.
> ---------------------
> 'Create a File System Object
> on error resume next
> Const ForReading = 1, ForWriting = 2, ForAppending = 8
>
> Dim fso
> Set fso = CreateObject("Scripting.FileSystemObject")
> 'Get the file contents
> Dim MyPrenoms
> Set MyPrenoms = fso.OpenTextFile( "C:\VBS STUFF\prénoms.csv",ForReading,
> False)
>
> 'Loop through counting the lines
> LineCount=0
> Dim MyArray (4000, 4)
>
> Do While Not MyPrenoms.AtEndOfStream
> MyLine = MyPrenoms.readLine
>
> 'msgbox Myline
> LineCcontents = Split(MyLine, ",", -1, 1)
> 'MSGBOX LineCcontents(0) & LineCcontents(1) & LineCcontents(2)&
> LineCcontents(3)
> MyArray(LineCount,0) = LineCcontents(0)
> MyArray(LineCount,1) = LineCcontents(1)
> MyArray(LineCount,2) = LineCcontents(2)
> MyArray(LineCount,3) = LineCcontents(3)
>
> ' msgBox MyArray(LineCount,0) & MyArray(LineCount,1) &
> MyArray(LineCount,2) & MyArray(LineCount,3)
>
> LineCount = LineCount + 1
> Loop
>
> msgbox "LineCount Prénoms : " & LineCount
>
>
> Dim MyFileAdressesIn
> Set MyFileAdressesIn = fso.OpenTextFile("C:\VBS STUFF\lapat1.txt",
> 1, False)
>
> Set MyFile = fso.GetFile("C:\VBS STUFF\lapatOut.txt")
> MyFile.Delete
> Dim MyFileAdressesOut
> Set MyFileAdressesOut = fso.OpenTextFile ("C:\VBS
> STUFF\lapatOut.txt", ForAppending, True)
>
> do while RL < 20 '---------for testing
> MyAdresse = MyFileAdressesIn.readLine
> RL = RL + 1
> MyOldAdresse = MyAdresse
> For i = 0 to Linecount
> For j = 1 TO 3
> ' If Instr(1,Myadresse, chr(9) &trim( MyArray(i,j) )&
> chr(9), 0 ) then
> ' MsgBox "Found" & vbcrlf & MyAdresse & vbcrlf &
> MyArray(i,j)
> ' End if
>
> MyAdresse = Replace( MyAdresse, _
> chr(9) & MyArray(i,j) & chr(9), _
> chr(9) & MyArray(i,0) & chr(9), 0 )
>
> MsgBox MyOldAdresse & vbcrlf & _
> MyAdresse & vbcrlf & _
> chr(9)& MyArray(i,j)& chr(9)& vbcrlf & _
> chr(9)& MyArray(i,0)& chr(9)
>
> Next 'i
> Next 'j
> MyFileAdressesOut.writeline(MyAdresse)
>
> Loop
>
> 'Cleanup
>
>
> MyMyPrenoms.Close
> MyFileAdressesIn.Close
> MyFileAdressesOut.Close
> Set MyFileContents = Nothing
> Set fso = Nothing
> msgbox "Finished" & "LineCount " & LineCount & vbcrlf & _
> " i " & i & vbcrlf & _
> " j " & j
>
>

Is "the adresses file" a CSV file?
If so, which element is the name?

You can use a Regular Expression to isolate whole words.

McKirahan
Thu Dec 02 13:18:59 CST 2004

"McKirahan" <News@McKirahan.com> wrote in message
news:kKIrd.599767$mD.374027@attbi_s02...
> "Scottie Hannigan" <Scottie.Hannigan@wanadoo.fr> wrote in message
> news:41af4f90$0$9029$8fcfb975@news.wanadoo.fr...
> > Hi,
> > I have a file which is a list of French first-names in four CSV-type
> > columns
> >
> > Agnès-Céline;Agnès Céline;AGNES-CELINE;AGNES CELINE
> > Pat;Pat;PAT;PAT
> > etc
> >
> > The first column is correctly spelt.
> > But 2nd 3rd and 4th columns are mis-spellings.
> > 2=No hyphen. 3=Block capitals. 4=Block capitals and no hyphen.
> >
> > The idea is to use this first-names file to correct another file of
names
> > and adresses where first-names may be mis-spelt..
> > The idea is to scan through the adresses file line by line checking for
> the
> > prescence of strings identical to data in columns 2, 3 or 4.
> > Any such data should then be replaced by the data from column 1.
> >
> > This works BUT !! ...
> > Incidences of PATTON (as in PATTON STREET) were getting replaced by
> PatTON.
> > etc.
> > BANNER became BAnneR, (i.e. the ANNE rplaced by Anne.)
> >
> > So I decided to check for whole words, thinking that as my data in the
> > adress file is tab-separated I could look for
> > chr(9)&ANNE&chr(9) and replace that with chr(9)&Anne&chr(9)
> >
> > But that doesn't work and I can't figure out why.
> > Is it Unicode/Ascii ?
> > Is there no way to use Replace() on only whole words ?
> >
> > It's driving me nuts.
> > Almost there but not quite!
> > Here's the script.
> > TIA
> >
> > Scottie.
> > ---------------------
> > 'Create a File System Object
> > on error resume next
> > Const ForReading = 1, ForWriting = 2, ForAppending = 8
> >
> > Dim fso
> > Set fso = CreateObject("Scripting.FileSystemObject")
> > 'Get the file contents
> > Dim MyPrenoms
> > Set MyPrenoms = fso.OpenTextFile( "C:\VBS
STUFF\prénoms.csv",ForReading,
> > False)
> >
> > 'Loop through counting the lines
> > LineCount=0
> > Dim MyArray (4000, 4)
> >
> > Do While Not MyPrenoms.AtEndOfStream
> > MyLine = MyPrenoms.readLine
> >
> > 'msgbox Myline
> > LineCcontents = Split(MyLine, ",", -1, 1)
> > 'MSGBOX LineCcontents(0) & LineCcontents(1) & LineCcontents(2)&
> > LineCcontents(3)
> > MyArray(LineCount,0) = LineCcontents(0)
> > MyArray(LineCount,1) = LineCcontents(1)
> > MyArray(LineCount,2) = LineCcontents(2)
> > MyArray(LineCount,3) = LineCcontents(3)
> >
> > ' msgBox MyArray(LineCount,0) & MyArray(LineCount,1) &
> > MyArray(LineCount,2) & MyArray(LineCount,3)
> >
> > LineCount = LineCount + 1
> > Loop
> >
> > msgbox "LineCount Prénoms : " & LineCount
> >
> >
> > Dim MyFileAdressesIn
> > Set MyFileAdressesIn = fso.OpenTextFile("C:\VBS
STUFF\lapat1.txt",
> > 1, False)
> >
> > Set MyFile = fso.GetFile("C:\VBS STUFF\lapatOut.txt")
> > MyFile.Delete
> > Dim MyFileAdressesOut
> > Set MyFileAdressesOut = fso.OpenTextFile ("C:\VBS
> > STUFF\lapatOut.txt", ForAppending, True)
> >
> > do while RL < 20 '---------for testing
> > MyAdresse = MyFileAdressesIn.readLine
> > RL = RL + 1
> > MyOldAdresse = MyAdresse
> > For i = 0 to Linecount
> > For j = 1 TO 3
> > ' If Instr(1,Myadresse, chr(9) &trim( MyArray(i,j) )&
> > chr(9), 0 ) then
> > ' MsgBox "Found" & vbcrlf & MyAdresse & vbcrlf &
> > MyArray(i,j)
> > ' End if
> >
> > MyAdresse = Replace( MyAdresse, _
> > chr(9) & MyArray(i,j) & chr(9), _
> > chr(9) & MyArray(i,0) & chr(9), 0 )
> >
> > MsgBox MyOldAdresse & vbcrlf & _
> > MyAdresse & vbcrlf & _
> > chr(9)& MyArray(i,j)& chr(9)& vbcrlf & _
> > chr(9)& MyArray(i,0)& chr(9)
> >
> > Next 'i
> > Next 'j
> > MyFileAdressesOut.writeline(MyAdresse)
> >
> > Loop
> >
> > 'Cleanup
> >
> >
> > MyMyPrenoms.Close
> > MyFileAdressesIn.Close
> > MyFileAdressesOut.Close
> > Set MyFileContents = Nothing
> > Set fso = Nothing
> > msgbox "Finished" & "LineCount " & LineCount & vbcrlf & _
> > " i " & i & vbcrlf & _
> > " j " & j
> >
> >
>
> Is "the adresses file" a CSV file?
> If so, which element is the name?
>
> You can use a Regular Expression to isolate whole words.
>
>

Here's a solution that replaces "Bad" names with "Good" names as long as
there is a space on either side (or if its at the begging or end of the
line). Watch for word-wrap.

'****
'* This Visual Basic Script (VBS) program does the following:
'* 1) Read a CSV into an array; display the record count.
'* (actually its a Semicolon-Separated-Values) file.
'* Each record contains 4 versions of a name;
'* The first name is "Good", the other three are "Bad" names.
'* 2) Read a Name and Address file into an array.
'* 3) Examine each row replacing each "Bad" name with the "Good" name.
'* 4) Write each record to a new Name and Address file.
'* 5) Display the number of "Bad" names that were replaced.
'****
Option Explicit
'*
'* Declare Constants
'*
Const cVBS = "prénoms.vbs"
Const cCSV = "prénoms.csv"
Const cOT1 = "lapat1.txt"
Const cOT2 = "lapatOut.txt"
Const cFOL = "C:\VBS STUFF\"
'*
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
'*
'* Delare Variables
'*
Dim arrCSV
Dim intCSV
Dim strCSV
Dim arrNAM
Dim intNAM
Dim strNAM
Dim intPOS
Dim arrOT1
Dim intOT1
Dim strOT1
Dim intOT2
Dim strOT2
'*
'* Declare Objects
'*
Dim objFSO
Dim objOTF
Dim objOT1
Dim objOT2
'*
'* Assign Objects
'*
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOTF = objFSO.OpenTextFile(cFOL & cCSV,ForReading)
Set objOT1 = objFSO.OpenTextFile(cFOL & cOT1,ForReading)
Set objOT2 = objFSO.OpenTextFile(cFOL & cOT2,ForWriting,True)
'*
'* Read Prénoms
'*
strCSV = objOTF.ReadAll()
arrCSV = Split(strCSV,vbCrLf)
MsgBox "LineCount Prénoms : " & UBound(arrCSV) + 1,vbInformation,cVBS
'*
'* Read the input file into an array
'*
strOT1 = objOT1.ReadAll()
arrOT1 = Split(strOT1,vbCrLf)
intOT2 = 0
'*
'* Process each Name and Address record
'*
For intOT1 = 0 To UBound(arrOT1)
strOT2 = " " & arrOT1(intOT1) & " "
'*
'* Process each Good/Bad Name record
'*
For intCSV = 0 To UBound(arrCSV)
arrNAM = Split(arrCSV(intCSV),";")
strNAM = arrNAM(0)
'*
'* Find "Bad" names and replace them with the "Good" name
'*
Do
For intNAM = 1 To UBound(arrNAM)
intPOS = InStr(strOT2," " & arrNAM(intNAM) & " ")
If intPOS > 0 Then
strOT2 = Left(strOT2,intPOS) & strNAM &
Mid(strOT2,Len(strNAM)+intPOS+1)
intOT2 = intOT2 + 1
End If
Next
If intPOS = 0 Then Exit Do
Loop
Next
'*
'* Write out the new Name and Address record
'*
strOT2 = Mid(strOT2,2,Len(strOT2)-2)
objOT2.WriteLine(strOT2)
Next
'*
'* Destroy Objects
'*
Set objOTF = Nothing
Set objOT1 = Nothing
Set objOT2 = Nothing
Set objFSO = Nothing
'*
'* Finished
'*
MsgBox "Changed Prénoms : " & intOT2,vbInformation,cVBS

Ugadoo
Thu Dec 02 14:40:35 CST 2004

Thank you, Sir !
I'll give that a blast some time tomorrow.
Scottie.



"McKirahan" <News@McKirahan.com> a écrit dans le message de news:
DUJrd.702946$8_6.177634@attbi_s04...
> "McKirahan" <News@McKirahan.com> wrote in message
> news:kKIrd.599767$mD.374027@attbi_s02...
>> "Scottie Hannigan" <Scottie.Hannigan@wanadoo.fr> wrote in message
>> news:41af4f90$0$9029$8fcfb975@news.wanadoo.fr...
>> > Hi,
>> > I have a file which is a list of French first-names in four CSV-type
>> > columns
>> >
>> > Agnès-Céline;Agnès Céline;AGNES-CELINE;AGNES CELINE
>> > Pat;Pat;PAT;PAT
>> > etc
>> >
>> > The first column is correctly spelt.
>> > But 2nd 3rd and 4th columns are mis-spellings.
>> > 2=No hyphen. 3=Block capitals. 4=Block capitals and no hyphen.
>> >
>> > The idea is to use this first-names file to correct another file of
> names
>> > and adresses where first-names may be mis-spelt..
>> > The idea is to scan through the adresses file line by line checking for
>> the
>> > prescence of strings identical to data in columns 2, 3 or 4.
>> > Any such data should then be replaced by the data from column 1.
>> >
>> > This works BUT !! ...
>> > Incidences of PATTON (as in PATTON STREET) were getting replaced by
>> PatTON.
>> > etc.
>> > BANNER became BAnneR, (i.e. the ANNE rplaced by Anne.)
>> >
>> > So I decided to check for whole words, thinking that as my data in the
>> > adress file is tab-separated I could look for
>> > chr(9)&ANNE&chr(9) and replace that with chr(9)&Anne&chr(9)
>> >
>> > But that doesn't work and I can't figure out why.
>> > Is it Unicode/Ascii ?
>> > Is there no way to use Replace() on only whole words ?
>> >
>> > It's driving me nuts.
>> > Almost there but not quite!
>> > Here's the script.
>> > TIA
>> >
>> > Scottie.
>> > ---------------------
>> > 'Create a File System Object
>> > on error resume next
>> > Const ForReading = 1, ForWriting = 2, ForAppending = 8
>> >
>> > Dim fso
>> > Set fso = CreateObject("Scripting.FileSystemObject")
>> > 'Get the file contents
>> > Dim MyPrenoms
>> > Set MyPrenoms = fso.OpenTextFile( "C:\VBS
> STUFF\prénoms.csv",ForReading,
>> > False)
>> >
>> > 'Loop through counting the lines
>> > LineCount=0
>> > Dim MyArray (4000, 4)
>> >
>> > Do While Not MyPrenoms.AtEndOfStream
>> > MyLine = MyPrenoms.readLine
>> >
>> > 'msgbox Myline
>> > LineCcontents = Split(MyLine, ",", -1, 1)
>> > 'MSGBOX LineCcontents(0) & LineCcontents(1) & LineCcontents(2)&
>> > LineCcontents(3)
>> > MyArray(LineCount,0) = LineCcontents(0)
>> > MyArray(LineCount,1) = LineCcontents(1)
>> > MyArray(LineCount,2) = LineCcontents(2)
>> > MyArray(LineCount,3) = LineCcontents(3)
>> >
>> > ' msgBox MyArray(LineCount,0) & MyArray(LineCount,1) &
>> > MyArray(LineCount,2) & MyArray(LineCount,3)
>> >
>> > LineCount = LineCount + 1
>> > Loop
>> >
>> > msgbox "LineCount Prénoms : " & LineCount
>> >
>> >
>> > Dim MyFileAdressesIn
>> > Set MyFileAdressesIn = fso.OpenTextFile("C:\VBS
> STUFF\lapat1.txt",
>> > 1, False)
>> >
>> > Set MyFile = fso.GetFile("C:\VBS STUFF\lapatOut.txt")
>> > MyFile.Delete
>> > Dim MyFileAdressesOut
>> > Set MyFileAdressesOut = fso.OpenTextFile ("C:\VBS
>> > STUFF\lapatOut.txt", ForAppending, True)
>> >
>> > do while RL < 20 '---------for testing
>> > MyAdresse = MyFileAdressesIn.readLine
>> > RL = RL + 1
>> > MyOldAdresse = MyAdresse
>> > For i = 0 to Linecount
>> > For j = 1 TO 3
>> > ' If Instr(1,Myadresse, chr(9) &trim( MyArray(i,j) )&
>> > chr(9), 0 ) then
>> > ' MsgBox "Found" & vbcrlf & MyAdresse & vbcrlf &
>> > MyArray(i,j)
>> > ' End if
>> >
>> > MyAdresse = Replace( MyAdresse, _
>> > chr(9) & MyArray(i,j) & chr(9), _
>> > chr(9) & MyArray(i,0) & chr(9), 0 )
>> >
>> > MsgBox MyOldAdresse & vbcrlf & _
>> > MyAdresse & vbcrlf & _
>> > chr(9)& MyArray(i,j)& chr(9)& vbcrlf & _
>> > chr(9)& MyArray(i,0)& chr(9)
>> >
>> > Next 'i
>> > Next 'j
>> > MyFileAdressesOut.writeline(MyAdresse)
>> >
>> > Loop
>> >
>> > 'Cleanup
>> >
>> >
>> > MyMyPrenoms.Close
>> > MyFileAdressesIn.Close
>> > MyFileAdressesOut.Close
>> > Set MyFileContents = Nothing
>> > Set fso = Nothing
>> > msgbox "Finished" & "LineCount " & LineCount & vbcrlf & _
>> > " i " & i & vbcrlf & _
>> > " j " & j
>> >
>> >
>>
>> Is "the adresses file" a CSV file?
>> If so, which element is the name?
>>
>> You can use a Regular Expression to isolate whole words.
>>
>>
>
> Here's a solution that replaces "Bad" names with "Good" names as long as
> there is a space on either side (or if its at the begging or end of the
> line). Watch for word-wrap.
>
> '****
> '* This Visual Basic Script (VBS) program does the following:
> '* 1) Read a CSV into an array; display the record count.
> '* (actually its a Semicolon-Separated-Values) file.
> '* Each record contains 4 versions of a name;
> '* The first name is "Good", the other three are "Bad" names.
> '* 2) Read a Name and Address file into an array.
> '* 3) Examine each row replacing each "Bad" name with the "Good" name.
> '* 4) Write each record to a new Name and Address file.
> '* 5) Display the number of "Bad" names that were replaced.
> '****
> Option Explicit
> '*
> '* Declare Constants
> '*
> Const cVBS = "prénoms.vbs"
> Const cCSV = "prénoms.csv"
> Const cOT1 = "lapat1.txt"
> Const cOT2 = "lapatOut.txt"
> Const cFOL = "C:\VBS STUFF\"
> '*
> Const ForReading = 1
> Const ForWriting = 2
> Const ForAppending = 8
> '*
> '* Delare Variables
> '*
> Dim arrCSV
> Dim intCSV
> Dim strCSV
> Dim arrNAM
> Dim intNAM
> Dim strNAM
> Dim intPOS
> Dim arrOT1
> Dim intOT1
> Dim strOT1
> Dim intOT2
> Dim strOT2
> '*
> '* Declare Objects
> '*
> Dim objFSO
> Dim objOTF
> Dim objOT1
> Dim objOT2
> '*
> '* Assign Objects
> '*
> Set objFSO = CreateObject("Scripting.FileSystemObject")
> Set objOTF = objFSO.OpenTextFile(cFOL & cCSV,ForReading)
> Set objOT1 = objFSO.OpenTextFile(cFOL & cOT1,ForReading)
> Set objOT2 = objFSO.OpenTextFile(cFOL & cOT2,ForWriting,True)
> '*
> '* Read Prénoms
> '*
> strCSV = objOTF.ReadAll()
> arrCSV = Split(strCSV,vbCrLf)
> MsgBox "LineCount Prénoms : " & UBound(arrCSV) + 1,vbInformation,cVBS
> '*
> '* Read the input file into an array
> '*
> strOT1 = objOT1.ReadAll()
> arrOT1 = Split(strOT1,vbCrLf)
> intOT2 = 0
> '*
> '* Process each Name and Address record
> '*
> For intOT1 = 0 To UBound(arrOT1)
> strOT2 = " " & arrOT1(intOT1) & " "
> '*
> '* Process each Good/Bad Name record
> '*
> For intCSV = 0 To UBound(arrCSV)
> arrNAM = Split(arrCSV(intCSV),";")
> strNAM = arrNAM(0)
> '*
> '* Find "Bad" names and replace them with the "Good" name
> '*
> Do
> For intNAM = 1 To UBound(arrNAM)
> intPOS = InStr(strOT2," " & arrNAM(intNAM) & " ")
> If intPOS > 0 Then
> strOT2 = Left(strOT2,intPOS) & strNAM &
> Mid(strOT2,Len(strNAM)+intPOS+1)
> intOT2 = intOT2 + 1
> End If
> Next
> If intPOS = 0 Then Exit Do
> Loop
> Next
> '*
> '* Write out the new Name and Address record
> '*
> strOT2 = Mid(strOT2,2,Len(strOT2)-2)
> objOT2.WriteLine(strOT2)
> Next
> '*
> '* Destroy Objects
> '*
> Set objOTF = Nothing
> Set objOT1 = Nothing
> Set objOT2 = Nothing
> Set objFSO = Nothing
> '*
> '* Finished
> '*
> MsgBox "Changed Prénoms : " & intOT2,vbInformation,cVBS
>
>

McKirahan
Thu Dec 02 15:33:05 CST 2004

"Scottie Hannigan" <Scottie.Hannigan@wanadoo.fr> wrote in message
news:41af4f90$0$9029$8fcfb975@news.wanadoo.fr...
> Hi,
> I have a file which is a list of French first-names in four CSV-type
> columns
>
> Agnès-Céline;Agnès Céline;AGNES-CELINE;AGNES CELINE
> Pat;Pat;PAT;PAT
> etc
>
> The first column is correctly spelt.
> But 2nd 3rd and 4th columns are mis-spellings.
> 2=No hyphen. 3=Block capitals. 4=Block capitals and no hyphen.

[snip]

I just noticed that in your "Pat" example above that the first and second
columns for "Pat" are the same which means your script (when fixed) will
unnecessarily replace "Pat" with "Pat".

Scottie
Fri Dec 03 02:52:22 CST 2004

"McKirahan" <News@McKirahan.com> a écrit dans le message de
news:lSLrd.502310$D%.317160@attbi_s51...
> "Scottie Hannigan" <Scottie.Hannigan@wanadoo.fr> wrote in message
> news:41af4f90$0$9029$8fcfb975@news.wanadoo.fr...
> > Hi,
> > I have a file which is a list of French first-names in four CSV-type
> > columns
> >
> > Agnès-Céline;Agnès Céline;AGNES-CELINE;AGNES CELINE
> > Pat;Pat;PAT;PAT
> > etc
> >
> > The first column is correctly spelt.
> > But 2nd 3rd and 4th columns are mis-spellings.
> > 2=No hyphen. 3=Block capitals. 4=Block capitals and no hyphen.
>
> [snip]
>
> I just noticed that in your "Pat" example above that the first and second
> columns for "Pat" are the same which means your script (when fixed) will
> unnecessarily replace "Pat" with "Pat".
>
>
Yep. It's dirty. The 3 columns of wrong data were built from the OK data.
When column 1 has no accents no spaces and no hyphen then neither do the
others.

Any idea why my script was not working when I tried to check for
chr(9)&ANNE&chr(9) ?

TIA
Scottie

Scottie
Fri Dec 03 04:39:37 CST 2004

Tested !
I'm getting an "800A0009" error on
line 76 char 13
In French the message says
"Indice hors de la plage" which means that the
"Index is out of range".

A check on "800A0009" in Google tends to indicate
"Subscript out of range"



Here is a snip from each file :

This is a snip from the prénoms file:

Andrée-Anne;Andree-Anne;ANDREE-ANNE;ANDREE ANNE
Anne-Aymone;Anne-Aymone;ANNE-AYMONE;ANNE AYMONE
Anne-Béatrice;Anne-Beatrice;ANNE-BEATRICE;ANNE BEATRICE
Anne-Bénédicte;Anne-Benedicte;ANNE-BENEDICTE;ANNE BENEDICTE

This is a snip from the address file.
Tabs have been replaced by * for legibility.
Any relation to persons living and/or dead is purely coincidental.

NUMBER*SOCIETY*NAME*FIRSTNAME*F*IN*MRMRS*STREET*STREET2*PCODE*TOWN*COUNTRY
52* *AXXXX SYYYY *ODILE * *IN*MLE *77 SQUARE JEAN MOULIN * *51100 *REIMS
*FRANCE
45* *AXXXX *EDITH * *IN*MME *77 SQUARE JEAN MOULIN * *55100 *VERDUN *FRANCE
3* *MXXXX *JOSE * *IN*MME *DISSEY *77 SQUARE JEAN MOULIN *55100 *VERDUN
*FRANCE

Any ideas ?
TIA

Scottie.



"Scottie Hannigan" <Scottie.Hannigan@wanadoo.fr> a écrit dans le message de
news:41b02947$0$8134$8fcfb975@news.wanadoo.fr...
> "McKirahan" <News@McKirahan.com> a écrit dans le message de
> news:lSLrd.502310$D%.317160@attbi_s51...
> > "Scottie Hannigan" <Scottie.Hannigan@wanadoo.fr> wrote in message
> > news:41af4f90$0$9029$8fcfb975@news.wanadoo.fr...
> > > Hi,
> > > I have a file which is a list of French first-names in four CSV-type
> > > columns
> > >
> > > Agnès-Céline;Agnès Céline;AGNES-CELINE;AGNES CELINE
> > > Pat;Pat;PAT;PAT
> > > etc
> > >
> > > The first column is correctly spelt.
> > > But 2nd 3rd and 4th columns are mis-spellings.
> > > 2=No hyphen. 3=Block capitals. 4=Block capitals and no hyphen.
> >
> > [snip]
> >
> > I just noticed that in your "Pat" example above that the first and
second
> > columns for "Pat" are the same which means your script (when fixed) will
> > unnecessarily replace "Pat" with "Pat".
> >
> >
> Yep. It's dirty. The 3 columns of wrong data were built from the OK data.
> When column 1 has no accents no spaces and no hyphen then neither do the
> others.
>
> Any idea why my script was not working when I tried to check for
> chr(9)&ANNE&chr(9) ?
>
> TIA
> Scottie
>
>

McKirahan
Fri Dec 03 05:07:15 CST 2004

"Scottie Hannigan" <Scottie.Hannigan@wanadoo.fr> wrote in message
news:41b04269$0$16369$8fcfb975@news.wanadoo.fr...
> Tested !
> I'm getting an "800A0009" error on
> line 76 char 13
> In French the message says
> "Indice hors de la plage" which means that the
> "Index is out of range".
>
> A check on "800A0009" in Google tends to indicate
> "Subscript out of range"
>
>
>
> Here is a snip from each file :
>
> This is a snip from the prénoms file:
>
> Andrée-Anne;Andree-Anne;ANDREE-ANNE;ANDREE ANNE
> Anne-Aymone;Anne-Aymone;ANNE-AYMONE;ANNE AYMONE
> Anne-Béatrice;Anne-Beatrice;ANNE-BEATRICE;ANNE BEATRICE
> Anne-Bénédicte;Anne-Benedicte;ANNE-BENEDICTE;ANNE BENEDICTE
>
> This is a snip from the address file.
> Tabs have been replaced by * for legibility.
> Any relation to persons living and/or dead is purely coincidental.
>
> NUMBER*SOCIETY*NAME*FIRSTNAME*F*IN*MRMRS*STREET*STREET2*PCODE*TOWN*COUNTRY
> 52* *AXXXX SYYYY *ODILE * *IN*MLE *77 SQUARE JEAN MOULIN * *51100 *REIMS
> *FRANCE
> 45* *AXXXX *EDITH * *IN*MME *77 SQUARE JEAN MOULIN * *55100 *VERDUN
*FRANCE
> 3* *MXXXX *JOSE * *IN*MME *DISSEY *77 SQUARE JEAN MOULIN *55100 *VERDUN
> *FRANCE
>
> Any ideas ?
> TIA
>
> Scottie.

Your Name and Address file did not contain any of the Names in the (partial)
CSV file so the replacement logic was executed. Could you post the failing
record and the portion of the CSV file that should be used?

To help isolate the error you could insert the following line
WScript.Echo strOT2
before the comment "Process each Good/Bad Name record"
to identify the CSV record that fails.

You may want to run it under CSript.exe to have the "Echo" not pop-up.

Add other WScript.Echo statements as necessary to debug.

Scottie
Fri Dec 03 06:05:43 CST 2004

I have got my script working, after applying two remedies.

MyAdresse = Replace( MyAdresse, _
( chr(9) & MyArray(i,j) &chr(32) & chr(9)) , _
( chr(9) & MyArray(i,0) & chr(9)) , 1 )

Remedy 1 :
Brackets around the replace arguments.
"text_to_find" and "replace_with "
As they are both concatenated strings ...
That seeems to help.

Remedy 2 :
There is an space after each Firtsname in the adresse file.
But it is not a chr(20) type space. It is a chr(32) type space.

So my script is now working but slow.
I'd like to fix yours as with the readall will get as much as possible done
in RAM.

Will reply abou fix in a separate post as PC is about to reboot after
Friday's auto WinUpdate !!
Scottie

McKirahan
Fri Dec 03 06:59:10 CST 2004

"Scottie Hannigan" <Scottie.Hannigan@wanadoo.fr> wrote in message
news:41b05698$0$11768$8fcfb975@news.wanadoo.fr...
> I have got my script working, after applying two remedies.
>
> MyAdresse = Replace( MyAdresse, _
> ( chr(9) & MyArray(i,j) &chr(32) & chr(9)) , _
> ( chr(9) & MyArray(i,0) & chr(9)) , 1 )
>
> Remedy 1 :
> Brackets around the replace arguments.
> "text_to_find" and "replace_with "
> As they are both concatenated strings ...
> That seeems to help.
>
> Remedy 2 :
> There is an space after each Firtsname in the adresse file.
> But it is not a chr(20) type space. It is a chr(32) type space.
>
> So my script is now working but slow.
> I'd like to fix yours as with the readall will get as much as possible
done
> in RAM.
>
> Will reply abou fix in a separate post as PC is about to reboot after
> Friday's auto WinUpdate !!
> Scottie

I'm glad you have a solution.

Here's a variation of my original one that handles your Tab-delimited Name
and Address file. Only the "FIRSTNAME" field is examined for replacement.
However, it does Trim() the FIRSTNAME; that is, it removes spaces on either
side of it when it writes it back out.

For debugging, change the value of cDBG to True but run it under CScript on
the command line; for example, "cscript.exe //nologo prenoms.vbs >
prenoms.txt". Then examine "prenoms.txt" for problems.

'****
'* This Visual Basic Script (VBS) program does the following:
'* 1) Read a CSV into an array; display the record count.
'* (actually its a Semicolon-Separated-Values) file.
'* Each record contains 4 versions of a name;
'* The first name is "Good", the other three are "Bad" names.
'* Create a secondary array used for processing.
'* 2) Read a Name and Address file into an array.
'* 3) Examine FIRSTNAME replacing the "Bad" with the "Good".
'* 4) Write each record to a new Name and Address file.
'* 5) Display the number of "Bad" names that were replaced.
'****
Option Explicit
'*
'* Declare Constants
'*
Const cVBS = "prenoms.vbs"
Const cCSV = "prenoms.csv"
Const cDBG = False '= Debug in CScript: WScript.Echo (True/False)
Const cOT1 = "lapat1.txt"
Const cOT2 = "lapatOut.txt"
Const cFOL = "C:\VBS STUFF\"
'*
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
'*
'* Delare Variables
'*
Dim arrCSV
Dim intCSV
Dim strCSV
Dim arrNAM()
Dim intNAM
Dim strNAM
Dim arrOT1
Dim intOT1
Dim strOT1
Dim intOT2
Dim strOT2
Dim arrPRE
Dim strPRE
'*
'* Declare Objects
'*
Dim objFSO
Dim objOTF
Dim objOT1
Dim objOT2
'*
'* Assign Objects
'*
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOTF = objFSO.OpenTextFile(cFOL & cCSV,ForReading)
Set objOT1 = objFSO.OpenTextFile(cFOL & cOT1,ForReading)
Set objOT2 = objFSO.OpenTextFile(cFOL & cOT2,ForWriting,True)
'*
'* Read Prénoms
'*
strCSV = objOTF.ReadAll()
arrCSV = Split(strCSV,vbCrLf)
MsgBox "LineCount Prénoms : " & UBound(arrCSV) + 1,vbInformation,cVBS
'*
'* Prep Prénoms
'*
For intCSV = 0 To UBound(arrCSV)
strNAM = Split(arrCSV(intCSV),";")
If cDBG Then WScript.Echo intCSV & ". " & vbTab & strNAM(0)
ReDim Preserve arrNAM(UBound(strNAM),intCSV)
arrNAM(0,intCSV) = strNAM(0)
For intNAM = 1 To UBound(strNAM)
If cDBG Then WScript.Echo vbTab & Trim(strNAM(intNAM))
arrNAM(intNAM,intCSV) = vbTab & Trim(strNAM(intNAM)) & vbTab
Next
Next
'*
If cDBG Then
For intCSV = 0 To UBound(arrNAM,2)
For intNAM = 0 To UBound(arrNAM,1)
WScript.Echo intCSV & "." & intNAM & " = " &
arrNAM(intNAM,intCSV)
Next
Next
End If
'*
'* Read the input file into an array
'*
strOT1 = objOT1.ReadAll()
arrOT1 = Split(strOT1,vbCrLf)
intOT2 = 0
objOT2.WriteLine(arrOT1(0))
'*
'* Process each Name and Address record (except Header)
'*
For intOT1 = 1 To UBound(arrOT1)
strOT2 = arrOT1(intOT1)
strOT2 = vbTab & strOT2 & vbTab
arrPRE = Split(strOT2,vbTab)
If cDBG Then WScript.Echo arrPRE(4) & " : " & strOT2
strPRE = vbTab & Trim(arrPRE(4)) & vbTab
'*
'* Process each Good/Bad Name
'*
For intCSV = 0 To UBound(arrNAM,2)
For intNAM = 1 To UBound(arrNAM,1)
'*
'* Find "Bad" names and replace them with the "Good" name
'*
If cDBG Then WScript.Echo strPRE & " : " &
arrNAM(intNAM,intCSV)
If strPRE = arrNAM(intNAM,intCSV) Then
If cDBG Then WScript.Echo strPRE & " ! "
arrPRE(4) = arrNAM(intCSV,0)
intOT2 = intOT2 + 1
End If
Next
Next
'*
'* Write out the new Name and Address record
'*
strOT2 = Join(arrPRE,vbTab)
strOT2 = Mid(strOT2,2,Len(strOT2)-2)
objOT2.WriteLine(strOT2)
Next
'*
'* Destroy Objects
'*
Set objOTF = Nothing
Set objOT1 = Nothing
Set objOT2 = Nothing
Set objFSO = Nothing
'*
'* Finished
'*
MsgBox "Changed Prénoms : " & intOT2,vbInformation,cVBS