Jump to content

Will76021

Members
  • Posts

    1
  • Joined

  • Last visited

Everything posted by Will76021

  1. I need to export data from SQL to a Flat File with no Headers. Invoke-Sqlcmd $dbname = "Sendsuite Live" $AttachmentPath = "c:\\exportdata\BeckerAccountDataNew.txt" $QueryFmt= @" SELECT dbo.CharPad (s.PurchaseOrderNumber,10,'LEFT',' ') ,dbo.CharPad (s.SalesOrderNumber,10,'RIGHT','0') ,dbo.CharPad (U.UserName ,6,'LEFT',' ') ,dbo.CharPad (P.Reference2,6,'LEFT',' ') ,dbo.CharPad (P.Reference2,6,'LEFT',' ') ,dbo.CharPad (U.UserName,6,'LEFT',' ') ,dbo.CharPad ('',1,'LEFT',' ') ,dbo.CharPad ('MESS',6,'LEFT',' ') ,dbo.CharPad (U.DepartmentName,4,'LEFT',' ') ,dbo.CharPad (P.Reference1,4,'LEFT',' ') ,dbo.CharPad ('||',8,'LEFT',' ') ,dbo.CharPad ('USD',3,'LEFT',' ') ,dbo.CharPad (REPLACE(CONVERT(VARCHAR(10), s.ShipmentDate, 1), '/', ''),6,'LEFT',' ') ,dbo.CharPad (REPLACE(CONVERT(VARCHAR(10), s.ShipmentDate, 1), '/', ''),6,'LEFT',' ') ,dbo.CharPad ('||',6,'LEFT',' ') ,dbo.CharPad (case when s.NoOfPieces IS NULL then '1' else s.NoOfPieces end,9,'LEFT',' ') ,dbo.CharPad (case when s.NoOfPieces IS NULL then '1' else s.NoOfPieces end,9,'LEFT',' ') ,dbo.CharPad (s.TotalCost,11,'LEFT',' ') ,dbo.CharPad ('',1,'LEFT',' ') ,dbo.CharPad ('||',6,'LEFT',' ') ,dbo.CharPad ('||',4,'LEFT',' ') ,dbo.CharPad ('||',20,'LEFT',' ') ,dbo.CharPad (P.Reference4,20,'LEFT',' ') ,dbo.CharPad ('||',2,'LEFT',' ') ,dbo.CharPad ('||',2,'LEFT',' ') ,dbo.CharPad ('||',2,'LEFT',' ') FROM Shipments as s left JOIN [Record Identifiers] R on R.ShipmentID = s.ID left join Packages P on P.ShipmentID = s.ID left join Users U on U.id = s.ShipForUserID where P.HistoryStateID <> 4 AND s.ShipmentDate = CONVERT(Date, GETDATE()-3) "@ Invoke-Sqlcmd -ServerInstance Beware99 -Database $dbname -Query $QueryFmt |Export-Csv $AttachmentPath -NoTypeInformation This will export it but in a csv and with Headers any help would be apricated.
×
×
  • Create New...