User Data Import Routine

[Simon Harris]

Hi All,

I need to write an ASP page that will allow users to upload an Excel file,
the contents of which should then be imported into an SQL database.

Problem I am facing is how to do this efficiently. So far I have a page
that
will accept an upload, then display the data from the Excel file, my first
thought was to loop each row, then run an update/insert based on the data
found...Obviously this is not going to be very efficient, I'd exepect time
outs often!

Has anyone got any ideas to get round this? I'm not looking for a
solution,
just ideas!

Thanks!
Simon.

 

[Bob Barrows [MVP]]

Hi All,

I need to write an ASP page that will allow users to upload an Excel
file, the contents of which should then be imported into an SQL
database.
Problem I am facing is how to do this efficiently. So far I have a
page that
will accept an upload, then display the data from the Excel file, my
first thought was to loop each row, then run an update/insert based
on the data found...Obviously this is not going to be very efficient,
I'd exepect time outs often!

Has anyone got any ideas to get round this? I'm not looking for a
solution,
just ideas!

Third-party tool:
http://www.aspfaq.com/show.asp?id=2189

 

[Simon Harris]

Third-party tool:

http://www.aspfaq.com/show.asp?id=2189

Thanks Bob, but as mentioned I've done the upload bit...

"So far I have a page that will accept an upload, then display the data
from
the Excel file...."

Problem is how to update/add many rows in the SQL database, based on what
the user enters into the uploaded Spreadsheet....efficiently.

Simon.

 

[Simon Harris]

Third-party tool:

http://www.aspfaq.com/show.asp?id=2189

Thanks Bob, but as mentioned I've done the upload bit...

"So far I have a page that will accept an upload, then display the data
from
the Excel file...."

Problem is how to update/add many rows in the SQL database, based on what
the user enters into the uploaded Spreadsheet....efficiently.

Simon.

 

[Bob Barrows [MVP]]

Thanks Bob, but as mentioned I've done the upload bit...

Several of these tools include the capability to add the spreadsheets to the
database ...

 

[pbagar]

Assuming you have at least the Excel ODBC drivers installed on the
server, you should be able to do something like this. You may have to
add a column list depending on the format of the table/spreadsheet, and
modify the syntax to suit your needs.

sql = "INSERT " & _
" INTO <your table name> " & _
"SELECT * " & _
" FROM [" & <the name of the sheet or range to import> & "$]
" & _
" IN '' [EXCEL 5.0;DATABASE=" & <fully qualifed name of
the excel workbook> & "]"

then hand this off to the DB.

HTH,

Paul

 

[Bob Barrows [MVP]]

Thanks Bob, but as mentioned I've done the upload bit...

"So far I have a page that will accept an upload, then display the
data from
the Excel file...."

Problem is how to update/add many rows in the SQL database, based on
what the user enters into the uploaded Spreadsheet....efficiently.

Oh! I thought you wanted to enter the spreadsheet into an image column in
the database table!
Would it be possible to use a scheduled DTS package? If not, see Paul's
post.