Uploading data to an online database
-
Hi, I'm currently creating a site that I want to include an online data storage system that can automatically add bulk data from a spreadsheet. I want the user to upload an excel document containing a table with the same fields as my online (MSSQL server) database and have the site add each row of the spreadsheet added to the database automatically. I have other needs too like the obvious need to avoid repeat values without loss of data but for now I just need to know where to look to get the upload of data from an excel spreadsheet part working. Does anybody know what sort of things I should be researching to get this sort of functionality as I'm not sure where to start? I hope all that made sense. Thanks Andy
-
Hi, I'm currently creating a site that I want to include an online data storage system that can automatically add bulk data from a spreadsheet. I want the user to upload an excel document containing a table with the same fields as my online (MSSQL server) database and have the site add each row of the spreadsheet added to the database automatically. I have other needs too like the obvious need to avoid repeat values without loss of data but for now I just need to know where to look to get the upload of data from an excel spreadsheet part working. Does anybody know what sort of things I should be researching to get this sort of functionality as I'm not sure where to start? I hope all that made sense. Thanks Andy
My suggestion: 1) Create a method where you will prompt the user for the Excel document to upload. 2) Upload the .XLS to a "working" directory giving it a unique name (include a timestamp) 3) Have a background process open the .XLS document, process the records, create an error log and when finished move the document to an "archive" folder. 4) You can create this background process as a scheduled task that gets run on your IIS server, periodically checking the "working" directory for .XLS files to process. Sound good ? :thumbsup:
-
Hi, I'm currently creating a site that I want to include an online data storage system that can automatically add bulk data from a spreadsheet. I want the user to upload an excel document containing a table with the same fields as my online (MSSQL server) database and have the site add each row of the spreadsheet added to the database automatically. I have other needs too like the obvious need to avoid repeat values without loss of data but for now I just need to know where to look to get the upload of data from an excel spreadsheet part working. Does anybody know what sort of things I should be researching to get this sort of functionality as I'm not sure where to start? I hope all that made sense. Thanks Andy
When you say spread sheet if you mean csv file it will be easier then if it is an xls file. If csv give the user the means to upload the file to the server. I've used the asp .net control for uploading which seems to work out well. Once uploaded use the filestream class to open the csv file, parse through the file for the data items, and insert them into your database. If the files are xls you will have to do Excel automation to pull out the data from the spread sheet. You can to this client side or server side. If client side the user will need to have Excel installed. You also may have to worry about what version they have. If you do it on the server your version is not a problem but using server resoucres could be a problem if multiple people will be uploading at the same time. For server side you will have to setup a service application to process the xls files.