[VBS] Find and rename content in csv

[Leosy]

Hello.

I need to open CSV file, find some "string" in a row and rename it.

For example

DATE;DEPART;CLASSE;SERVEUR;ERREURS;MESSAGE;SCHEDULE;TYP;DUREE;TAILLE(Kb);FICHIERS;RETENTION;WARS Date ouverture;WARS Date fermeture;Commentaires
2012-04-22;21:01:25;lala_nbondv02_data;nbondv02;0;the req ops;lmejvd-lala-2sem;lalaérentielle;00:12:53;1909225;1447;2 Sem
2012-04-22;21:00:00;lala_nbondv02_sys;nbondv02;0;the req ops;lmmjvd-lala-2sem;lalaérentielle;00:03:58;371778;2184;2 Sem
2012-04-22;21:00:59;lala_nbondv03_data;nbondv03;0;OK;lmejvd-lala-2sem;lalaérentielle;00:07:49;892604;6695;2 Sem
2012-04-22;21:00:00;lala_nbondv03_sys;nbondv03;0;OK;lmmjvd-lala-2sem;lalaérentielle;00:08:38;1209603;399;5 Sem
2012-04-22;21:00:57;lala_nbondv04_data;nbondv04;0;OK;lmejvd-lala-2sem;lalaérentielle;03:22:41;135497795;300;2 Sem

And I need to find string "the req ops" and change it for "OK"

Is it possible ?

I have some part of the code but not sure how to modify it.

Function OK()

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Rapport.csv", ForReading)

Do Until objFile.AtEndOfStream
    strLine = objFile.ReadLine
    If InStr(strLine, "the req ops") Then
        InStr(strLine, "OK")
    End If
Loop

objFile.Close

Set objFile = objFSO.OpenTextFile("C:\Rapport.csv", ForWriting)

objFile.Write strNewContents
objFile.Close

End Function

but it doesn;t work

 

[guitarzan]

Leosy:

Somewhere along the way your loop went from:
Do Until objFile.AtEndOfStream
Loop

to the never-terminating:
Do
Loop

 

[Leosy]

MakeItSo

Teach me master ! respect. I've modify it a bit for my use. Thank you very much.

Function OK()

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Rapport.csv", ForReading)

[b]

Do Until objFile.AtEndOfStream

strLine = objFile.Readline
tmpArr=Split(strLine, ";")

If tmpArr(4)="1" or tmpArr(4)="2" Then tmpArr(4)="0" End If

strNewLine=Join(tmpArr,";")
strNewContents=strNewContents & strNewLine & vbCrLf


loop
objFile.Close

[/b]

Set objFile = objFSO.OpenTextFile("C:\Rapport.csv", ForWriting)

objFile.Write strNewContents
objFile.Close

'destroy objects to release memory
Set objFile = Nothing
Set objFSO = Nothing

End Function

But I found some error. I have log where in the end are 2 empty lines. and the script is "failing" on it.

When I remove it manually it works.

Is it possible to enter a code to delete 2 empty lines in the end of the file and than do this

...If tmpArr(4)="1" or tmpArr(4)="2" Then tmpArr(4)="0" End If

 

[jges]

But I found some error. I have log where in the end are 2 empty lines. and the script is "failing" on it.

You'll have to add a check for the empty lines.

Something like this:

    [COLOR=blue]Do[/color] [COLOR=blue]While[/color] [COLOR=blue]Not[/color] objFile.AtEndOfStream  
 [COLOR=green]'Read Contents line by line[/color]
        strLine [COLOR=blue]=[/color] objFile.Readline  
		[highlight]strLine [COLOR=blue]=[/color] Trim(strLine)[/highlight]  
		  
		[highlight]if strLine <> "" [COLOR=blue]then[/color][/highlight]  
			  [COLOR=green]'Check value in 5th column, replace to 0 if 1 or 2[/color]
			tmpArr=Split(strLine, ";")  
			If tmpArr(4)="1" [COLOR=blue]or[/color] tmpArr(4)="2" [COLOR=blue]Then[/color] tmpArr(4)="0"  
			strNewLine=Join(tmpArr,";")  
			  
			  [COLOR=green]'Replace "the req ops" with "OK"[/color]
			strNewLine [COLOR=blue]=[/color] Replace(strNewLine, "the req ops", "OK")  
			  
			  [COLOR=green]'Add altered line to new content string[/color]
			strNewContents=strNewContents [COLOR=blue]&[/color] strNewLine [COLOR=blue]&[/color] vbCrLf  
		[highlight]end [COLOR=blue]if[/color][/highlight]  
		  
    [COLOR=blue]Loop[/color]

 

[Leosy]

Great jges it works
MakeItSothank you very much for your help!

Function OK()

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Rapport_de.csv", ForReading)

Do While Not objFile.AtEndOfStream

strLine = objFile.Readline
strLine = Trim(strLine)  
          
if strLine <> "" then
tmpArr=Split(strLine, ";")
If tmpArr(4)="1" or tmpArr(4)="2" Then tmpArr(4)="0" End If
strNewLine=Join(tmpArr,";")
strNewContents=strNewContents & strNewLine & vbCrLf
End If
Loop
objFile.Close

Set objFile = objFSO.OpenTextFile("C:\Rapport_de.csv", ForWriting)

objFile.Write strNewContents
objFile.Close

'destroy objects to release memory
Set objFile = Nothing
Set objFSO = Nothing

End Function