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.

Advertisements

More new charts

I have been going through the data and seeing what I could come up with. I have created some new charts based on various pieces of data.

Here is a chart showing the last 6 season worth of data, highlighting average scored and conceded goals. Nothing really shows that we didn’t know.


 

 

 

 

 

 

 

This chart is based on the promoted sides and how they did in the first six games. This is more interesting as I always said don’t bet on the first 6 games as this is when the promoted teams do better, or perceived to do better as the other teams are getting used to them.

 

 

 

 

 

 

 

 

 

 

This chart goes into more details and shows how the promoted teams did in the following season. We can see things like;

  • Which teams from 3 to 6 actually got promoted.
  • Where the promoted teams finished in the following season to being promoted. I also grouped this by where they finished in the promotion season.
  • How many promoted teams stayed up.
  • Some stats on points.

 

 

 

 

 

 

 

 

These are just the first set of charts, I am starting to go through the data to see what information it tells me.

 

 

Its been an Age

Its been an age since my last post. Saying that I have been busy with work and things and the things have included re-development of the website and backend.

I have also been playing with Power BI a lot more. Here are some of my latest dashboards.

This chart is showing Home and Away goal difference by the current season.

 

This chart is showing Home and Away goal difference by various season.

This chart is showing home and away wins by teams by season, it also shows the percentage by season and it compares with the previous season to see if there has been more home wins or away wins.

 

 

It becomes more interesting when you look over more than 2 seasons.

 

I will be getting more active on here so keep an eye out for more posts.