How to join two CSV files for SSRS Reporting

How to join two CSV files for SSRS Reporting - Part 2

SSRS report with Inner joining two CSV files

   My recent article has covered “How to Use CSV file as DataSource for SSRS reports”, however one of the reader posted a question on that article, how to join two CSV files, i found it interesting hence i came up with this new article.

Joining two CSV files are as similar as joining two SQL tables, but there is a small syntax difference, consider this article as second part of previous article “How to Use CSV file as DataSource for SSRS reports” for better understanding on CSV as data source.

• First we will see the source data, i have created 2 CSV files with sample data of Mutual Funds and Asset Value,  with common ID for Joining 2 data source.
• Fund_groups.csv 

ID,Fund_Family,Fund_Family_Sub_Group,Line_Item,SubScript

1,SBI Blue Chip,SBI Blue Chip G,Received from General Partner,1

2,SBI Blue Chip,SBI Blue Chip N,Withholdings taxes,2

3,SBI Blue Chip,SBI Blue Chip O,Received from Distributed General Partner,3

4,DSP BR Micro Cap,DSP BR Micro Cap G,Received from General Partner,1

5,DSP BR Micro Cap,DSP BR Micro Cap N,Withholdings taxes,2

6,Birla Sun Life,Birla Sun Life G,Received from General Partner,1

7,Birla Sun Life,Birla Sun Life N,Withholdings taxes,2 

• Fund_Assets.csv 

ID,Total_Fund_Asset

1,10000000

2,1500000

3,8000000

4,40000000

5,41000000

6,4000000

7,4500000 

• Next querying those 2 data sources, and using it in SSRS reports, for this we need to use OLEDB Driver to establish connection.  (Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\CSV Source";Extended Properties="text;HDR=YES;FMT=Delimited")

• Write an ANSI SQL to join 2 CSV files, use complete file name as Table, we can use any joins Inner, Left, Right or Full Outer join, below is a sample query.

SELECT   fg.ID, fg.Fund_Family, fg.Fund_Family_Sub_Group, fg.Line_Item, fg.SubScript, 

fa.Total_Fund_Asset  

FROM Fund_groups.csv fg

INNER JOIN Fund_Assets.csv fa ON fg.ID = fa.ID

•      Now we got report data set, use it for report development, we can use any csv file no matter how large it is, joins will work. 
  

  

This is how we can join 2 different csv files using ANSI SQL. please leave your comments, messages, feedback, thank you