Need to split program and save under new names
I have 30 text files. Each file has multiple SQL scripts.
These are four examples from one of the files.
One of the files is attached.
The section should be copied from 2.1 to the line before 2.2 and created
into a new file named 2-1.sql.
The next section would be copied and pasted into a new file named 2-2.sql.
The sections have the same format within each file.
[Section name] 2-1. Declaring Variables.
[Blank line]
[Script] DECLARE @AddressLine1 nvarchar(60) = 'Heiderplatz';
[Script] SELECT AddressID, AddressLine1
[Script] FROM Person.Address
[Script] WHERE AddressLine1 LIKE '%' + @AddressLine1 + '%';
[Blank line]
[Blank line]
[Next section name] 2-2. Retrieving a Value Into A Variable
2-1. Declaring Variables.
DECLARE @AddressLine1 nvarchar(60) = 'Heiderplatz';
SELECT AddressID, AddressLine1
FROM Person.Address
WHERE AddressLine1 LIKE '%' + @AddressLine1 + '%';
2-2. Retrieving a Value Into A Variable
DECLARE @AddressLine1 nvarchar(60);
DECLARE @AddressLine2 nvarchar(60);
SELECT @AddressLine1 = AddressLine1, @AddressLine2 = AddressLine2
FROM Person.Address
WHERE AddressID = 66;
SELECT @AddressLine1 AS Address1, @AddressLine2 AS Address2;
2-3. Writin an IF...THEN...ELSE Statement
DECLARE @QuerySelector int = 3;
IF @QuerySelector = 1
BEGIN
SELECT TOP 3 ProductID, Name, Color
FROM Production.Product
WHERE Color = 'Silver'
ORDER BY Name
END
ELSE
BEGIN
SELECT TOP 3 ProductID, Name, Color
FROM Production.Product
WHERE Color = 'Black'
ORDER BY Name
END;
2-4. Writing a Simple CASE Expression
SELECT DepartmentID AS DeptID, Name, GroupName,
CASE GroupName
WHEN 'Research and Development' THEN 'Room A'
WHEN 'Sales and Marketing' THEN 'Room B'
WHEN 'Manufacturing' THEN 'Room C'
ELSE 'Room D'
END AS ConfRoom
FROM HumanResources.Department
ch01.sql
These are four examples from one of the files.
One of the files is attached.
The section should be copied from 2.1 to the line before 2.2 and created
into a new file named 2-1.sql.
The next section would be copied and pasted into a new file named 2-2.sql.
The sections have the same format within each file.
[Section name] 2-1. Declaring Variables.
[Blank line]
[Script] DECLARE @AddressLine1 nvarchar(60) = 'Heiderplatz';
[Script] SELECT AddressID, AddressLine1
[Script] FROM Person.Address
[Script] WHERE AddressLine1 LIKE '%' + @AddressLine1 + '%';
[Blank line]
[Blank line]
[Next section name] 2-2. Retrieving a Value Into A Variable
2-1. Declaring Variables.
DECLARE @AddressLine1 nvarchar(60) = 'Heiderplatz';
SELECT AddressID, AddressLine1
FROM Person.Address
WHERE AddressLine1 LIKE '%' + @AddressLine1 + '%';
2-2. Retrieving a Value Into A Variable
DECLARE @AddressLine1 nvarchar(60);
DECLARE @AddressLine2 nvarchar(60);
SELECT @AddressLine1 = AddressLine1, @AddressLine2 = AddressLine2
FROM Person.Address
WHERE AddressID = 66;
SELECT @AddressLine1 AS Address1, @AddressLine2 AS Address2;
2-3. Writin an IF...THEN...ELSE Statement
DECLARE @QuerySelector int = 3;
IF @QuerySelector = 1
BEGIN
SELECT TOP 3 ProductID, Name, Color
FROM Production.Product
WHERE Color = 'Silver'
ORDER BY Name
END
ELSE
BEGIN
SELECT TOP 3 ProductID, Name, Color
FROM Production.Product
WHERE Color = 'Black'
ORDER BY Name
END;
2-4. Writing a Simple CASE Expression
SELECT DepartmentID AS DeptID, Name, GroupName,
CASE GroupName
WHEN 'Research and Development' THEN 'Room A'
WHEN 'Sales and Marketing' THEN 'Room B'
WHEN 'Manufacturing' THEN 'Room C'
ELSE 'Room D'
END AS ConfRoom
FROM HumanResources.Department
ch01.sql
BTW your description says the format is
I used the latter. You'll need to change the regex to accommodate the former.
Or it could be changed to handle both I guess.
x-y. <desc> x.y. <desc>I used the latter. You'll need to change the regex to accommodate the former.
Or it could be changed to handle both I guess.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Just to clarify:
Of the section name "2-1. Declaring Variables." only 2-1 would be used.
The section should be copied from 2.1 to the line before 2.2 and created
into a new file named 2-1.sql.
The next section would be copied and pasted into a new file named 2-2.sql.
Of the section name "2-1. Declaring Variables." only 2-1 would be used.
The section should be copied from 2.1 to the line before 2.2 and created
into a new file named 2-1.sql.
The next section would be copied and pasted into a new file named 2-2.sql.
ASKER
You are correct. I was silly enough to assume that the format was the same in ch01.sql as in the following .sql files. It changed to - in ch02.slq.
ASKER
I will give you all the points since you did fix the problem very well.
However, I would like to know how to handle the formats in the other files where they DO use a "-". See attached. Do you want me to submit a separate question?
They have two dashes, a space then the section number.
And, I do like the idea of including the first line of the section with the description in the file.
How is that commented out in perl.
Most of these seem to use the following format:
ch03.sql
ch05.sql
However, I would like to know how to handle the formats in the other files where they DO use a "-". See attached. Do you want me to submit a separate question?
They have two dashes, a space then the section number.
And, I do like the idea of including the first line of the section with the description in the file.
How is that commented out in perl.
Most of these seem to use the following format:
-- 3-1. Replacing NULL with an Alternate Value
SELECT h.SalesOrderID,
h.CreditCardApprovalCode,
CreditApprovalCode_Display = ISNULL(h.CreditCardApprovalCode,
'**NO APPROVAL**')
FROM Sales.SalesOrderHeader h ;
SELECT ISNULL(CAST(NULL AS CHAR(10)), '20 characters*******') ;
SELECT ISNULL(1, 'String Value') ;
-- 3-2. Returning the First Non-NULL Value from a List
SELECT c.CustomerID,
SalesPersonPhone = spp.PhoneNumber,
CustomerPhone = pp.PhoneNumber,
PhoneNumber = COALESCE(pp.PhoneNumber, spp.PhoneNumber, '**NO PHONE**')
FROM Sales.Customer c
LEFT OUTER JOIN Sales.Store s
ON c.StoreID = s.BusinessEntityID
LEFT OUTER JOIN Person.PersonPhone spp
ON s.SalesPersonID = spp.BusinessEntityID
LEFT OUTER JOIN Person.PersonPhone pp
ON c.CustomerID = pp.BusinessEntityID
ORDER BY CustomerID ;ch03.sql
ch05.sql
ASKER
Thanks for the quick solution.
This should handle both number formats:
(Tweak regex and $1 -> $2 extracted match.)
I included this after my first submission, so the subsequent two should have it.
That was achieved by removing the "next" statement, which begins the loop again and doesn't write the matched heading line. Removing it allows the matched line to be written to the output file.
(Tweak regex and $1 -> $2 extracted match.)
#!/usr/bin/perl
my $file = $ARGV[0];
if(!defined($file)) {
print "Specify file name on command line\n";
exit 1;
}
open FILE, "<$file";
open OUT, ">/dev/null";
while(<FILE>) {
if(/^(-- )?(\d+[-\.]\d+)\. /) {
# Found new file
# Close previous file.
close OUT;
# print "Found section $2\n";
open OUT, ">$2.sql";
}
print OUT $_;
}
close OUT;
close FILE;
And, I do like the idea of including the first line of the section with the description in the file.
How is that commented out in perl.
I included this after my first submission, so the subsequent two should have it.
That was achieved by removing the "next" statement, which begins the loop again and doesn't write the matched heading line. Removing it allows the matched line to be written to the output file.
Open in new window