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...Obvious ly 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.
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 3052 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com for free now!

[Bob Barrows [MVP]]

Simon Harris wrote:

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...Obvious ly 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
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[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.
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 3052 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com for free now!

[Bob Barrows [MVP]]

Simon Harris wrote:

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

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 ...

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[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

Simon Harris wrote:

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.
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 3052 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com for free now!

[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.
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 3052 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com for free now!

[Bob Barrows [MVP]]

Simon Harris wrote:

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.

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.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.