Export-csv with Tab Delimiter and Remove Double Quotes

  
  
 
 
 
$file='C:\temp\theOutFile.txt'
get-service | export-csv -delimiter "`t" -path $file -notype 
(Get-Content $file) | Foreach-Object {$_ -replace '"', ''}|Out-File $file

notepad $file

 
 
 

 

Check all values in a group (Relational division)

  
  
 
 create table #t1(memid varchar(100),eventcode varchar(100))

insert into #t1 values('mem1','1stdate')
insert into #t1 values('mem1','2ndate')
insert into #t1 values('mem1','3rddate')
insert into #t1 values('mem2','1stdate')
insert into #t1 values('mem2','2ndate')
insert into #t1 values('mem2','3rddate')
insert into #t1 values('mem3','1stdate')
insert into #t1 values('mem3','2ndate')
insert into #t1 values('mem4','3rddate')



--option 1
select memid from #t1 WHERE eventcode='1stdate'
intersect
select memid from #t1 WHERE eventcode='2ndate'
intersect
select memid from #t1 WHERE eventcode='3rddate'


--option 2
select distinct memid from #t1 s 
WHERE Exists (select 1 from   #t1 t WHERE t.eventcode='1stdate' and s.memid=t.memid)
and Exists (select 1 from   #t1 t WHERE t.eventcode='2ndate' and s.memid=t.memid)
and Exists (select 1 from   #t1 t WHERE t.eventcode='3rddate' and s.memid=t.memid)
 

--Option 3
;with mycte as (
Select * 
,SUm(
Case 
when eventcode='1stdate' then POWER(2*1,1) --1stdate
when eventcode='2ndate' then POWER(2*1,2) --2ndate
when eventcode='3rddate' then POWER(2*1,3) --3rddate
--when eventcode='4thdate' then POWER(2*1,4) --4thdate
End) Over(Partition by memid)  bitSUM  
from #t1
)


Select memid,eventcode from mycte
WHERE (2 & bitSUM = 2 ) --1stdate
and  (4 & bitSUM = 4) --2ndate
AND (8 & bitSUM = 8) --3rddate
--AND (16 & bitSUM = 16) --4thdate








---Option 4
--Celko's relational division
--https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

--Celko solution 1
SELECT DISTINCT memid
  FROM #t1 AS t1 
  WHERE NOT EXISTS
       (SELECT *
          FROM  (Select distinct eventcode from #t1 ) t
         WHERE NOT EXISTS
               (SELECT *
                  FROM  #t1 AS t2
                 WHERE (t1.memid = t2.memid)
                   AND (t2.eventcode = t.eventcode)));

 --Celko solution 2
SELECT t1.memid
   FROM #t1 AS t1, (Select distinct eventcode from #t1 ) AS t2
  WHERE t1.eventcode = t2.eventcode
  GROUP BY t1.memid 
  HAVING COUNT(t1.eventcode) = (SELECT COUNT(eventcode) FROM  (Select distinct eventcode from #t1 ) AS t );

--Celko solution 3
SELECT t1.memid
  FROM #t1 AS t1
LEFT OUTER JOIN (Select distinct eventcode from #t1 ) AS t2
       ON t1.eventcode = t2.eventcode 
  GROUP BY t1.memid
HAVING COUNT(t1.eventcode) = (SELECT COUNT(eventcode) FROM (Select distinct eventcode from #t1) t)
   AND COUNT(t2.eventcode) = (SELECT COUNT(eventcode)FROM (Select distinct eventcode from #t1) t);

   
--Celko solution 4

;with mycte as (
Select distinct eventcode from #t1
)

SELECT PS1.memid 
  FROM  #t1 AS PS1    
  LEFT OUTER JOIN     mycte AS H1   ON PS1.eventcode = H1.eventcode
GROUP BY PS1.memid 
Having    COUNT(PS1.eventcode) = (SELECT COUNT(eventcode) FROM mycte)
            AND COUNT(H1.eventcode) = (SELECT COUNT(eventcode) FROM mycte) ;



--SELECT PS1.memid,
--CASE WHEN COUNT(PS1.eventcode) > 
--                              (SELECT COUNT(eventcode) FROM mycte )
--                              AND COUNT(H1.eventcode) = (SELECT COUNT(eventcode)FROM mycte )
--            THEN 'more than all'
--            WHEN COUNT(PS1.eventcode) = (SELECT COUNT(eventcode) FROM mycte)
--                 AND COUNT(H1.eventcode) 
--                     = (SELECT COUNT(eventcode) FROM mycte)
--            THEN 'exactly all '
--            WHEN MIN(H1.eventcode) IS NULL
--            THEN 'none'
--            ELSE 'some' END AS skill_level
--  FROM  #t1 AS PS1    LEFT OUTER JOIN     mycte AS H1   ON PS1.eventcode = H1.eventcode
--  GROUP BY PS1.memid;


drop table #t1

 

https://social.msdn.microsoft.com/Forums/en-US/e676bccf-9fa8-479d-ae5f-d48a39b1ee0a/need-select-if-all-my-values-are-present?forum=transactsql


Find Date Gap Between Start and End

  
  
  --SQL Server 2012 or above
  create table SampleData(ID   int,   start_dt  datetime, end_dt datetime)  
insert into SampleData values
(123,'01-Aug-14','01-Aug-14')
,(123,'01-Aug-14','01-Aug-14')
,(345,'01-Aug-14 ','01-Aug-14')  
,(456,'01-Aug-14 ','02-Aug-14')
,(456,'02-Aug-14','02-Aug-14') 
,(456,'03-Aug-14','03-Aug-14') 
,(456,'03-Aug-14','13-Aug-14') 
,(456,'15-Aug-14','23-Aug-14') 
 
 

;WITH mycte AS
(
	SELECT *
		, CASE 
			WHEN DATEDIFF(day, LAG(end_dt) OVER (PARTITION BY ID ORDER BY start_dt), start_dt) <= 1 THEN 0
			ELSE 1
		END AS [flag]
	FROM SampleData
),
mycte1 AS 
(
	SELECT *, SUM([flag]) OVER (PARTITION BY ID ORDER BY start_dt) AS grp
	FROM mycte
)
SELECT ID, MIN(start_dt) AS start_dt, MAX(end_dt) AS end_dt
FROM mycte1
GROUP BY ID, grp
Order by ID, start_dt

 
 


drop table SampleData

 

Use SFTP to send file with public/private keys along passphrase

WinSCP is a great open source tool to use sftp protocol to send files.
Besides you can use user name and password, you can use public key and private key pair to authenticate to the site.
With newer version (>=5.6), you can automate an agent job to run an SSIS package with Script Task to include passphrase used to generate the key pair.
The following code snippet is an example:

string logname = “E:\\temp\\log\\” + DateTime.Now.ToString(“yyyyMMddHHmmssfff”) + “thislog.xml”;
//// Run hidden WinSCP process
Process winscp = new Process();
winscp.StartInfo.FileName = “c:\\Program Files (x86)\\WinSCP\\winscp.com”;

winscp.StartInfo.Arguments = “/log=\”” + logname + “\””;
winscp.StartInfo.UseShellExecute = false;
winscp.StartInfo.RedirectStandardInput = true;
winscp.StartInfo.RedirectStandardOutput = true;
winscp.StartInfo.CreateNoWindow = true;
winscp.Start();

//// Feed in the scripting commands

winscp.StandardInput.WriteLine(“option batch abort”);
winscp.StandardInput.WriteLine(“option confirm off”);

winscp.StandardInput.WriteLine(“open theuser@155.111.222.101 -privatekey=E:\\keys\\mykeys\\myprivate.ppk -passphrase=MySecretPhrase “);
winscp.StandardInput.WriteLine(“put E:\\output\\exportfile.csv”);

winscp.StandardInput.Close();

string output = winscp.StandardOutput.ReadToEnd();

// Wait until WinSCP finishes
winscp.WaitForExit();
Dts.TaskResult = (int)ScriptResults.Success;


Append Timestamp to File Names–Powershell

You can rename text files in a folder with a simple replace to append timestamp to file names through powershell script.
There is also another way to control file extension to change file name as well.

From Powershell window:



 --Option with  replace .txt with appended timestamp
 Get-ChildItem c:\temp\src\*.* | rename-item -NewName {$_.name -replace ".txt",((get-date).ToString("_yyyyMMdd_HHmmss")+".txt")}
 
 --Option insert timestamp before .txt extension

ls c:\temp\src\*.* | ? { !$_.PSIsContainer   -and   $_.extension -eq '.txt' } |   ren -NewName { "$($_.BaseName) $(get-date -format "yyyymm%d%H%M%S")$($_.extension) "  }  




 

If you want to run powershell from SSMS, you can use xp_cmdshell to call powershell.exe

  
  --## replace .txt with appended timestamp
xp_cmdshell 'PowerShell.exe -noprofile -command  "Get-ChildItem c:\temp\src\*.* | rename-item -NewName {$_.name -replace ''.txt'',((get-date).ToString(''_yyyyMMdd_HHmmss'')+''.txt'')} "'


--## insert timestamp before .txt extension
xp_cmdshell 'PowerShell.exe -noprofile -command  "ls c:\temp\src\*.* | ? { !$_.PSIsContainer  -and  $_.extension -eq ''.txt'' } |   ren -NewName { \"$($_.BaseName) $(get-date -format \"yyyymm%d%H%M%S\")$($_.extension) \"  }   "'
--Or
xp_cmdshell 'PowerShell.exe -noprofile -command  "ls c:\temp\src\*.* | ? { !$_.PSIsContainer  -and  $_.extension -eq ''.txt'' } |   ren -NewName { \"$($_.BaseName) $(get-date -format ''_yyyyMMdd_HHmmss'')$($_.extension) \"  }   "'


 

Remove double quotes from Export-CSV commandlet (or files with double quotes

When use default Export-CSV commandlet, the csv file are double quoted.
The following script can remove them all.



$file="C:\temp\filefinal.csv"
(Get-Content $file) | Foreach-Object {$_ -replace '"', ''}|Out-File $file

##Open in notepad
notepad $file



Or this version:
$file="C:\temp\filefinal.csv"
(GC $file) | % {$_ -replace '"', ''}  > $file

##Open in notepad
notepad $file

 

Import String Type Datetime through SSIS.(dd-MMM-yy hh.mm.ss.fff )

When you import a string type datetime to a SQL datetime column, you have to manipulate input string to follow ISO format of YYYY-MM-DD to pass the value through a derived column.
You can find a detailed posting from this posting:
http://toddmcdermid.blogspot.com/2008/11/converting-strings-to-dates-in-derived.html

Sometimes, the expression can get really complicated.
The expression looks like:

((“20” + SUBSTRING(dt,8,2) + “-” + (UPPER(SUBSTRING(dt,4,3)) == “JAN” ? “01” : UPPER(SUBSTRING(dt,4,3)) == “FEB” ? “02” : UPPER(SUBSTRING(dt,4,3)) == “MAR” ? “03” : UPPER(SUBSTRING(dt,4,3)) == “APR” ? “04” : UPPER(SUBSTRING(dt,4,3)) == “MAY” ? “05” : UPPER(SUBSTRING(dt,4,3)) == “JUN” ? “06” : UPPER(SUBSTRING(dt,4,3)) == “JUL” ? “07” : UPPER(SUBSTRING(dt,4,3)) == “AUG” ? “08” : UPPER(SUBSTRING(dt,4,3)) == “SEP” ? “09” : UPPER(SUBSTRING(dt,4,3)) == “OCT” ? “10” : UPPER(SUBSTRING(dt,4,3)) == “NOV” ? “11” : UPPER(SUBSTRING(dt,4,3)) == “DEC” ? “12” : “00”) + “-” + SUBSTRING(dt,1,2)) + ” ” + REPLACE(SUBSTRING(dt,11,8),”.”,”:”) + “.” + SUBSTRING(dt,20,3))

There is another option to use a stage table or a stage column in your target table.
First, you import the string to varchar column and update the target column with converted the datetime string through T-SQL.
In this case dd-MMM-yy hh.mm.ss.fff (i.e. 14-DEC-2016 06.23.37.000):

  
  
 ---The update statement:

update targettable
Set dt = cast(Replace(dtstring,'.',':') as datetime) ; 

 

We design two steps within SSIS package,

Use a Sequence container 1 to use Data Flow Task to import text file to target table directly;
Use second Sequence container to Execute T-SQL Statement Task with the above Update statement.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f576e695-c398-4301-8708-6d5dc5463228/14dec16-062337000-string-is-in-csv-file-and-insert-as-datetime-in-sql-table-using-ssis?forum=sqlintegrationservices#e1aa5e09-07e9-45c5-aa26-2853831f3f45