-1

I've read Allow users upload CSV files, and embed the data for a simple API , Importing large data sets into a database over the web the closest concept is Is there a way to efficiently allow a user to upload multiple content at once for e-commerce type sites? and the most thorough A manual and automated CSV file upload solution for a website

also many of the other solutions to upload data.
What I'm thinking is to have my end users upload an Excel file to a .NET ASHX file handler that opens the file using ClosedXML and does a bulk import into a MS SQL server after doing some validation checking. Once the data is saved it is passed back to the website in a datagrid. The reason for the Excel file is that all of the users use them anyway and it a place that the data is stored locally.

Would it be better to convert the Excel data client side and send it as JSON to a WEB API as a POST? Is there a size limitation?

Is there a better way to do this? Should I consider using an FTP client in the user interface?

Thank you for your direction. I hope that this is the type of question that can be asked here.

Improve this question
3
  • Can you ensure your users use a predefined Excel form (provided by you) to provide the data? Or do you want to allow them to upload some arbitrary document? And 2.: do your end users belong / work for the same organization like the ones who receive the documents, or can your end user be Joe Malware from another jurisdiction than the receivers?
    – Doc Brown
    Commented Mar 18, 2021 at 21:37
  • All end users will be given an Excel template that will go through column check to ensure that the data can be processed. I did not consider the possibility of uploading some form of Malware. With that in mind I may try to read the file client side and rebuild according to my spec's and make sure no macro or other hidden items.
    – user1314159
    Commented Mar 19, 2021 at 12:14
  • When you want to prevent malicious attacks, the last thing you do is trusting any client side checks, you need server side checks! But when this is an inhouse application and your users may belong to the same company, or at least share the same jurisdiction as the receiver (which you did not tell us), using a specified Excel document can be ok. An xlsx file is just a compressed xml file, under the hood - a complex one, but still one which can be validated.
    – Doc Brown
    Commented Mar 19, 2021 at 13:52

1 Answer 1

Reset to default
0

I would advise to do some preprocessing and validation client-side and send a well defined document to your server. The document that is ultimately sent could be JSON or XML or even delimited plain text. This will save you the headache of dealing with garbage input from clients who forgot something or do not understand how to enter data the way you want them to. Guide them to do the right thing from your Excel macro/code-behind. So your users could still use their favorite editor (Excel) but you would provide the Excel application that has a button they can click after which their sheet is validated and only uploaded (in your favorite format) when found good. You could also split the uploads in multiple manageable documents if size is a concern.

This allows you to provide them with a dedicated data entry program later that spits out the same type of document. Your server would not have to know. Note that XML or JSON is more flexible than lines of text, providing structure and hierarchy. Making modifications and extensions later will be easier.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.