Using PowerShell, how can a SQL file be split into several files based on its content?

Question

I'm trying to use PowerShell to automate the division of a SQL file into seperate files based on where the headings are located.

An example SQL file to be split is below:

/****************************************
Section 1
****************************************/
Select 1

/****************************************
Section 2
****************************************/
Select 2

/****************************************
Section 3
****************************************/
Select 3

I want the new files to be named as per the section headings in the file i.e. 'Section 1', 'Section 2' and 'Section 3'. The content of the first file should be as follows:

/****************************************
Section 1
****************************************/
Select 1

The string: /**************************************** is only used in the SQL file for the section headings and therefore can be used to identify the start of a section. The file name will always be the text on the line directly below.

@Mike no I haven't as I'm new to PowerShell. It looks like it could be done with the .NET StreamReader class and the 'Add-Content' cmdlet as in the answer to the following question: "http://stackoverflow.com/questions/1001776/how-can-i-split-a-text-file-using-powershell". However, this question was too different from mine for me to be able to use the answer. — Fletch, Nov 03 '15 at 15:50

score 2 · Answer 1 · answered Nov 15 '15 at 10:12

You can try like this (split is here based on empty lines between sections) :

#create an index for our output files
$fileIndex = 1

#load SQLite file contents in an array
$sqlite = Get-Content "G:\input\sqlite.txt"

#for each line of the SQLite file
$sqlite | % {


    if($_ -eq "") {

        #if the line is empty, increment output file index to create a new file
        $fileindex++

    } else {

        #if the line is not empty
        #build output path
        $outFile = "G:\output\section$fileindex.txt"

        #push line to the current output file (appending to existing contents)
        $_ | Out-File $outFile -Append

    }

}

#load generated files in an array
$tempfiles = Get-ChildItem "G:\output"

#for each file
$tempfiles | % {

    #load file contents in an array
    $data = Get-Content $_.FullName

    #rename file after second line contents
    Rename-Item $_.FullName "$($data[1]).txt"
}

+1 as this answer was very helpful to me. I've added my final answer also as I've altered and added parts of your script so that the finding of section names and breaks works correctly on my actual SQL files, which may contain multiple line returns within a section. — Fletch, Nov 16 '15 at 13:46

Fletch · Accepted Answer · 2015-11-18T11:30:41.950

The below code uses the heading names found within the comment blocks. It also splits the SQL file into several SQL files based on the location of the comment blocks.

#load SQL file contents in an array
$SQL = Get-Content "U:\Test\FileToSplit.sql"
$OutputPath = "U:\TestOutput"

#find first section name and count number of sections
$sectioncounter = 0
$checkcounter = 0
$filenames = @()

$SQL | % {
    #Add file name to array if new section was found on the previous line
    If ($checkcounter -lt $sectioncounter) 
    {
        $filenames += $_
        $checkcounter = $sectioncounter
    }
    Else
    {
        If ($_.StartsWith("/*")) 
        {
            $sectioncounter += 1
        }
    }
}

#return if too many sections were found
If ($sectioncounter > 50) { return "Too many sections found"}

$sectioncounter = 0
$endcommentcounter = 0

#for each line of the SQL file (Ref: sodawillow)
$SQL | % {

    #if new comment block is found point to next section name, unless its the start of the first section
    If ($_.StartsWith("/*") -And ($endcommentcounter -gt 0))
    {
        $sectioncounter += 1
    }

    If ($_.EndsWith("*/"))
    {
        $endcommentcounter += 1
    }

    #build output path
    $tempfilename = $filenames[$sectioncounter]
    $outFile = "$OutputPath\$tempfilename.sql"

    #push line to the current output file (appending to existing contents)
    $_ | Out-File $outFile -Append
}

Using PowerShell, how can a SQL file be split into several files based on its content?

2 Answers2