We currently have a CSV file that is generated externally and this is is outside of salesforce, this is a master sheet and stored in a FTP and has details for all accounts together. We want to show this in salesforce and we are trying to avoid duplicating the same data again in salesforce and use Lightning connect to show the data from the FTP. How do we approach this scenario and how can a CSV be hosted in a web service and support ODATA. Is there any online tools or any directions anyone can give.
Well, if it HAS to be a CSV, you could build an OData web service yourself. Simply follow the tutorial on odata.org to figure out how to make it respond properly to the OData calls that Salesforce will make. But you'll end up building everything, including the filtering, sorting, and pagination yourself.
If you don't want to build it yourself, maybe look at Teiid, which is a data virtualization solution that supports CSV and OData and a lot of other formats. You can also search on github and you'll see a few other people who have already built OData solutions for exposing CSV files.
It would really make your life easier if you loaded that CSV into a database. Then the tooling available will do pretty much all of the work for you. Here is a dead simple example on how to get started with OData on ASP.NET. Or you could load it into Google Sheets and use the Lightning Connect Custom Adapter framework to create an Apex based solution that exposes the data. The advantage of these latter approaches is that it would make it far simpler to support the write features of Lightning Connect.