Jump to content

Will76021

Members
  • Content Count

    1
  • Joined

  • Last visited

Community Reputation

0 Neutral

About Will76021

  • Rank
    Newbie
  1. Will76021

    Export From SQL to flate file

    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.
×