Powershell command to read between two points in a Loop and write to end of file-CodePudding

There is this mySQL file (file.sql) and I want to copy the text between two points which is a starting and a ending point and write to a new .sql file. I need this in a Loop which will run for N number of tables in a Array and write to a new .sql file. At the end of each iteration, I need the process to add new line then append the text of new table to the end of the file.

--DO NOT COPY
--DO NOT COPY
-- Table structure for table `address`
--

DROP TABLE IF EXISTS `address`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `address` (
`ENCODEDKEY` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`ADDRESSTYPE` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT 
NULL,
`CITY` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `address`
--

 LOCK TABLES `address` WRITE;
/*!40000 ALTER TABLE `address` DISABLE KEYS */;
INSERT INTO `address` VALUES ( 'ENCODEDKEY','ADDRESSTYPE','CITY');
/*!40000 ALTER TABLE `address` ENABLE KEYS */;
UNLOCK TABLES;
--DO NOT COPY
--DO NOT COPY
 
-- Table structure for table `client`
--
DROP TABLE IF EXISTS `client`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `client` (
`KEY` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`TYPE` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT 
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `client`
--

 LOCK TABLES `client` WRITE;
/*!40000 ALTER TABLE `client` DISABLE KEYS */;
INSERT INTO `client` VALUES ( 'KEY','TYPE');
/*!40000 ALTER TABLE `address` ENABLE KEYS */;
UNLOCK TABLES;
--DO NOT COPY
--DO NOT COPY

For Each Iteration, the starting point should be the exact string of =

Table structure for table 'find_tablename'

The ending point should be exact string of =

UNLOCK TABLES;

And I want to copy/read all the text between those 2 points and write to a new .sql file. This process should be in a Loop and the Loop would run for N number of tables and appending to the same .sql file after adding new line. In this case it would run for 2 iterations that is for the tables address and client.

There is this script which executes the process in PowerShell for the address table. I also was thinking to use Array as seen below in the comments which allows me to easily add/remove tables I need then use a FOR LOOP for iteration. The code I have in comments is what I was trying to add to execute this Loop. So the value as position tableList[num] would subsititue address .

 # $tableList = New-Object -TypeName 'System.Collections.ArrayList';
 # $tableList.Add("address")
 # $tableList.Add("client")

 # for ($num = 1 ; $num -le $tableList.Count ; $num  )
 # {
$write = $false
$(switch -CaseSensitive -File ("file.sql") {
'-- Table structure for table `address`' { $write = $true; $_ }
'UNLOCK TABLES;' { if ($write) { $_; break; } }
default { if ($write) { $_ }}
 }) | Set-Content "new_file.sql"
 #}

CodePudding user response：

I wouldn't use switch on this, but a combination of -split, -replace and finally -join:

# read the original file as single multiline string
$sql = Get-Content -Path 'D:\Test\SqlFile.sql' -Raw

# 1) split the whole content on 'UNLOCK TABLES'
# 2) keep only the text blocks that contain '-- Table structure for table'
# 3) remove all text above '-- Table structure for table' from each text block. 
# 4) Append the final 'UNLOCK TABLES;' sql command we've removed using the first split

$result = $sql -split '(?ms)^UNLOCK TABLES;\s\r?\n' | Where-Object { $_ -match '-- Table structure for table' } | ForEach-Object {
    ($_ -replace '(?ms)^.*(-- Table structure for table .*)', '$1')   "UNLOCK TABLES;"
}

You can now save the resulting array as individual files, but if I understand the question correctly, you want to merge the array into one new file

$result -join "`r`n`r`n" | Set-Content -Path 'D:\Test\newSqlFile.sql'

The resulting file will look like this:

-- Table structure for table `address`
--

DROP TABLE IF EXISTS `address`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `address` (
`ENCODEDKEY` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`ADDRESSTYPE` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT 
NULL,
`CITY` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `address`
--

 LOCK TABLES `address` WRITE;
/*!40000 ALTER TABLE `address` DISABLE KEYS */;
INSERT INTO `address` VALUES ( 'ENCODEDKEY','ADDRESSTYPE','CITY');
/*!40000 ALTER TABLE `address` ENABLE KEYS */;
UNLOCK TABLES;

-- Table structure for table `client`
--
DROP TABLE IF EXISTS `client`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `client` (
`KEY` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`TYPE` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT 
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `client`
--

 LOCK TABLES `client` WRITE;
/*!40000 ALTER TABLE `client` DISABLE KEYS */;
INSERT INTO `client` VALUES ( 'KEY','TYPE');
/*!40000 ALTER TABLE `address` ENABLE KEYS */;
UNLOCK TABLES;

CodePudding user response：

Based on the existing solution, I created this function:

function Select-TableSql {
    param (
        [Parameter(Mandatory, Position = 0)]
        [string]$Path,

        [Parameter(Mandatory, Position = 1)]
        [string[]]$TableNames
    )
    switch -Regex -File ($Path) {
        '-- Table structure for table `(?<TableName>\w )`' {
            if ($Matches.TableName -in $TableNames) {
                $write = $true
                $_
            }
        }
        'UNLOCK TABLES;' {
            if ($write) {
                $write = $false
                $_
            }
        }
        default {
            if ($write) {
                $_
            }
        }
    }
}

You can use it like this:

Select-TableSql file.sql client, address | Set-Content new_file.sql