I need to read through a file containing postgres DML (inserts, updates, deletes) and remove characters to make the DML compatible with SQL Server DML syntax

  • I have a script file that looks like this: Generated by Devart dbForge Data Compare for PostgreSQL

    -- Script was generated by Devart dbForge Data Compare for PostgreSQL, Version 1.0.123.0

    -- Product Home Page: http://www.devart.com/dbforge/postgresql/datacompare

    -- Script date 8/18/2016 1:59:43 PM

    -- Source server version: PostgreSQL 9.1.23, compiled by Visual C++ build 1500, 64-bit

    -- Source connection string: User Id=postgres;Host=Localhost;Database=Production;Unicode=True;

    -- Target server version: PostgreSQL 9.1.23, compiled by Visual C++ build 1500, 64-bit

    -- Target connection string: User Id=postgres;Host=Localhost;Database=Staging;Unicode=True;

    -- Run this script against Staging to synchronize it with Production

    -- Please backup your target database before running this script

    --

    -- Inserting data into table "Staging"."Customer"

    --

    INSERT INTO "Staging"."Customer"("CustID", "CustName", "Address1", "Address2", "City", "State", "Zip") VALUES

    (1, E'Lowes ', E'102 Lowes Drive ', E' ', E'Philadelphia ', E'Pa', 15029),

    (4, E'Merriwethers ', E'322 Fisher Street ', E' ', E'Philadelphia ', E'Pa', 15655),

    (5, E'Happy Harrys ', E'888 Herry Road ', E' ', E'Philadelphia ', E'Pa', 15112),

    (7, E'Carolee ', E'102 CompUSA Drive ', E' ', E'Philadelphia ', E'Pa', 15029),

    (8, E'Brandsmart USA ', E'88 Brandsmart Drive ', E' ', E'Philadelphia ', E'Pa', 15122);

    You will notice that there are characters in the value fields that are preceded by (a space and 'E') Can anyone tell me what code I might write or show me some T-SQL that will read through the .txt file containing the (space and 'E') and replace these 2 characters with a "Space". I need to clean this code so that I can use the file containing the cleanedup DML in SQL Server. Suggestions welcome.Thank you.

  • GaMusicMan (8/18/2016)


    I have a script file that looks like this: Generated by Devart dbForge Data Compare for PostgreSQL

    -- Script was generated by Devart dbForge Data Compare for PostgreSQL, Version 1.0.123.0

    -- Product Home Page: http://www.devart.com/dbforge/postgresql/datacompare

    -- Script date 8/18/2016 1:59:43 PM

    -- Source server version: PostgreSQL 9.1.23, compiled by Visual C++ build 1500, 64-bit

    -- Source connection string: User Id=postgres;Host=Localhost;Database=Production;Unicode=True;

    -- Target server version: PostgreSQL 9.1.23, compiled by Visual C++ build 1500, 64-bit

    -- Target connection string: User Id=postgres;Host=Localhost;Database=Staging;Unicode=True;

    -- Run this script against Staging to synchronize it with Production

    -- Please backup your target database before running this script

    --

    -- Inserting data into table "Staging"."Customer"

    --

    INSERT INTO "Staging"."Customer"("CustID", "CustName", "Address1", "Address2", "City", "State", "Zip") VALUES

    (1, E'Lowes ', E'102 Lowes Drive ', E' ', E'Philadelphia ', E'Pa', 15029),

    (4, E'Merriwethers ', E'322 Fisher Street ', E' ', E'Philadelphia ', E'Pa', 15655),

    (5, E'Happy Harrys ', E'888 Herry Road ', E' ', E'Philadelphia ', E'Pa', 15112),

    (7, E'Carolee ', E'102 CompUSA Drive ', E' ', E'Philadelphia ', E'Pa', 15029),

    (8, E'Brandsmart USA ', E'88 Brandsmart Drive ', E' ', E'Philadelphia ', E'Pa', 15122);

    You will notice that there are characters in the value fields that are preceded by (a space and 'E') Can anyone tell me what code I might write or show me some T-SQL that will read through the .txt file containing the (space and 'E') and replace these 2 characters with a "Space". I need to clean this code so that I can use the file containing the cleanedup DML in SQL Server. Suggestions welcome.Thank you.

    T-SQL is not a text-processing language. It will not do this cleanly for you.

    What's wrong with using Find and Replace in a text editor?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I need to read through this .txt file containing the DML code I placed in the post row by row and where ever I encounter (space and E) I want to replace the Space and E with a space. Not sure how to do that with a simple Replace.

  • I need to read through .txt files like the sample daily and remove the

    (space and E) so that the SSIS execute SQL Task will be able to execute the script contained in the .txt file. Note. I will be renaming the cleaned up .txt file to a .sql extension.

  • Just googling around found this discussion on StackOverflow showing how to do what you are asking in PowerShell.

  • I dropped a Script Task and wrote C# code to find and replace. Problem solved. Thank you for your input.

Viewing 6 posts - 1 through 5 (of 5 total)

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