Search code examples
sql-serverwindowsbatch-filecommand-linebcp

Insert file with name containing yesterday's Date using BCP

I have a BCP command which has a hard coded file name ID_Customer_160216.csv. The file name ends in a date with formatted as yymmdd 

bcp sfnav.dbo.Customer in "C:\Users\TSL\Desktop\TSL Data\ID_Customer_151124.csv" -F2 -c -t "^" -r "\n" -S ftpserver\sqlexpress -U abc -P xyz

I want to make it dynamic: replace it with yesterday's date in the given format.

Solution

  • Boot up Powershell ISE and paste the following in. I think this is kinda what you are after....? I know its not cmd but, it is time to move on from that sort of thing anyway ;)

    $yesterdaysDateExtension = (Get-Date).AddDays(-1).ToString("yyMMdd")
$fileName = "ID_Customer_$yesterdaysDateExtension.csv"

$filePath = "C:\Users\TSL\Desktop\TSL Data\$fileName"

Write-Host "Attempting bcp with file $filePath"
bcp sfnav.dbo.Customer in $filePath -F2 -c -t "^" -r "\n" -S ftpserver\sqlexpress -U abc -P xyz