Upload an Excel File

Upload an Excel File

  
Hi,

I am trying to write a web method that takes a file path, which is an excel file, converts that to XML, and passes it to a stored procedure in the database to insert the excel data to a table.

I already have the conversion to XML down, but I am stuck on the first part, namely, taking a file path, reading the contents with File_ReadBinary, and then reading that excel content file to a list with the Excel To Record List Built-In function.

Do I need to give outsystems special access to my computers file structure before it will be able to read my files?

I guess my main trouble is how to make  File_ReadBinary work with the Excel To Record List Built-In function .

Hello Adam,

If I understand correctly getting the fie (in this case the excel list) from a folder on you're computer doesn't work?

The File_ReadBinary should work but you're folder must have the ASP.NEt right so OS is granted to read that folder. You can also read this post about the fileSystem: http://www.outsystems.com/NetworkForums/ViewTopic.aspx?Topic=How-to%3a-Setting-up-Application's-access-to-file-systems. When you use the File_ReadBinary just must give the <serverpath> + <filename> to be able to get that file.

After you've read the file with the File_ReadBinay you can easily use the Excel2RL widget and as content select the File_ReadBinary.Content to convert that binary data to a RL.

Kind regards,
Evert
Thanks Evert, that helped alot.

Here are my comments about reading from an excel file in outsystems.

1.  You must give outsystems permission to access to the files you want it to read.  In order to do this, you must create a user called OSRuntime in the control panel, user accounts..., and you must grant OSRuntime Read and Read and Execute priviliges to the files you want it to read.

2.  I got hung up on the ExcelToRecordList.  Apparently it cannot read 1's and 0's as booleans, so that was throwing an error for me.  I am going to ask outsystems to make it so Excel To Record List can read booleans from a 1 or a 0.

3.  Lastly, if you are trying to upload an excel file from Silverlight, I recommend hard coding the folder path in outsystems, and then passing in the file name, since that is all silverlight will allow you to pass from an open file dialog.  The path for my File_ReadBinary action looked like this: "C:\UploadExcelFiles\" + In1
(where In1 is the file name as an input parameter).

Thanks to all.
Hi Adam,

here's some answers and also some questions too...

1. in order to have access to a folder located in your server, all web applications must have granted privileges to access to it. In a OutSystems Application that can be done in Service Center, as you did, in other apps you must do it in the IIS and in the folder. Its a security reason.

2. the ExcelToRecordList can read TRUE or FALSE as 1s or 0s, and that should resolve your problem

now the question...can you explain more about the reason for  you to use a stored procedure in the database to insert the excel data to a table?

cheers, 
RNA

Hello Adam,

Nice to hear the post helped you.

For you're other questions:

1) already answerd by Ricardo :),

2) What you also can do is set the right value by an if statment, so for example: "if(examplevariable=1,True,False)"

3) I would advise you to set the path to your folder as a site property (then if needed you can change it on runtime). Regarding the filename, I don't know you're construction but I sometimes didn't know how the file was named. In that case I first did a directory List files (FileSystem action) of the folder so I had the fileName of the file in that folder not knowing how it's called. By the directory list file you can use the "folderPath + DirectoryListFile.Name" to get the file from that folder. (just a suggestion).

Kind regards,
Evert
We actually have the stored procedure part solved.  You see, once we get the excel data as a Record List, we can just parse that to XML and hand that to a database stored procedure that takes care of the updating and inserting.

thanks all.