Upload data from an excel file

Upload data from an excel file

I just started learning Service Studio this week and have created a project.  Now I need solutions for some tasks.

When I just created a table, I was given a choice to bootstrap data from an excel file.  How can I add a button or link on a page to the same funtion to allow users to load data from an excel file?

And how can I add a filter (e.g. in C#) to inspect the uploaded file to make sure the data in the file are okay?  For example, I want to make sure the texts in a colomn are all in the right format. Or I may need to preprocess the data in a column, such as converting date from dd/mm/yy to yyyy-mm-dd. 


P.S. I posted it to a wrong gorup and posted it again here.
I've been doing this a lot for a project I'm working on.  If you look at the bootstrap logic created by the platform you'll see it's pretty simple.  It goes through each record and sets the variables in the database record and then calls the Create function for that entity.  It's fairly easy to modify this, instead of writing C# code, to do anything you want.  I've already used this to set foreign keys between multiple imported Excel sheets and set other values.  I won't have users doing this but for testing purposes I created a page with a button to delete all the records using an advanced query and another button to call the bootstrap procedure.  It's all pretty straight forward once you look at the generated logic.
Thanks, Curt.

I've managed to achieve the uploading, pretty much in the way you discribed.  Now I have another task, guess similar to what you did involving foreign keys. Suppose I have two tables, say t1 and t2, with columns t1.id, t1.name and t2.id, t2.def, t2.t1_id (foreign key to t1.id).  Now I have an excel sheet with columns name and def and I want to load the data in the excel file into t1 and t2 (name to t1 and def to t2), in such a way that for each row, I'll need to check if name is already in t1, if yes, I'll get its id, and if not, I'll insert name into t1 first, then get its id. Then the id for name will be inserted into t2 along with def.  Can you describe to me how I can do that?  I may also need to check if name is in accepted format, such as letters followed by a hiphen then some digits (e.g. ORI-35) and may need to reformat name, for example, to trim off something at the end (e.g. changing ORI-35.1 to ORI-35).

Any info or hints are appreciated.

Are there documents available where I can get instructions for what I just described?

I don't have time right now to put together an example but I can give you some hints so you might be able to make some progress.

1) Import T1 first.  Trying to go back and forth between two files just makes everything more complicated.
2) There are many built in string functions that will allow you to parse, examine and fix (or reject) incoming data fields.  You usually can use the expression editor within an Assign block to do that.
3) To get the foreign key use the linking field in T2 as the a parameter for a simple query to lookup the T1 record.  Then set the foreign key field in T2 to the ID of T1.

There are no documents that describe this to my knowledge.
Thanks again, Curt.

Seems the system's not well documented.
I wouldn't agree with that statement.  If you were to look for how to do this in the standard documentation for other languages (Java, C#, etc.) you wouldn't find it either.  Where Outsystems struggles is in examples of how to do things like this because the development community is small, but as you'll find out, very eager to help.

The great thing is that once you get the hang of it you can experiment and try things so much faster.  Stick with Outsystems and you'll love it!!
I'm sure I can develop my application with Outsystems. I just feel frustrated not being able to get docuements for basic features I need to use.

The forums are certainly helpful, so are the videos.  But I'd like to have some written documents that describe the basic features from which people can build applications without extra help.  What I described for my application is a very basic functionality.  Processing users' data uploaded from a web form is something most web applications will do.  So I expect documents describing how to do so to be available for a system for developing web applications.  Maybe the documents are available somewhere and I simply haven't found them yet.
Hi Zhang,

First of all welcome to the OutSystems community!
If you are starting a great place for you to see things happening is the online training. There's a video in module 13 that discusses the bootstrap action, now we can't foresee every single example you'll bump into, but this video discusses how to change the bootstrap action to create realationships between entities by looking into the data in the excel file.
The bootstrap action is a good starting place for what you want but it is not a solution for your problem.

You'll have to think a little bit on the problem at hand and take some decisions:
  • How to validate data? Create a user action as seen here that receives the input from the excel file and validates it. It should probably return any error it finds...
  • ?If errors occur do you import anything at all? If not then you should probably rollback the transaction...
  • How will you show the errors to the user? You should probably append the errors from the previous action to a list and show it on the screen (the logic of this screen is pretty much the same as this one but instead of binding the TableRecords to the output of a query on the preparation you will bind it to a local variable that should be a record list of a structure you define to hold the error messages).
  • How big will the excel be? In other words will the excel file process quickly enough not to stall the web request or do we need to process it asynchronously?
Finally here's an example you can probably get some good hints from.

If you need help don't get frustrated, keep reaching out to the community, I'm sure they'll be willing to help you move forward.


Thanks,  Andre.

The example and the suggested videos are really helpful.  They certainly show the techniques I'm looking for though I still need to spend time on them to be able to apply them to my cases.  Actually I'd watched the online videos, but I hadn't fully understood them yet.

The data file is not very big (at most a couple of hundreds of rows at a time with about two dozens of columns per row), so I think it should be pretty fast to process the records in a single thread. The transaction has to be rolled back if any record's not validated.

I've developed many web applications on unix systems where I directly write codes (in various languages, such as JavaScript, SQL, PHP, Perl, Java, Groovy (with GRAILS) , and even shell scripts)  to process inputs and send back responses. This is the first time I'm developing applications on Windows server and the first time to use a developing system like Service Studio. The online training videos clearly show it's easy to develop applications with Outsystems platform, but I'm simply not used to such developing enviroment and feel like helpless when I can't see and edit the actual codes.  Anyway I can tell Outsystems platform's quite powerful and I hope it'll get easier for my next project.

I totally understand that the shift can be hard... I really hope that you stick for a while 'cause I have no doubt that you'll be having fun :)

Be sure to come back so that we can help you in your tasks.

Regarding the online training I am very interested in earing you thoughs! Can you drop me a private message with the hurdles you faced and what was your process going thru the videos?

Thank you!
I'll surely finish the project with Outsystems. This project is a relatively small and simple project and it'd take me a week or so to deliver if I were to develop it on my unix server.  I think it woudn't take longer, if not shorter, to finish this particular project with Service Studio were I skillful with the system.  But now it's been more than a week since I started with Service Studio and still need to learn a lot.  Hopefully after I'm done with the project,  I'll feel comfortable with the system and use the system for other projects.  
I've solved this problem.  Thank all for your help.