Read and Write SQL text

Question

User1510859543 posted

I am writing code to open one .sql file that was output from SQL Server, replace some text and write out a new .sql file.  The process works but the output file is losing all the formatting that the input .sql file had. The code I am using is below.

    Private Sub WriteSQLFile()
        Dim file As String = "C:\ketofiles\03trgPatientsAudit.sql"
        Dim fileout As String = "C:\ketofiles\03trgPatientsAuditNew.sql"
        Dim strTextout As String = ""

        If System.IO.File.Exists(file) Then
            Dim lines As String() = System.IO.File.ReadAllLines(file)

            For Each ln As String In lines
                strTextout &= Replace(ln, "[ketodata]", "[kdctest]")
            Next
            System.IO.File.WriteAllText(fileout, strTextout)
        End If
    End Sub

Below is a sample of the top of the input and output files.

USE [ketodata]
GO
/****** Object:  Trigger [dbo].[trgPatientsAudit]    Script Date: 10/21/2019 5:32:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




--new file output
USE [kdctest]GO/****** Object:  Trigger [dbo].[trgPatientsAudit]    Script Date: 10/21/2019 5:32:15 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- 

How can I get the output text file to be exactly like the format of the input file (which was output from SSMS).

Answer 1

User475983607 posted

Put the lines break back.

strTextout &= Replace(ln, "[ketodata]", "[kdctest]") & vbCrLf

Answer 2

User1510859543 posted

That was too simple!