I am trying to find a programmatic way of updating a csv file in the repository, specifically one that is used by a Webi report as a data source. For Excel sources all is well. There are RESTful calls to add a spread sheet to the repository (POST /spreadsheets) and to update it (PUT /spreadsheets/<spreadsheet_id>). But for text\csv files there is a call to add a file to the repository (POST /infostore/folder/<folder_id>/file) but nothing to update it. Using this call on a newer version of the csv file will create a new object in the repository with a new SI_ID. I’ve thought of doing this and repointing existing Webis to the new repository object but there seems to be a gap in the RESTful SDKs too when it comes to repointing text dataproviders.
Can anybody give me hope that this is actually possible with the current SDKs, RESTful or otherwise?
I haven’t tried so don’t have the answer to your question but just wanted to check if you had considered what I would think of as a better solution but that depends on your requirements.
If you create the report in Webi Rich Client instead then you can specify the CSV file as a link to a file on a network share. I would prefer this any day.
You are not able to create or maintain this in the web version of Webi but it will be runnable from there providing that your BO service account also has access to the share (you also have to whitelist allowed shares).
I would personally use this approach any day over the uploading approach, the issue just goes away. You might want to think about security on the folder though so people can’t rename, move edit or lock the file by having it open but it’s a far better approach IMHO. Depending on the process behind the CSV file, you may also need to adjust the file name so that it’s static. I suspect you’re doing that anyway if you’re using an uploaded one on a report.
Ignore if you knew about this and have discounted the approach, just saying in case you were not aware.
Steve
EDIT: I’ve never understood why the option is only in WRC. I get that in the web version that it’s running as the service account but you should still be able to create a report against a file with a UNC path surely? Isn’t it as simple as making sure that the service account has access to the path as a prerequisite???
FURTHER EDIT: I’m not 100% sure about a CSV getting locked, it may not be an issue. The reason I said it was because it’s definitely an issue with Excel workbooks. If someone has a workbook open then the report will fail to refresh which you really don’t want so go with a CSV copy if I were you with a static name in a secured folder.
Thanks for your reply. Very valid points and I probably should of put in my post that I was aware of this option. It’s just the internal powers that be here who don’t like it! I have actually stumbled upon a solution since posting though:-
upload file to the repository manually, ensuring that it has a .csv extension, not .txt. The MIME type given to the infoobject in the repository is application/csv
the file is now listed in the Launchpad & CMC as an Excel file, but appears in the Text category when choosing a Data Source for Webi creation
I can update the file using PUT /spreadsheets/<spreadsheet_id>
any existing Webi reports will still refresh and return the new data but I get an error when trying build a new query against the csv file in a new or existing webi report
turns out that the PUT /spreadsheets/<spreadsheet_id> call updates the MIME type on the infoobject to application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
with a few lines of code from the Platform Java SDK I can set the MIME type back to application/csv and the ability to write new queries against the csv file is restored
There are /documents calls, but none that I can see to update the contents of a file already uploaded to the repository. I totally agree that I shouldn’t be using /spreadsheets and I don’t want to as this is working more by luck than design. But this is the only way I’ve found to achieve what I’m after so far.
I did this several years ago with Excel files using the .NET SDK. It can be done in either .NET or Java, but I don’t know if it can be done using the RESTful SDK.
Here’s the basic process:
Get the InfoObject for the file from the CMS database - make sure you include the Files information.
In the Files information, get the path to the file you want to replace. This will come in with something like frs://Input/ or frs://Output/<path to the file.
If the path is “Input”, get the InfoObject for the Input File Repository Server. If it’s “Output”, get the InfoObject for the Output File Repository Server. Here’s some sample C# code for getting the RootPath from there:
private string getInputServerPath()
{
string result = string.Empty;
string query = "Select * From CI_SYSTEMOBJECTS Where SI_FRIENDLY_NAME like 'Input%'";
using (InfoObjects servers = _common.BOEInfoStore.Query(query))
{
if (servers.Count >= 1)
{
Server server = (Server)servers[1];
FileServerAdmin serverAdmin = new FileServerAdmin(server.ServerAdmin);
result = serverAdmin.RootDirectory;
//strip off trailing backslash
if (result.Substring(result.Length - 1) == "\\")
result = result.Substring(0, result.Length - 1);
}
}
return result;
}
Build the path to the file you want to overwrite based on the information in Steps 2 and 3.
In whatever language your using, get a file object that is the new file that you want to copy. Make sure that the file is NOT set to “Read Only”!
Copy the new file over the top of the old file identified in Step 4.
Call .Refresh() on the InfoObject that points to the file so that it will pick up the new properties of the file.
NOTE: At the point in time that I was writing this code (over 10 years ago), SAP did not officially support updating files this way. However, there was no other way to do it and this worked.
Many thanks for your reply. Must admit I had not thought of this approach. I imagine though that it would still not be supported by SAP? I know the powers that be here will certainly not allow direct updating of the filestore…
I’ve been scanning the SAP BI 2025 docs today but no mention of the SDKs as far as I can see. A few changes around Change Source for Text files interactively so I’m holding on to the hope that an update call for Text files has been added to one of the RESTful SDks.
The hacky use of the /spreadsheets/<spreadsheet_id> call seems to still be my best bet.