Replacing string in text file with another string

Question

Replacing string in text file with another string

VoldemarG

Hall of Fame

Points: 3883
More actions
November 12, 2018 at 10:34 am

#372969

I need to replace all occurrences of 'ABCD' in .txt file with 'EFGH'.
Is it possible to do from an SSIS task? (which one?)
Likes to play Chess

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247158 More actions · Answer 1

VoldemarG - Monday, November 12, 2018 10:34 AM
I need to replace all occurrences of 'ABCD' in .txt file with 'EFGH'.
Is it possible to do from an SSIS task? (which one?)

I'd do this in C# using a script task.

VoldemarG Hall of Fame Points: 3883 More actions · Answer 2

Phil Parkin - Monday, November 12, 2018 10:38 AM
VoldemarG - Monday, November 12, 2018 10:34 AM
I need to replace all occurrences of 'ABCD' in .txt file with 'EFGH'.
Is it possible to do from an SSIS task? (which one?)
I'd do this in C# using a script task.

I tried different ways (using VB but doesn't matter...)
I tried this way (does not work). using Data Tools 2015.

Const ForReading = 1 Const ForWriting = 2 Dim objFSO, objFile, strText, strNewText objFSO = CreateObject("Scripting.FileSystemObject") objFile = objFSO.OpenTextFile("C:\Users\newtons\Desktop\Text.txt", ForReading) strText = objFile.ReadAll objFile.Close()strNewText = Replace(strText, "WTOTA", "TOTAL") objFile = objFSO.OpenTextFile("C:\Users\newtons\Desktop\Text.txt", ForWriting) objFile.WriteLine(strNewText) objFile.Close() objFSO = Nothing objFile = Nothing

and i also tried this way: (doesn't work either)

Imports System.IO // in imports section
then
Dim filePath As String = Dts.Variables("User::FullFilePath").Value.ToString() File.WriteAllText(filePath, File.ReadAllText(filePath).Replace("Jim", "James"))

Likes to play Chess

Phil Parkin SSC Guru Points: 247158 More actions · Answer 3

If the files are not huge (ie, they will confortably fit into RAM), you can use the technique here.

VoldemarG Hall of Fame Points: 3883 More actions · Answer 4

I have tried this way as well but code does not compile...

See the attached too, same.

Public Sub Main()
   '
   Dim reader As System.IO.StreamReader = New System.IO.StreamReader(Dts.Variables("FullFilePath").Value)
   Dim writer As System.IO.StreamWriter = New System.IO.StreamWriter(Dts.Variables("FullFilePath2").Value)

   While Not reader.EndOfStream
    Dim theLine As String = reader.ReadLine()
    ' Do your replacements here
    theLine = theLine.Replace("WTOTA", "TOTAL")
    theLine = theLine.Replace("oldValue1", "newValue1")
    ' etc, etc, etc.

    ' Write the cleansed output to the new file
    writer.WriteLine(theLine)
   End While

   writer.Flush()
   writer.Close()
   '
   Dts.TaskResult = ScriptResults.Success
End Sub

Likes to play Chess

VoldemarG Hall of Fame Points: 3883 More actions · Answer 5

I finally got it working THIS WAY:

   public void Main()
        {
    string fileText;
    try
    {
      fileText = System.IO.File.ReadAllText(Dts.Variables["FullFilePath"].Value.ToString());
      fileText = fileText.Replace("WTOTA", "TOTAL");
      System.IO.File.WriteAllText(Dts.Variables["FullFilePath"].Value.ToString(), fileText);

      Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception ex)
    {
      Dts.TaskResult = (int)ScriptResults.Failure;
      throw ex;
    }
    finally
    {
      fileText = null;
    }

Dts.TaskResult = (int)ScriptResults.Success;
}

Likes to play Chess

Jeff Moden SSC Guru Points: 1004592 More actions · Answer 6

VoldemarG - Monday, November 12, 2018 2:59 PM
I finally got it working THIS WAY:
   public void Main()
        {
    string fileText;
    try
    {
      fileText = System.IO.File.ReadAllText(Dts.Variables["FullFilePath"].Value.ToString());
      fileText = fileText.Replace("WTOTA", "TOTAL");
      System.IO.File.WriteAllText(Dts.Variables["FullFilePath"].Value.ToString(), fileText);
      Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception ex)
    {
      Dts.TaskResult = (int)ScriptResults.Failure;
      throw ex;
    }
    finally
    {
      fileText = null;
    }
    Dts.TaskResult = (int)ScriptResults.Success;
        }

If you have a huge file, what does the System.IO.File.ReadAllText do to memory on the system?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 247158 More actions · Answer 7

Jeff Moden - Tuesday, November 13, 2018 7:37 AM
If you have a huge file, what does the System.IO.File.ReadAllText do to memory on the system?

Consumes it!
Huge files need to be streamed in and out to avoid this.

Jeff Moden SSC Guru Points: 1004592 More actions · Answer 8

Phil Parkin - Tuesday, November 13, 2018 8:12 AM
Jeff Moden - Tuesday, November 13, 2018 7:37 AM
If you have a huge file, what does the System.IO.File.ReadAllText do to memory on the system?
Consumes it!
Huge files need to be streamed in and out to avoid this.

That's what I was reading into it. Heh.... might as well make a call to Notepad then.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Replacing string in text file with another string

Cookies on SQLServerCentral