Access denied error when using File_AppendBinary

Access denied error when using File_AppendBinary

  
Dear Community,

Our Requirement
  1. We have an excel file with us. It just have columns created but no data. So just a skeleton file.
  2. Our task is to write data on this file or append data on this existing file.
What we are doing
  1. We are using RecordListToExcel in built method to first convert the record list into binary content so that it can be written to the excel file.
  2. We are then passing its content to File_AppendBinary method of FileSystem extention. The Content attribute of File_AppendBinary takes output of RecordListToExcel while Path attribute contains the path where our excel file exists.
Error

When trying to run this flow, we are getting Access to the path denied error. However, OSRuntime has full access on the folder where our excel file exists. The important thing is that using Outsystem, we were able to create new file using File_WriteBinary without any issue. The problem is occuring for File_AppendBinary only.

May I please know wht is the cause of this problem. If this is not the correct way to use File_AppendBinary then please let me know the correct way to achieve this. In case, you have any other solution to achieve what I'm looking for, please feel free to pour in your suggestions.

Regards,
Shiva
I just gave user everyone full rights on the folder containing the excel file and now, I'm not getting any error but still data is not getting appended.

Two things:
  1. Why data is not getting appended? As asked in my main thread, what is the correct way to use File_AppendFile. Where I'm wrong?
  2. It is not safe to give everyone access to the folder containing excel file. OSRuntime user has full access to it which should be sufficient. 
Please share your comments on above points.

Regards,
Shiva
Hi Shiva,

Just to make sure, when you say OSRuntime are you talking about the RunAs configuration for you espace? Please check the attached image to confirm this.



Based on your requirement, I think that you need to only to replace the existing file by a new one. You can use File_WriteBinary combined with other actions to accomplish a good file replace.

Regards,
Paulo Garrudo

Dear Paulo,

I have exactly same settings in eSpace as shown in your attachment. The RunAs fields are blank. 

As far as using File_WriteBinary is concerned, I guess it will create a new file all together. I need to append data in that file only. Consider as king of a requirement. 

Please help.


Thanks,
Shiva
Shiva,

You can set the "Run As" for your eSpace, only after that operation the access to the file will be done using the user account.

I don't know if you can just append content to a existing MS Excel File, that's why I said that you must replace it be a new file.

Regards,
Paulo Garrudo
Then what is the use of File_AppendBinary? How to use it?

Is there no way in Outsystems to append data in excel file?

Shiva,

I don't know for shure but I thought the File_AppendBinary was for appending text to an .txt file. Not for appending binary to an Excel file.

If you want to append data in an excel file I think you need to make an extention which can do that. The other way I van imagine would be using the File_ReadBinary, use the ExcelToRecordList widget, append you're lines to the RL and then use the RecordListToExcel and write it back the to filesytem again. Only it will be slow for the larger excel files.

Kind regards,
Evert
Dear Evert,

This sounds a sensible solution. I'm trying the solution you have provided. However, I have never added records on existing records. Can you please tell me how to achive that.

If possible, please create a dummy oml file and share that with me. That would be of great help.

Thanks for your hint. I remember you have helped in past as well. Great work man.

Cheers,
Shiva
Shiva,

See the attached file. Don't have much time so did it quickly, but it does what it need to do.

Have done much with files on the filesystem so likes to help people who have problems with that. There are some nice features possible with OutSystems.

Kind regards,
Evert
Dear Evert,

I am getting error while opening the oml file. May be I'm using an older version (5.1.0.22).

However, I was successfully able to implement as you suggested on my own. But actually I need to append the data to same excel file rather than creating a new one all together. here is the reason why???

Actually I need to hide few columns in the excel sheet. I mean that although these columns will be available in the worksheet but will be in hidden mode. For example, I have three columns called ID, Name and Age which will be mapped to columns A, B and C of excel sheet. Suppose I want to hide Name in the excel sheet. Thus although I have imported all three columns in the excel sheet but column Name which is in second position will be hidden to user. Thus on opening the excel sheet, user will see ID data on column A and Age on Column C while Name which is on column B will be hidden to the user. User however can unhide the column B and see its content. But we want it to be hidden by default.

Since I have not figured out any way to hide excel sheet column, That is why I was thinking about having a template excel file with blank data and hidden column. On outsystem call, I will just append the data in that file. This way, data will get added on the excel file and hidden column will remain hidden.

Creating a new excel file using old file will defeat the purpose all together. I posted a thread around this on Outsystem but got nothing concrete on this.

May I please have your comments on this. This is very urgent.

Regards,
Shiva
Shiva,

eSpace is indeed version 6.0.1.....

Nice to hear you already implemented what I meant. To bad it's not the solution you're looking for.

Reagarding you're question, isn't it better to use 2 excelfiles? One with all the attributes (including the name) and one without the name. Then you also know for shure the users will NEVER see the name (since a show isn't possible anymore). Think hide an excel colum isn't going to be possible with OS, maybe with an extension but not by 'default'.

With the writeBinary extention you can write both files to different folders (you can use the recordlistToExcel twice with more or less colums selected in the widget) where the rights on the folders can be set to different users. So the application (or managers?) can read the full excel file, but the user can only read the excel file with relevant data.

Would this be a possible solution for you?

Kind regards,
Evert

Dear Evert,

Actually the excel sheet created (having hidden column) will be used by another application to read its content and load data in a database table. This application would need that hidden column as well. So we need an excel sheet with all columns.
 
Its just that we dont want to show that column by default to the users. Thus I cannot have two different excel files for different users. All users need same excel file.

If hiding the column is not possible in outsystem and as you have suggested that creating an extention could be a solution, may I please have few details around this? I mean:
  1. How to create extentions in outsystems?
  2. What would be the logic in that extention so that I can achieve what I'm looking for? I have NO clue around this.
Since I cannot access oml file created by you because of version issues, please attach a snapshot of the design if you wish to create it for me.

I would really appreciate your help on this. I want to close this forever now.

Many thanks in advance.

Warm Regards,
Shiva
Dear Shiva,


There are already components here in the community to add data to your own excel template. Please check the following components:
This way you can have a template with hidden columns and populate it using one of the components above.

Hope this helps.
Dear Rodrigo,

Many thanks for your prompt response. As pwer your suggestion I downloaded the Excel binary reader/writer extention:

Here are the steps in my workflow:
  1. First I have used Workbook_Open action to open the excel file. Its FileName parameter contains path "C:\Inetpub\wwwroot\PricingCommunicationsUploadedFiles\2011-08-31\From.xls". OSRuntime user has full access to this path.
  2. Next is Cell_WriteRange. Parameter Worksheet contains Workbook_Open.Workbook, RowStart is 1, ColumnStart is 0 while DataSet is ToObject(MyList). Here MyList is collection of my custom entity.
  3. Next is line is Workbook_Save. Its Workbook parameter contains Workbook_Open.Workbook.
When I run my oml file I get error "Could not open the workbook.Access to the path 'C:\Inetpub\wwwroot\PricingCommunicationsUploadedFiles\2011-08-31\From.xls' is denied." 

May I please know:
  1. The cause of this error. How to correct it?
  2. Is the logic of my workflow correct as per my requirement. If not could you please correct is possible.
Please share your comments. This is getting really critical for us day by day.

Regards,
Shiva
Shiva,

A little bit late but see that Rodrigo has provide an answer.

I'm not a developer, I only use OutSystems, so i'm not very handy when it's about integration studio, think you can search about it on the OS network to find out what it is and how it works.

I didn't worked with the excel binary read/write, so can't tell you why ist's not working.

Hope Rodrigo can.

Kind regards,
Evert

Dear Evert,

No issues. Many thanks for your help till now. You have shared your valuable comments with us.

Dear Rodrigo, May I please have your comments on this issue? Please.

Regards,
Shiva
Community,

I have given user everyone full access and thus the access issue has resolved. However, I know that this is not the correct approach.

Moving further, as mentioned in my previous post, I'm passing ToObject(MyList) in the DataSet parameter of Cell_WriteRange function where MyList is my record list. This is giving me error stating  "Unable to cast object of type 'Winnovative.ExcelLib.ExcelWorkbook' to type 'Winnovative.ExcelLib.ExcelWorksheet'."

As per documenttaion, DataSet expects object type of value and that is why I'm casting MyList to object. Is it not the correct way? May I please know the correct way then?

Regards,
Shiva
Dear All,

I have achieved a lot what I wanted and I’m not facing any error now except a small issue. I’m just not getting my correct data in the excel sheet. I mean, its junk data something like System.Collections.BitArray etc. In fact the headers are getting displayed correctly. I have attached the output excel file for your reference (ABC.xls). 
 
I believe the issue is in Cell_WriteRange method and that to in the DataSet parameter. It expects an object type input while I have my record list. I tried converting my record list to object type using ToObject. It is not giving me any error but not working either.
 
Please please have a look at this and help me on this issue.
 
This is really URGENT.
Hello Shiva,

Couldn't let it go (also since I can see some possiblities for our own application) so play a lot with the extension.

The errors you've got (regarding you're early posts), I also got and when I found the answer you already posted it :). But I do got it working not, only not with the '

Cell_WriteRange' action (also got the strange key behind my record and can't read it into an application anymore).
I use the 'Cell_WriteByName'. Here you need to say in wich row/cell the attribute must be placed. I only write one line now, but I can imagine that you have a local RL and use an for each loop to write all the values to the excellist.

Ive attached a screenshot where I show what I do, hope this is enough for you.

Kind regards,
Evert

PS: the picture became a little bit messy, but you must be able to sort it out :)
Dear Evert,

Thank you so very much for your solution. That indeed worked for me. Finally !!!

But I would still be waiting for Outsystems people to reply on why Cell_WriteRange didn't work for us? I'm in touch with them.

Actually the reason I'm emphasizing more on Cell_WriteRange becuase this will let me write data to excel sheet without bothering about the number of columns I have. In your solution, I have to repeat the Cell_WriteByName for each column I have write the data in. I can definately iterate over my record list but I cant iterate over an individual record to get data of each column as Outsystem doesn't support this.

So Cell_WriteRange must work for me so that I can use this web method at multiple places. Otherwise, I'll have to create a new web method everytime because number of columns will vary everytime.

If you have an answer for this last leg as well, please share that with me.

Anyways, you have stood by me across this situation and I would like to thank you for the same.

Waiting a reply from Outsystem s team now on Cell_WriteRange.

Regards,
Shiva

Hello Shiva,

I would also be curious to the solution for the 'Cell_WriteRange' function. It's strange the 'code text' is added to the end of a line. I thinking it can have something to do with the licence or the convert of the RL to object.

I understand your need for this function, I think the performance for the upper function would be a lot better then writing it by line. I don't know if the data in each cell has the same datatype (for example it's always text)? In that case you could make different (public) action where the number of colums defines which action you should use.

For example you have an action with inputs:
- path (where the file is located)
- startnr (the integer where it must start adding)
-
sheetname
- useRL (boolean to know if a whole RL must be added or just one Rec)
- textRL
- textRec

In the action you can get the file, open it, get the right sheet and then (depending on the boolen) use a forEach to add the whole textRL or use the '
Cell_WriteByName'  to write each line of the textRec.

This action can then be used in mutiple screens, you only need as many action as you have files with different numers of colums.

But it will be better to just have the
'Cell_WriteRange' function to work correctly :).

Kind regards,
Evert

Hello Evert,

I agree, I was also thinking about the same structure as you have proposed but they all are work arounds. Cell_WriteRange must work as it will automatically take care of the data type of the column and performance would definately be better.

I have not yet received any comment from the Outsystes team on this. I'll update this post as soon as I receive any comment from them.

Regards,
Shiva
Hi,


Find attached an example eSpace using Cell_WriteRange and a new version of the extension Excel_Binary.

I'm not sure how the Cell_WriteRange worked, but changed the Utils.cs file so that the input DataSet can be a simple RecordList.

Hope this helps.


Cheers,
Hello Rodrigo,

Many thanks for the effort. But I can't open the file because of different version of integration studio. I'm using 5.1.0.22 while I guess you have a higher version. Attached is the snapshot of the error I'm getting. It says that if I want to open this extention, then I must contact Outsystems. 

Could you please convert your solution to make it compatible with the version I have? Please.

Regards,
Shiva
Hi Shiva,


Projects cannot be downgraded. Please replace the Util.cs file in the extension with the one attached.

You should consider upgrading to the 6.0 version to take advantage of all the new capabilities, which you can see here.


Kind regards,

Hello Rodrigo,

First: thanks for fixing the convert bug in the extention.

Second: There isn't a notify that the rl couldn't be converted, but now it's ok, but there is no data written to the excelsheet.

So the bug is solved but the extension doesn't work :).

Also you're extensions has an IPP property on it.

Kind regards,
Evert