BCP – Create CSV files from SQL Server

I have been playing with my football data and getting it from files and inserting it into a database, but I decided that I wanted to extract the data as CSV files and add them to a Azure data lake then link it to Power BI.
Ignore the fact that there is a column called ‘JSONExtract’ 🙂
declare @season varchar(5), @leagueid int, @dateid int, @Command varchar(1000) , @BCPCommand varchar(1000), @File varchar(150),  @SQL varchar(500), @FullFile varchar(150)
set @File = 'C:\FootballData\Results'
declare db_results cursor for

select distinct se.seasonID, leagueid, dateid
  from [database].[schema].[table] hl
  join [data].[Season] se
  on se.Season = hl.Season
  and JSONExtract = 'N'
  order by 1, 2


open db_results
fetch next from db_results into @Season, @Leagueid, @dateid 
while @@FETCH_STATUS = 0
begin
truncate table [ThePredictor].[data].[json]

insert into [database].[schema].[table](LeagueID,Dateid, season, hometeamid, awayteamid,fthg,ftag,ftr,hthg,htag,htr)
   select LeagueID,Dateid, hl.season, hometeamid, awayteamid,fthg,ftag,ftr,hthg,htag,htr
   from [database].[schema].[table] hl join [thepredictor].[data].[Season] se
   on hl.season = se.season
   where se.Seasonid = @Season
   and Leagueid = @LeagueID
   and DateID = @DateIDset @sql = 'select * from [ThePredictor].[data].[json] for json auto'
   set @FullFile = CONCAT(@File, @Season, @Leagueid, @dateid,'.csv')
   set @BCPCommand = 'BCP ' + '"' + @SQL + '" queryout ' +  @FullFile + ' -c -t, -T -S server\instance'

--Update the table so only the latest records are extracted.
  update [database].[schema].[table]
  set JSONExtract = 'Y'
  where Leagueid = @LeagueID
  and DateID = @DateID

execute xp_cmdshell @BCPCommand
fetch next from db_results into @Season, @Leagueid, @dateid
end
close db_results
deallocate db_results

After I run the SP I get a series of files which I then upload to a data lake.


 

 

 

 

 

Using Power BI I connect to the data lake and I import the files. At this stage I still need to merge the files.


 

 

 

 

After merging I get a single data set.


 

 

 

 

At this point I can create a simple chart.


 

 

 

 

 

 

Now, this was just a simple example of creating files from a database/data warehouse and using the files in Power BI.

The possibilities are endless really.