locked
Reading text File and Saving Content in Different Files RRS feed

  • Question

  • Hi.

    i have a big .sql file generated by one of the comparison tools.

    this script file has more than 11,000 line of code (TSQL) which consists of ALTERS, CREATEs etc DROPs etc etc of all objects (TABLEs, VIEWS,SPs etc.)

     

    what i want to do is; i need to read this file and get the content of each object in separate files. 

     

    the follwing are some characteristics of the large script file - they will be useful for u

    >. each object DDL is seperated by the following 3 lines

    GO

    PRINT N'ALTERING OBJECT OBJECTNAME'

    GO

     

    >. the name of the Object always starts like [dbo].[object name]

     

     

    here is a sample of the script which i am talking about; 

     

     

    ====================
    GO
    PRINT N'ALTERING TABLE TABLE01'
    GO
    ALTER TABLE [dbo].[TBL_TABLE01]
    fdidkgffgh
    dfhegh
    rj
    ryjryjrkhyotphtr
    httromhkp
    rgjrogmtphkltpkp
    erhjtohytptlhyth.t;
    sdasid
    GO
    PRINT N'ALTERING PROCEDURE PROCEDURE01'
    GO
    ALTER PROCEDURE [dbo].[PROC_PROCEDURE01]
    fkiojgirogkhorpegh[eg'
    rgergpkorpggr
    rgkopr
    4647877r
    rytrjutikjhg
    j
    fj
    gg
    GO
    PRINT N'CREATING PROCEDURE PROCEDURE02'
    GO
    CREATE PROCEDURE [dbo].[PROC_PROCEDURE02]
    dg
    sgthyt
    t
    ru
    yu
    yu
    y
    kj
    yu
    yt
    u6
    5u6
    GO
    PRINT N'ALTERING TABLE TABLE02'
    GO
    ALTER TABLE [dbo].[TBL_TABLE02]
    etwtkorwpyw
    ry
    67
    355
    gw
    rh
    te
    hr
    g
    GO
    PRINT N'ALTERING TABLE TABLE03'
    GO
    CREATE TABLE [dbo].[TBL_TABLE03]
    355
    gw
    r
    rgpkorpggr
    rgkopr
    464787

     

     

    Wednesday, October 20, 2010 9:15 AM

Answers

  • Regular expressions are good at matching patterns:

    Regex rx = new Regex(@"^GO$.*?^GO$(?<text>.*?\[dbo\]\.\[(?<object>[^]]+)\].*?)(?=^GO$|(?i-m:$))", RegexOptions.Multiline | RegexOptions.Singleline);
    string inputText = File.ReadAllText("input.sql");
    // Deal with all kinds of end of line. Regex only knows \n
    inputText = Regex.Replace(inputText, "[\r\n]+", "\n");
    foreach (Match m in rx.Matches(inputText))
      File.AppendAllText(m.Groups["object"].Value + ".sql", m.Groups["text"].Value);
    
    Wednesday, October 20, 2010 1:40 PM

All replies

  • Regular expressions are good at matching patterns:

    Regex rx = new Regex(@"^GO$.*?^GO$(?<text>.*?\[dbo\]\.\[(?<object>[^]]+)\].*?)(?=^GO$|(?i-m:$))", RegexOptions.Multiline | RegexOptions.Singleline);
    string inputText = File.ReadAllText("input.sql");
    // Deal with all kinds of end of line. Regex only knows \n
    inputText = Regex.Replace(inputText, "[\r\n]+", "\n");
    foreach (Match m in rx.Matches(inputText))
      File.AppendAllText(m.Groups["object"].Value + ".sql", m.Groups["text"].Value);
    
    Wednesday, October 20, 2010 1:40 PM
  • Hi novicedba ,

     

    Welcome to MSDN Forums!

     

    If your question has been solved, please don’t forget mark/vote the replies which helped you, this will encourage the other community members to join in discussion and help each one.

     

    If there’s anything unclear, please feel free to let us know.

     

    Have a nice weekend!

    Mike

    *****************************************************

    [All-In-One Code Framework]

    Sample world! You will get more from this world!

    Welcome to the new world!

    Sunday, October 24, 2010 12:31 PM
    Moderator