User Data Import Routine

Discussion in 'ASP General' started by Simon Harris, Jan 3, 2006.

  1. Simon Harris

    Simon Harris Guest

    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.


    ----------------------------------------
    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, Jan 3, 2006
    #1
    1. Advertising

  2. 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...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
    --
    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"
     
    Bob Barrows [MVP], Jan 3, 2006
    #2
    1. Advertising

  3. Simon Harris

    Simon Harris Guest

    > 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, Jan 3, 2006
    #3
  4. Simon Harris

    Simon Harris Guest

    > 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, Jan 3, 2006
    #4
  5. 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"
     
    Bob Barrows [MVP], Jan 3, 2006
    #5
  6. Simon Harris

    Guest

    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!
     
    , Jan 3, 2006
    #6
  7. 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.
     
    Bob Barrows [MVP], Jan 6, 2006
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Paul Opal
    Replies:
    12
    Views:
    985
    Paul Opal
    Oct 11, 2004
  2. JPractitioner
    Replies:
    13
    Views:
    20,217
    Roedy Green
    Feb 24, 2006
  3. Vitali Gontsharuk
    Replies:
    2
    Views:
    592
    Vitali Gontsharuk
    Aug 25, 2005
  4. franco.amato

    Passing data to a callback routine

    franco.amato, Sep 9, 2008, in forum: C Programming
    Replies:
    0
    Views:
    309
    franco.amato
    Sep 9, 2008
  5. Klaus
    Replies:
    1
    Views:
    499
    Klaus
    Jun 25, 2012
Loading...

Share This Page