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


Dynamic SQL to Insert Datetime or Binary Data

  

  --sample table
create table test (FileName NVARCHAR(50), CreateDate DateTime, [File]  Varbinary(MAX) )

--sample 1
--dynamic insert data (datetime)
Create PROCEDURE [dbo].[SetFilestreamData_datetime]  
	@TableName NVARCHAR(100) 
	, @FileName NVARCHAR(50)
	, @CreatedDate DateTime 
	as 
Begin
DECLARE @sql nvarchar(4000)
SET @sql = 'INSERT INTO [dbo].' + quotename(@TableName) +  
            + '([FileName], [CreateDate]) VALUES (@FileName, @CreatedDate)'

EXEC sp_executesql @sql, N'@FileName NVARCHAR(50), @CreatedDate DateTime'
,@FileName=@FileName, @CreatedDate =@CreatedDate  
END

--execute sp
declare @CreatedDate DateTime = getdate()
EXEC [dbo].[SetFilestreamData_datetime] 'Test', 'Test1',@CreatedDate

--Sample 2
--dynamic insert data (binary)
Create PROCEDURE [dbo].[SetFilestreamData_varbinary]  
	@TableName NVARCHAR(100) 
	, @FileName NVARCHAR(50)
	, @FileData Varbinary(MAX)
	as 
Begin
DECLARE @sql nvarchar(4000)
SET @sql = 'INSERT INTO [dbo].' + quotename(@TableName) +  
            + '([FileName], [File]) VALUES (@FileName, @FileData)'

EXEC sp_executesql @sql, N'@FileName NVARCHAR(50), @FileData Varbinary(MAX)'
,@FileName=@FileName, @FileData =@FileData  

END

--execute sp
declare @FileData Varbinary(MAX)=Cast('aaa' as Varbinary(MAX)) 
EXEC   [SetFilestreamData_varbinary] 'Test', 'testfilename',@FileData
 

Dynamic Count ModifiedDate Rows

  
   --*********begin of sample table and data
 create table LZO_a (id int, MODIFIEDAT date)
insert into LZO_a values (1, '2019-03-01'),(2, '2019-03-02'),(3, '2019-03-03'),(4, '2019-03-04'),(5, '2019-03-05')
,(6, '2019-03-02'),(7, '2019-03-03'),(8, '2019-03-04'),(9, '2019-03-05'),(10, '2019-03-12'),(11, '2019-03-13'),(12, '2019-03-14') 

create table LZO_b (id int, MODIFIEDAT date)


insert into LZO_b values (1, '2019-03-01'),(2, '2019-03-01'),(3, '2019-03-03'),(4, '2019-03-04'),(5, '2019-03-05')
,(6, '2019-03-03'),(7, '2019-03-03'),(8, '2019-03-04'),(9, '2019-03-05'),(10, '2019-03-05'),(11, '2019-03-05'),(12, '2019-03-14') 

 --************end of sample table and data

 


DECLARE @RunDate date = '2019-03-02' --change date parameter
 
DECLARE @sql1 nvarchar(4000)=N'' 
DECLARE @ColsPivot as NVarchar(4000)=null




 Select @sql1= 'Select tname,  cname , count(*) RowCnt into tmp_BUILDMODIFIED FROM ('+  Stuff((
SELECT  ' UNION ALL '+ char(13) + char(10)  + 'Select    ''' + quotename(t.[TABLE_SCHEMA]) + '.' + quotename(t.[TABLE_NAME] ) 
+ ''' as tname, '  + quotename(c.COLUMN_NAME) +'  as cname, MODIFIEDAT   FROM   '
+ quotename(t.[TABLE_SCHEMA]) + '.' + quotename(t.[TABLE_NAME]) + ' WHERE 1=1 and MODIFIEDAT >='''  + CONVERT(NVARCHAR(10),@RunDate)+''''
 + char(13) + char(10)  
 FROM [INFORMATION_SCHEMA].[TABLES]  t join [INFORMATION_SCHEMA].[COLUMNS] c 
  on t.[TABLE_SCHEMA] =c.[TABLE_SCHEMA] and t.[TABLE_NAME]=c.[TABLE_NAME]  
  WHERE TABLE_TYPE='BASE TABLE' and [DATA_TYPE] Like '%date%' and t.[TABLE_NAME] like'LZO_%' 
  and   t.[TABLE_NAME] NOT LIKE '%DJW%' AND t.[TABLE_NAME] NOT LIKE 'LZO_TRUST%'
  and c.COLUMN_NAME='MODIFIEDAT'
FOR XML PATH(''), type ).value('.', 'varchar(max)'),1,10,'')
   + ' ) t  group by tname,cname'

--create the temp table
EXEC sp_executesql @sql1 
 
 
Select @ColsPivot =N' Select tname,   '+STUFF( (SELECT distinct ',' + '  sum(CASE WHEN cname=' + quotename(cname,'''') + ' THEN RowCnt else 0 end ) as ' + quotename(cname,'[')  + char(10)+char(13)
                             FROM tmp_BUILDMODIFIED
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
							+ ' from tmp_BUILDMODIFIED group by tname '

--print @ColsPivot
EXEC sp_executesql @ColsPivot 

--clean up
IF OBJECT_ID('dbo.tmp_BUILDMODIFIED','U') IS NOT NULL 
DROP TABLE tmp_BUILDMODIFIED


 --drop sample tables
  drop table LZO_b,LZO_a

 

 
 
 

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/aa0c4f82-cfd6-4922-9f64-4985fedf8a4c/how-do-i-pivot-date-rows-into-columns?forum=transactsql