Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

[VBS] Find and rename content in csv

Status
Not open for further replies.
Leosy

Leosy

Technical User
Apr 13, 2012
49
PL
Hello.

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

For example

Code: 
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.

Code: 
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 :(
 
Sort by date Sort by votes
InStr is a function that returns the position of a search strin, it doesn't do anything to the string.

Change this:
Code: 
 If InStr(strLine, "the req ops") Then
        [red][strikethrough]InStr(strLine, "OK")[/strikethrough][/red]
    End If
into this:
Code: 
If InStr(strLine, "the req ops") Then
        Replace(strLine, "the req ops", "OK")
    End If

I suppose your "strNewContents" actually holds all the contents correctly?

Cheers,
MakeItSo

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.
 
Upvote 0 Downvote
Dang! Preview, man, preview! [blush]
==>Ignore the [strikethrough]....

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.
 
Upvote 0 Downvote
Hey.

Thanks for answer.

But I have error

14, 46) Microsoft VBScript compilation error: Cannot use parentheses when calling a Sub
Click to expand...

Code: 
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
        Replace(strLine, "the req ops", "OK")
    End If
Loop

objFile.Close

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

objFile.Write strNewContents
objFile.Close

End Function
 
Upvote 0 Downvote
What about this ?
Code: 
Function OK()
Const ForReading = 1
Const ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Rapport.csv", ForReading)
strNewContents = Replace(objFile.ReadAll, "the req ops", "OK")
objFile.Close
Set objFile = objFSO.OpenTextFile("C:\Rapport.csv", ForWriting)
objFile.Write strNewContents
objFile.Close
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Thanks PHV for correcting my blunder.
I hate Mondays...

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.
 
Upvote 0 Downvote
PHV and MakeItSo


WORKS !
THANKS GUYS ! YOUR GREAT !
 
Upvote 0 Downvote
BTW I have another problem.


Is it possible to replace via VBS values in "column" 5 all values different than 0 change to 0 ?

for example 1 and 2 change to 0 but only in "column" - ERROR


Code: 
DATE;DEPART;CLASSE;SERVEUR;[b]ERROR[/b];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;[b]1[/b];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;[b]2[/b];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

I can't search for any 1 or 2 and change it to 0 because digit 1 and 2 exist in many many other "coulmns"
:(
 
Upvote 0 Downvote
You can use split for that:

Code: 
tmpArr=Split(strLine, ";")
tmpArr(4)="0"
strNewContents=Join(tmpArr,";")

Explanation: split the line into a temporary array.
Change the value in array field 4 (5th field, array is 0-based)
Re-join the array into a tring using ";" delimiter.
Voilà - 5th column is now always 0.

Cheers,
MakeItSo


“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.
 
Upvote 0 Downvote
Hmm

It's getting harder Than I thought

Code: 
Function OK()

Const ForReading = 1
Const ForWriting = 2

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

Do
strLine = objFile.Readline
tmpArr=Split(strLine, ";")
tmpArr(4)="0"
strNewContents=Join(tmpArr,";")
Loop

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

objFile.Write strNewContents
objFile.Close

End Function


DATE;DEPART;CLASSE;SERVEUR;ERROR;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;1;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;2;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
Click to expand...

It's replacing header "error" in to 0 and not going to the next row...

I think there should be somewhere added that "if found 1 or 2" in "array(4)" change it to 0.... hmmmmm.. crap I'm learning to slow this...

Microsoft VBScript runtime error: Input past end of file
Click to expand...
 
Upvote 0 Downvote
1.) yes, you need to start doing this from the second line onward of course.
2.) in your loop you are always overwriting strNewContents with the last read line. you do not concatenate the contents!
3.) now you don't care about "the req ops" anymore? Cause I can't see that in your code.

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.
 
Upvote 0 Downvote
3. Yes. this is done. I have STEP by STEP, script by SCRIPT, and in this step of my work I need to do this "change to 0" if you find "1 or 2" in column "5"

1 and 2. Yes I know but I'm tring to "get it all to together"

;)
 
Upvote 0 Downvote
Then do just that: if 1 or 2 then...
all you need for that is to replace
Code: 
tmpArr(4)="0"
with
Code: 
If tmpArr(4)="1" or tmpArr(4)="2" Then tmpArr(4)="0"


“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.
 
Upvote 0 Downvote
What I'm doing wrong ?

Code: 
Function OK()

Const ForReading = 1
Const ForWriting = 2

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

[b]Do
strLine = objFile.Readline
tmpArr=Split(strLine, ";")
If tmpArr(4)="1" or tmpArr(4)="2" Then tmpArr(4)="0"

Loop
[/b]

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

objFile.Write strNewContents
objFile.Close

End Function

(12, 1) Microsoft VBScript runtime error: Input past end of file
Click to expand...
 
Upvote 0 Downvote
Set objFile = objFSO.OpenTextFile("C:\ITPA_Monitoring_Bego\Rapport.csv", ForWriting)

objFile.Write strNewContents
Click to expand...
strNewContents is empty!
You're not doing anything useful in that Do Loop!
You are reading a line, replacing a value and then reading the next line - all without ever doing anything with the line contents, like storing the values in a string!

Where has all the rest of your code gone???

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.
 
Upvote 0 Downvote
Yup this is a good question,. Where has it gone. I've missed something.
I'm trying to get your ideas in to my VBS :)

I need to split to arrays and change "column 5" when find 1 or 2 in to 0

Let's do this from the beginning:


Code: 
Function OK()

Const ForReading = 1
Const ForWriting = 2

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

[b]
Do
    strLine = objFile.ReadLine
	tmpArr=Split(strLine, ";")
    If tmpArr(4)="1" or tmpArr(4)="2" Then tmpArr(4)="0"
	strNewContents=Join(tmpArr,";")
Loop
[/b]

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

objFile.Write strNewContents
objFile.Close

End Function

trying to figure it out...
 
Upvote 0 Downvote
You are reading a line, replacing a value and then reading the next line - all without ever doing anything with the line contents, like storing the values in a string!
Click to expand...

Code: 
Do
strLine = objFile.Readline
tmpArr=Split(strLine, ";")
If tmpArr(4)="1" or tmpArr(4)="2" Then tmpArr(4)="0" End If
strNewContents=Join(tmpArr,";")
Loop

This should work but it's not ;/

Microsoft VBScript runtime error: Input past end of file
Click to expand...
 
Upvote 0 Downvote
Code: 
strLine = objFile.Readline
tmpArr=Split(strLine, ";")
If tmpArr(4)="ERREURS" or tmpArr(4)="2" Then tmpArr(4)="0" End If
strNewContents=Join(tmpArr,";")

objFile.Close

When I use this it's only doing it in first row. So now I need to modify it to check all rows to the end...
 
Upvote 0 Downvote
Your Do loop continually tries to read new lines, you must check to see when you hit the end of the file. You had the check in the first few posts, but seem to have lost it along the way.

Code: 
Do Until objFile.AtEndOfStream
 
Upvote 0 Downvote
I'm trying to get your ideas in to my VBS
Click to expand...
That's all fine, but don't delete the previous ideas for it, they're all part of it!
[tongue]

OK, I've put all of the above hints into one function with comments so you know what happens where:
Code: 
Function OK()
Const ForReading = 1
Const ForWriting = 2
	
	Set objFSO = CreateObject("Scripting.FileSystemObject")
	Set objFile = objFSO.OpenTextFile("C:\Rapport.csv", ForReading)
	
	Do While Not objFile.AtEndOfStream
		'Read Contents line by line
		strLine = objFile.Readline
		
		'Check value in 5th column, replace to 0 if 1 or 2
		tmpArr=Split(strLine, ";")
		If tmpArr(4)="1" or tmpArr(4)="2" Then tmpArr(4)="0"
		strNewLine=Join(tmpArr,";")
		
		'Replace "the req ops" with "OK"
		strNewLine = Replace(strNewLine, "the req ops", "OK")
		
		'Add altered line to new content string
		strNewContents=strNewContents & strNewLine & vbCrLf
	Loop
	
	'overwrite file with new content
	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

That should do the trick.

;-)

Cheers,
MakeItSo

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

lunter
  • Locked
  • Question
How to create vbArray+vbByte (8209) in *.vbs stript
Replies
0
Views
225
lunter
lunter
Michastro
  • Locked
  • Question
Send an order to an IP power in VBS
Replies
0
Views
199
Michastro
Michastro
bdwilcox
  • Locked
  • Question
Trying to adapt VBScript to search if machine is in an AD group 6
Replies
8
Views
997
SoftwareRT
SoftwareRT
K
  • Locked
  • Question
VBSCRIPT dont work windows 11 23h2 ???
Replies
2
Views
499
SoftwareRT
SoftwareRT
Alexvb6
  • Locked
  • Question
Deprecation of VBScript + Classic ASP: How to Ask Microsoft to cancel their planned craziness
Replies
0
Views
358
Alexvb6
Alexvb6

Part and Inventory Search

Sponsor

Back
Top