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.
[SalesForce] How to expose a CSV as a Webservice/odata for salesforce to show in lightning connect
Related Solutions
I have been using the free version of the Jitterbit Data Loader(found here) for a couple years now (can't speak to the web service option though) and absolutely love it. I just tested querying SalesForce and writing to an FTP site and it worked perfectly and can be scheduled. There are a couple of limitations with the free version, like adding another field forces a rebuild of the query but you can modify the where clause, also haven't found a way to send out a notification of failure. Here are a few screens shots from setting up the test:
A 'Simple URL' data source is simply a file located at a given URL. The functionality allows you to have the linked file show up in the Chatter Files tab and feed. It's not related to the OData functionality at all.
See the docs for a little more info.
Best Answer
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.