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

Advertisements

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

 

https://jingyangli.wordpress.com/2015/02/13/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