Replace words in txt file

leandrorezende

Forums

I am working in a script that open an txt file and replace any caracter that I choose for another one, so far everything is ok.
The problem is when the file that I am reading contains quote marks(") as at example attached(input.txt).
In this case, when the script faces one line that contains a simple quote mark in the middle is enough for the program launch an error message to the user (image attached). 
It's seems that IDEAScript does't deal well with this situation, when the files contains quote marks and you try to manipulate a string at any circumstances.
Having in mind that the possibility of a txt file containing quote marks is relatively high I can't just ignore this scenario.
Is there a way to overcome this obstacle using IDEAScript?
 
The script that I am using it's attached right below.

Brian Element Thu, 07/27/2017 - 11:34

Hi Leandrorezende,

That is an interesting problem as it seems to work fine with a double quote if you do it in the equation editor. What I did is to create another loop that looks at each character and removes the double quote from the text line.  Depending on how big your file is it will take a bit longer to run.

 


Function Main
	Const ForReading = 1, ForWriting = 2, ForAppending = 8
	Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

	
	Dim fsoIn As Object
	Dim fsoStreamIn As StreamReader
	Dim strLine As String
	Dim searchFlag As Boolean 
	Dim sChar As String
	Dim iLen As Integer
	Dim i As Integer
	Dim sLine As String
	searchFlag = False
	fileNameIn = "C:\input.txt"
	Set fsoIn = CreateObject("Scripting.FileSystemObject")
	Set fsoStreamIn = fsoIn.OpenTextFile(fileNameIn, ForReading, True, TristateUseDefault )
	Do Until fsoStreamIn.AtEndOfStream
		Set strLine = fsoStreamIn.ReadLine
		iLen = Len(strLine)
		sLine = ""
		'Loops to remove any double quotes (")
		For i = 1 To iLen
			sChar = Mid(strLine, i, 1)
			If sChar <> Chr(34) Then
				sLine = sLine & sChar
			End If
		Next i
		MsgBox iReplace(sLine, "a","A")
	Loop 
	fsoStreamIn.Close
End Function

leandrorezende Thu, 07/27/2017 - 13:05

Yes, I also did this test with an random script gerenerated by IDEA and using a base with row containing quote marks and it's turn out to work fine, but when you are dealing with a txt file it behaves different and I don't know how to fix it.
Your solution worked fine, but the problem is that by removing the quote marks it's manipulating data that it can't be changed, only the exactly replacement word must be changed.
Do you believe this is an IDEAScripting bug?

Brian Element Thu, 07/27/2017 - 13:12

Yes, it does look like it could be an IDEAScript bug.  Ok, so you want to keep the double quote.  Then instead of using the iReplace use the loop I was using.  If you find the character replace it with the new one and if not just add the current one to the line. 

 


Function Main
	Const ForReading = 1, ForWriting = 2, ForAppending = 8
	Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

	
	Dim fsoIn As Object
	Dim fsoStreamIn As StreamReader
	Dim strLine As String
	Dim searchFlag As Boolean 
	Dim sChar As String
	Dim iLen As Integer
	Dim i As Integer
	Dim sLine As String
	searchFlag = False
	fileNameIn = "D:\Projects\Samples\Source Files.ILB\input.txt"
	Set fsoIn = CreateObject("Scripting.FileSystemObject")
	Set fsoStreamIn = fsoIn.OpenTextFile(fileNameIn, ForReading, True, TristateUseDefault )
	Do Until fsoStreamIn.AtEndOfStream
		Set strLine = fsoStreamIn.ReadLine
		iLen = Len(strLine)
		sLine = ""
		'Loops to remove any double quotes (")
		For i = 1 To iLen
			sChar = Mid(strLine, i, 1)
			If sChar <> "A"Then
				sLine = sLine & sChar
			Else
				sLine = sLine & "a"
			End If
		Next i
		MsgBox sLine
	Loop 
	fsoStreamIn.Close
End Function

leandrorezende Thu, 07/27/2017 - 14:36

Awesome, now i reached my goal!
Enjoying the topic, i would like to ask one more question...
Why there is some functions that only works if you put an "i" in the beginning while others you can just type their name as they are?
For example Len() and iReplace

Brian Element Thu, 07/27/2017 - 14:50

That is great to hear that it is working.

That is an interesting question.  The functions, like Len(), that don't have an i in front of them are visual basic functions.  The functions that have an i in front of them are IDEA equation editor functions.  For functions that are available in the equation editor you remove the @ and replace it with an i to get it work.  Not all @ functions work as they stopped doing this with V9 of IDEA, so any new functions from V9 and up do not have an i equivalent.  Also sometimes you can have a function like the mid() or the iMid() that work the same but one is from VB and the other is an IDEA function.

Hope that makes sense.

Brian

leandrorezende Thu, 07/27/2017 - 16:25

Oh nice, Thanks for clearing it up, this was a little bit confusing in my head.
Once again, thanks very much for the attention Brian.
 

JHDCC Thu, 08/15/2019 - 05:35

Hi all,

I think the above script almost does what I'm after, but I'm not familiar with VB to be able to adapt it to my needs.

I have an import txt file, sticking with the example in the above script, "D:\Projects\Samples\Source Files.ILB\input.txt"

This file is produced from a system which outputs addresses as seperate fields, encapsulated with" ie "Line 1, Line 2, Line 3, Line 4, Postcode"

Where Line 1 is a house name, the house name is being entered in the format "house name" ie ""Line 1",Line 2, Line 3, Line 4, Postcode"

So data is set out as

"Line 1, Line 2, Line 3, Line 4, Postcode"
"Line 1, Line 2, Line 3, Line 4, Postcode"
""Line 1",Line 2, Line 3, Line 4, Postcode"
"Line 1, Line 2, Line 3, Line 4, Postcode"

My import works if the " around line 1 are stripped. I would presume if I were to strip " entirely from the import CSV file the import would work.

If the import file is not altered, the lines with the house names end up with several lines in one field, and the rest of the fields staggered, as a result of the interpretation of the encapsulation.

As a temporary workaround, the offending lines are being manually altered before running the import, but it would be useful to automate the process.

Would someone be able to adapt the above script to remove all " from the import file?
Or does anyone have any other suggestions of a better way to achieve what I'm after?

raoul.kelly Thu, 08/15/2019 - 12:55

A very quick and nasty solution for someone like me with limited code ability  is to open the file in Notepad  , perform a seach and replace with the following :
Find ""   , replace with "
Find ",    replace with "
then save the file ...this will be make the changes you need in your example in bulk
 

JHDCC Mon, 08/19/2019 - 04:33

Thanks for the reply Raoul. That's what I'm doing currently when I say removing them manually, but i want to integrate this process into the script.

klmi Tue, 08/20/2019 - 11:11

When using IDEA 10.3 and following you can do that job with the Python code below:


# Constants
PATH = "C:/IDEA Projects/Samples/Makros.ILB"
FILE = "test.txt"

# Read imput file
with open(PATH + "/" + FILE, "r") as f:
data = f.read()
f.close()

# Replace
data = data.replace('"', '')

# Write output file
with open(PATH + "/" + FILE, "w") as f:
f.write(data)
f.close()

For learning reasons the code is not as short as possible!