INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...This was the ONLY place that I could find information that I could use to resolve the problem. So thanks once again to member TomSark and the SQL forum!..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > Languages > VBScript Forum

[VBS] Find and rename content in csv

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 :(

MakeItSo (Programmer)

23 Apr 12 6:01

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
[strikethrough]InStr(strLine, "OK")[/strikethrough]
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.

MakeItSo (Programmer)

23 Apr 12 6:02

Dang! Preview, man, preview! blush

==>Ignore the [strikethrough]....

Leosy (TechnicalUser)

23 Apr 12 6:14

Hey.

Thanks for answer.

But I have error

Quote:
14, 46) Microsoft VBScript compilation error: Cannot use parentheses when calling a Sub

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

PHV (MIS)

23 Apr 12 6:27

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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

MakeItSo (Programmer)

23 Apr 12 6:36

Thanks PHV for correcting my blunder.
I hate Mondays...

Leosy (TechnicalUser)

23 Apr 12 6:40

PHV and MakeItSo

WORKS !
THANKS GUYS ! YOUR GREAT !

Leosy (TechnicalUser)

23 Apr 12 7:58

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;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

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"
:(

MakeItSo (Programmer)

23 Apr 12 8:37

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

Leosy (TechnicalUser)

23 Apr 12 9:27

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

Quote:

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

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

Quote:

Microsoft VBScript runtime error: Input past end of file

MakeItSo (Programmer)

23 Apr 12 9:32

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.

Leosy (TechnicalUser)

23 Apr 12 9:37

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"

;)

MakeItSo (Programmer)

23 Apr 12 10:26

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"

Leosy (TechnicalUser)

24 Apr 12 5:40

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)

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

Loop

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

objFile.Write strNewContents
objFile.Close

End Function

Quote:

(12, 1) Microsoft VBScript runtime error: Input past end of file

MakeItSo (Programmer)

24 Apr 12 7:01

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

objFile.Write strNewContents

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???

Leosy (TechnicalUser)

24 Apr 12 7:29

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)

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

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

objFile.Write strNewContents
objFile.Close

End Function

trying to figure it out...

Leosy (TechnicalUser)

24 Apr 12 8:05

Quote:
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!

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 ;/

Quote:
Microsoft VBScript runtime error: Input past end of file

Leosy (TechnicalUser)

24 Apr 12 8:27

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

jges (TechnicalUser)

24 Apr 12 8:27

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

MakeItSo (Programmer)

24 Apr 12 8:28

Quote:
I'm trying to get your ideas in to my VBS

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

guitarzan (Programmer)

24 Apr 12 8:31

Leosy:

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

to the never-terminating:
Do
Loop

Leosy (TechnicalUser)

24 Apr 12 9:10

MakeItSo

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

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
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

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

CODE

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

jges (TechnicalUser)

24 Apr 12 10:00

Quote (Leosy):
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:

CODE

    Do While Not objFile.AtEndOfStream
'Read Contents line by line
        strLine = objFile.Readline
        strLine = Trim(strLine)

        if strLine <> "" then
              '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
        end if

    Loop

Leosy (TechnicalUser)

25 Apr 12 2:45

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

CODE

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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To VBScript

Member Login

Come Join Us!

LINK TO THIS FORUM!

Tek-Tips LinkedIn® Group

Partner With Us!

Feedback

Geography

Tek-Tips Shirts!

[VBS] Find and rename content in csv

CODE

CODE

CODE

CODE

Quote:

CODE

CODE

CODE

CODE

CODE

Quote:

Quote:

CODE

CODE

CODE

Quote:

Quote:

CODE

Quote:

CODE

Quote:

CODE

CODE

Quote:

CODE

CODE

CODE

Quote (Leosy):

CODE

CODE

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Back To Forum

Join Tek-Tips® Today!

Tek-Tips LinkedIn^® Group

Join Tek-Tips^® Today!