You can request LocalFs.* providers from FINBOURNE and host them locally to interact with files on a network file share (via a NAS or similar device).

Providing you have a suitable license and access control permissions, you can:

  • Read from CSV, Excel, SQLite, XML, Parquet and plain text files stored locally, perhaps in preparation for loading investment data into LUSID.
  • Write to files of the same types, perhaps having retrieved investment data from LUSID.
  • Search a folder or folders for files.

Hosting the LocalFs.* providers

  1. Make sure your computer has .NET 6+ installed.
  2. Contact us to request an archive and client certificate for each LUSID domain you wish to enable.
  3. Upon receipt, unzip the archive to a suitable folder. Note it might be preferable to locate each domain's archive on a separate computer.
  4. Copy the client certificate files (client_cert.pem and client_key.pem) over the default ones in the directory containing the DLLs, for example <unpacked-archive>\.dotnet\tools\.store\finbourne.luminesce.localfs\<version>\finbourne.luminesce.localfs\<version>\tools\net6.0\any\.
  5. Open the <unpacked-archive>\.dotnet\tools\.store\finbourne.luminesce.localfs\<version>\finbourne.luminesce.localfs\<version>\tools\net6.0\any\host-settings.json file and search for the following line:
    "ClientDomain": "fbn-ci,*",
    Add a ^ (caret) character in front of fbn-ci, for example:
    "ClientDomain": "^fbn-ci,*",
  6. Navigate to the <unpacked-archive>\.dotnet\tools folder and run the following command in a terminal window to start the service:
  7. When prompted in a browser window, authenticate to LUSID as a user with administrative privileges.
  8. Open your LUSID web app, navigate to the Data Virtualisation > Query Editor window, and click the Refresh button (highlighted in red). You should be able to search for LocalFs providers:

To stop the service, enter Ctrl-C in the terminal window. The providers should disappear from your web app catalog.

Renaming the LocalFs.* providers

You can optionally rename or remove the Mine suffix for all providers (you cannot change the LocalFs.* prefix). To do this, open the <unpacked-archive>\.dotnet\tools\.store\finbourne.luminesce.localfs\<version>\finbourne.luminesce.localfs\<version>\tools\net6.0\any\api-settings.json file and search for the following line:

"ProviderNameFormat": "{0}.Mine",

Change the text after {0} to something more intuitive or memorable, or remove it altogether, for example:

"ProviderNameFormat": "{0}",

The new names are reflected in the Luminesce catalog the next time you sign in. Note you must restart the service if you change this file while it is running.

Understanding access control permissions for the LocalFs.* providers

You, and any other LUSID user wishing to run queries against a LocalFs.* provider, must have suitable access control permissions. The general principle of creating policies for Luminesce providers, assigning them to roles and roles to users, is explained in this article. Note you should automatically have sufficient privileges if you are the LUSID domain owner with the built-in lusid-administrator role.

Getting help for the LocalFs.* providers

You can access the latest online help for LocalFs.* providers by running a query like this:

@x = use LocalFs.Csv.Mine

Using the LocalFs.* providers

The LocalFs.* providers are very similar to the Drive.* providers for interacting with files in Drive, except you must provide either an absolute location, or a location relative to that of the luminesce-local-file-access.exe file.

For reading data using the LocalFs.Csv.Mine, LocalFs.Excel.Mine, LocalFs.Sqlite.Mine, LocalFs.Xml.Mine, LocalFs.RawText.Mine and LocalFs.Parquet.Mine providers, specify a full path using the --file option, for example:

@x = use LocalFs.Csv.Mine
--file=C:\Users\JohnDoe\instruments.csv   # Either an absolute file path...
--file=..\..\instruments.csv              # ...or one relative to luminesce-local-file-access.exe
select * from @x

For writing data using the LocalFs.SaveAs.Mine provider, specify a folder path using the --path option and the names of one file per input table using the --fileNames option, for example:

@data1 = select distinct TableName from Sys.Field order by 1;
@data2 = select distinct TableName, FieldName from Sys.Field order by 1,2;
@x = use LocalFs.SaveAs.Mine with @data1, @data2
select * from @x

For finding files using the LocalFs.File.Mine provider, specify the RootPath parameter to nominate a base folder in the where clause of your query, for example:

select * from LocalFs.File.Mine where RootPath = 'C:\Users\JohnDoe' and Name like '%.csv' and IncludeSubdirectories = True

For examples of other operations, see the documentation for the similar Drive.* providers below:

To interact with...

Read from using the ... provider
(links are to docs for similar Drive.* providers)

Write to using the ... provider
(links are to docs for similar Drive.* providers)
CSV and similarly-structured filesLocalFs.Csv.MineLocalFs.SaveAs.Mine
Excel filesLocalFs.Excel.Mine
SQlite filesLocalFs.Sqlite.Mine
XML filesLocalFs.Xml.Mine
Text filesLocalFs.RawText.Mine
Apache Parquet filesLocalFs.Parquet.Mine
Find filesLocalFs.File.MineN/A