Project: online research questionnaire

Best method for retrieving data

[richardb]

Data stored online in a database (MsAccess or Sql).
Site running .asp

Various aspects of the data needs to be downloaded, processed and analysed into Excel via a Mac

Options 1

Ftp download of database
Export the data to Excel

Option 2

Screen scraping the data – but that will lead to errors in the data being compiled

Option 3

Import a page directly into the Excel spreadsheet > get external data > New web query

Option 4

Er I’m stuck! What do you good folk do?

TIA

Rich

[hakre]

hi richardb,

have you thought about the option to export the data into a CSV file (comma delimited file), which excel is able to import?

won't this be quite right for your needs, would be like opening an excel file... :)

-hakre

btw, if you're using ASP and the server is windows, why not using the activex document of an excel spreadsheet and creating the complete excell file on the fly by your script - then download it?

[richardb]

Hi hakre

Thanks for the reply

have you thought about the option to export the data into a CSV file (comma delimited file), which excel is able to import?

won't this be quite right for your needs, would be like opening an excel file... :)

Means fpting the whole database, when only some data subsets are needed :(

btw, if you're using ASP and the server is windows, why not using the activex document of an excel spreadsheet and creating the complete excel file on the fly by your script - then download it?

That looks interesting, although I have no idea of how to achieve it, will have a look tomorrow and get back with… …probably more questions ;)

Ta Rich

[bmcgee]

Hakre, doesn't your method require Office to also be installed on the server? I would imagine you couldn't create an Excel object without it.

If MS Office will be on the server, that is a very good option. For those who have a clean server without apps running on it though...that won't work.

[markus007]

Change the response.contenttype to excel and a embeded excel spreadsheet will open up in the browser.. THis is awesome for reports and i use it a lot. If you do it as part of a form post the client will be able to download a excel spreadsheet.

[aspdaddy]

Markus007

Can you show an example of this code, changing the content type & writing csv data to the response?

I usually do it client-side, create Excel as an ActiveX in vScript. Problem is it only works with low security settings.

[richardb]

Hi Folks

Thanks for the info so far...

Markus007

Change the response.contenttype to excel and a embeded excel spreadsheet will open up in the browser.

.

Looks interesting, however, I'm unsure as to how you could use this to interrogate data from the database? It only seems to open a blank spreadsheet.

To try to be more specfic, 1 scenario would be

1 * employer with 35 employees (+ 2 * peers + 2 * reportees + 1 * boss) (210 spreadsheets) all with related/interlinked data which need to be aggregated…

Rich

[richardb]

::bump::

Any takers?

Rich

[richardb]

Found an example with background info re: Markus007 suggestion using response.contenttype

http*//www.aspalliance.com/stevesmith/articles/examples/excelsample.asp

Rich

[markus007]

Create a asp page that dumbs records from a databaes to a HTML table.

Then add this to the top of the ASP file

Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=something.xls"
Response.Flush()