File is getting generated into folder, But not a csv file For the below SP, normal text file is getting generated instead of csv file.
ALTER PROCEDURE [dbo].[GenerateCDR_Test]
AS
BEGIN
DECLARE @FileName varchar(50),

 @bcpCommand varchar(2000)

SET @FileName = 'C:\TransactionsData\' 
              + ('Tansactions_'+  (CONVERT(VARCHAR, GETDATE(), 112) + '_' 
             + CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR) + '_' 
             + CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR) + '_' 
             + CAST(DATEPART(SECOND, GETDATE()) AS VARCHAR))  + '.csv');

SET @bcpCommand = 'BCP '+'&quot;SELECT * FROM [LinkVisaCard].[dbo].[CDRGeneration]&quot;' +' queryout '

SET @bcpCommand = @bcpCommand + @FileName + ' -U abc -P password -w'

EXEC master..xp_cmdshell @bcpCommand
END
On your BCP command use the -t switch to specify which field delimiter you want to use
https://docs.microsoft.com/en-us/sql/relational-databases/import-export/specify-field-and-row-terminators-sql-server?view=sql-server-ver15