Remove last Line from a File Powershell

Since version 5, we can use Select skiplast . for earlier versions, we can use search or array count to skip the last row.

$q = @"

SELECT  [CustomerID],[CompanyName]
  FROM [Northwind].[dbo].[Customers]
 where CustomerID='XXXXXX'
 UNION ALL
Select '' CustomerID, ''CompanyName

"@
Invoke-SQLCmd -ServerInstance $svr.Name  -Database $db -Query $q |  Export-Csv -NoTypeInformation -delimiter "`t" -Path $myPath0
gc $myPath0 | % {$_ -replace '"', ""} | Select -SkipLast 1 | out-file $myPath -Fo -En ascii

Use -notlike:

 
 $q = @" SELECT  [CustomerID],[CompanyName] FROM [Northwind].[dbo].[Customers] where CustomerID='XXXXXX' UNION ALL Select 'dummy' CustomerID, ''CompanyName "@ Invoke-SQLCmd -ServerInstance $svr.Name  -Database $db -Query $q |  Export-Csv -NoTypeInformation -delimiter "`t" -Path $myPath0 gc $myPath0 | % {$_ -replace '"', ""} | Where-Object {$_ -notlike "*dummy*"}| out-file $myPath -Fo -En ascii

–Or use array count:

 
 $q = @" SELECT [CustomerID],[CompanyName] FROM [Northwind].[dbo].[Customers] where CustomerID='XXXXXX' UNION ALL Select 'dummy' CustomerID, ''CompanyName "@ Invoke-SQLCmd -ServerInstance $svr.Name -Database $db -Query $q | Export-Csv -NoTypeInformation -delimiter "`t" -Path $myPath0 $file=gc $myPath0 $file[0..(($file).count - 2)] | % {$_ -replace '"', ""} | out-file $myPath -Fo -En ascii

https://www.poshcodebear.com/blog/2014/2/1/removing-the-first-or-last-lines-from-a-text-file

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/06598ae8-cfc1-4c82-9718-88fd47f91f8b/powershell-converttocsv-how-do-you-print-headers-only-when-no-results-returned-from?forum=transactsql

Export Query to Tab Delimited Text File with Powershell Even Without Data

Import-Module -Name 'SQLPS' -DisableNameChecking
$dt = Get-Date -Format yyyyMMddHHmmss
$workdir='C:\temp'

$svrname = "MC\SQL2014DEV"
 $db ='Northwind'

#Change default timeout time from 600 to unlimited
$svr =  new-object ('Microsoft.SqlServer.Management.Smo.Server') $svrname
$svr.ConnectionContext.StatementTimeout = 0

$myPath0 = "$workdir\myTabfile$($dt)0.txt"
$myPath = "$workdir\myTabfile$($dt).txt"

$q = @"

SELECT  [CustomerID],[CompanyName]
  FROM [Northwind].[dbo].[Customers]
 -- where CustomerID='XXXXXX'
 UNION ALL
Select '' CustomerID, ''CompanyName

"@
Invoke-SQLCmd -ServerInstance $svr.Name  -Database $db -Query $q |  Export-Csv -NoTypeInformation -delimiter "`t" -Path $myPath0
gc $myPath0 | % {$_ -replace '"', ""} | Select -SkipLast 1 | out-file $myPath -Fo -En ascii

 

Export Query to Tab Delimited Text File with Powershell

Aggregate Monthly Data and Sort by Month

  

 create table test (id int identity(1,1) primary key, dt datetime,amount decimal(10,2))
insert into  test (dt,amount) values
(getdate(),1.05),
(dateadd(month,-3,getdate()),1.05),
(getdate(),1.05),
(dateadd(month,-3,getdate()),1.05),
(dateadd(month,-2,getdate()),1.05),
(dateadd(month,-3,getdate()),1.05),
(dateadd(month,0,getdate()),1.05),
(dateadd(month,1,getdate()),1.05),
(dateadd(month,2,getdate()),1.05),
(dateadd(month,3,getdate()),1.05),
(dateadd(month,4,getdate()),1.05),
(dateadd(month,5,getdate()),1.05),
(dateadd(month,6,getdate()),1.05),
(dateadd(month,7,getdate()),1.05),
(dateadd(month,8,getdate()),1.05),
(dateadd(month,3,getdate()),1.05),
(dateadd(month,4,getdate()),1.05),
(dateadd(month,9,getdate()),1.05) 

select DATENAME(mm,dt) mnth,
        Amount  = SUM(Amount) 
 from test
 WHERE 
 dt>=dateadd(year,datediff(year,0,getdate()),0) and
 dt<dateadd(year,datediff(year,0,getdate())+1,0)
Group by DATENAME(mm,dt) 
ORDER BY CAST(DATENAME(mm,dt) + ' 1900' AS DATETIME)

drop table   test

 

https://www.sqlservercentral.com/articles/sorting-months-by-number-sql-spackle

Padding Number Inside a String with T-SQL

  

create table test (id int,col varchar(30) )

insert into test (id,col) values(1,'5AB89C'),(2,'GH1HJ'),(3,'N99K7H45')

;with  Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

,mycte as (
select *, case when PATINDEX('%[^0-9]%',mySplit)>0 then 1 else 0 end grp1,
Sum(case when PATINDEX('%[^0-9]%',mySplit)>0 then 1 else 0 end) Over(Partition by d.col Order by n) grp2
from Nums Cross apply (Select SUBSTRING(col,n,1 ), col from test) as d(mySplit, col)

)

Select   paddedCol  from test t 
Cross Apply (
 select (
 select newCol + ''
 from ( select n
,Case when grp1=0 and row_number()Over(partition by col,grp1,grp2 Order by n ) =1 
then right('0000000000'+mySplit,10) else mySplit end newCol
from mycte m
 WHERE m.col=t.col and m.mySplit<>''
 ) d
 Order by n
 FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
 ) p (paddedCol)  

drop table test

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d6fde44d-b3d0-48e6-be17-98a8ecf462cf/find-and-replace-a-number-with-in-a-string?forum=transactsql

Change ANSI Setting in Dynamic SQL (Double Exec)

  

   DECLARE @Definition  nvarchar(max);
SET @Definition = N' CREATE TRIGGER dbo.ExecOnUpdateTest ON dbo.Test FOR UPDATE AS RETURN;';
EXEC ('SET ANSI_NULLS ON EXEC(''' + @Definition  + ''')')
 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/36f40ee9-09e7-4741-9e45-15e3b8824c1a/how-to-create-a-trigger-from-dynamic-sql-with-proper-ansi-settings?forum=transactsql