Excel to ASP form

How to populate form with MS Excel Data?

Page 1 of 1

4 Replies - 8707 Views - Last Post: 02 September 2005 - 09:21 AM Rate Topic: -----

#1 meloman293  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 29-April 05

Excel to ASP form

Post icon  Posted 02 September 2005 - 01:57 AM

I have a system on my intranet that we use to keep track of personnel within our organization. The way I have it set up now is users manually type the names and other information in an ASP form provided on the intranet. The information is then input into a database for tracking. The problem is that if there are three-hundred or so names to enter someone has to sit there and type each and every one of them in by hand. I embedded an OWC Spreadsheet control in the form so that they could cut and paste the information in from the spreadsheet they're given, but not everyone has OWC installed so I have to go another route. (BTW: OWC sucks royally)

It seems the best way to handle this would be to have a "Load" button open Excel (which they do have installed, just not OWC), have the user paste the information in, then close Excel and have it populate either the database directly, or have it populate hidden input fields in the form, and then pass the data on submit.

I've looked around the net and I can't seem to find anything directly related to this. Any help would be greatly appreciated.

Thanks in advance,
MAB

Is This A Good Question/Topic? 0
  • +

Replies To: Excel to ASP form

#2 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: Excel to ASP form

Posted 02 September 2005 - 05:15 AM

While going from ASP to Excel is no problem, going from Excel to a DB or a form requires a little more work. The Excel file will be locally stored on the client computer...even if it is uploaded to the server, there will have to be a third party process to import the information. As long as you have a third party process, you may as well have it import directly to the database.

The easiest way to do this is to build a small application that opens and parses the files in the directory to which they'd been uploaded, import the information into a database, and move the files from that directory to another directory used for backup documentation purposes. You could accomplish the same thing in a web based interface, but the process would have to be triggered by a user defined event (button click, for example). If you built an application to do the trick, it could be scheduled via the operating system to run on a regular basis.

Just some ideas...
Was This Post Helpful? 0
  • +
  • -

#3 meloman293  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 29-April 05

Re: Excel to ASP form

Posted 02 September 2005 - 05:38 AM

Thanks for the help. What would be the best way to import the Excel data directly into the database? There is a designated format for the data that must adhere to, otherwise I’d just have an upload function for the users’ Excel docs and shoot it into the database that way. Ultimately it comes down to me not trusting the users not to screw it up.

Opening Excel at the press of a button and creating a template for the user is easy, but what’s the easiest way to dump the data into a DB from there? I could probably write a macro to merge the data into the DB, but can I do all of that just from the click of a command button?

Once again, thanks for all the help!

-MAB
Was This Post Helpful? 0
  • +
  • -

#4 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: Excel to ASP form

Posted 02 September 2005 - 08:53 AM

Myself, I'd write it one of two ways...

1. As an application, in a language such as c++, java, .NEt languages, whatever you like? Once written and compiled, that application can bescheduled through the operating system to run at regular intervals.

2. As a series of events set in motion by a triggered web event (user clicks a button called submit, for example. Once pressed, the ASP code would then
  • open the file
  • put the data in an array
  • loop through each record member, format as required, and enter it in the database
Which approach are you more comfortable with?
Was This Post Helpful? 0
  • +
  • -

#5 meloman293  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 29-April 05

Re: Excel to ASP form

Posted 02 September 2005 - 09:21 AM

Well, I’d rather write an actual application to take care of it for me. It would be more solid, and it would conserve bandwidth, which is a big issue around here, but I wouldn’t be able to just have the server run it every hour or twelve hours or whatever. There are upwards of two-hundred and fifty sets of data entered per day, and subsequent entries are often based on previous ones, so the data needs to be submitted to the database in real-time. The only way to do it then would be to call the application every time a new form was submitted, which seems like it would be pretty taxing of my server’s resources. The second approach seems like it would be a bit more conservative on my resources, and it would be done real-time.

What about creating an Excel doc on the server, formatting it as necessary to form a template, and then opening the doc in an iFrame on the page? Is that feasible or just way of target?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1