Professional Web Applications Themes

Move data from Excel to SQL Server via ASP - ASP Database

I'm trying to import data from an Excel spreadsheet, and write it into a SQL Server database. I know how to read the data from Excel and how to write the data to SQL Server. The part I'm struggling with is moving the data from Excel to the DB. I'd like to just open a recordset from Excel, then write that same recordset to the DB-- just not sure how that's done. I've tried it a couple different ways with no luck. Could someone please give me some guidance on this? Thank you in advance for the help!...

  1. #1

    Default Move data from Excel to SQL Server via ASP

    I'm trying to import data from an Excel spreadsheet, and write it into
    a SQL Server database. I know how to read the data from Excel and how
    to write the data to SQL Server. The part I'm struggling with is
    moving the data from Excel to the DB. I'd like to just open a
    recordset from Excel, then write that same recordset to the DB-- just
    not sure how that's done. I've tried it a couple different ways with
    no luck. Could someone please give me some guidance on this? Thank
    you in advance for the help!
    midwesthills Guest

  2. #2

    Default Re: Move data from Excel to SQL Server via ASP

    If the Excel file will always be the same structure, (and after some
    preliminary checks to make sure it is etc.), set-up a DTS package and then
    execute it when desired from ASP.

    If you save the DTS package as a job, (from memory... )... you will be able
    to execute it using sp_execute_job.

    From my system where I do this:

    objConn.Execute "usp_ImportPayslipsDTS", , adExecuteNoRecords +
    adCmdStoredProc


    CREATE Procedure usp_ImportPayslipsDTS
    As
    set nocount on
    EXEC msdb.dbo.sp_start_job job_name = 'Payslips Import'
    return




    "midwesthills" <midwesthillshotmail.com> wrote in message
    news:1f28b412.0408020548.1388b938posting.google.c om...
    > I'm trying to import data from an Excel spreadsheet, and write it into
    > a SQL Server database. I know how to read the data from Excel and how
    > to write the data to SQL Server. The part I'm struggling with is
    > moving the data from Excel to the DB. I'd like to just open a
    > recordset from Excel, then write that same recordset to the DB-- just
    > not sure how that's done. I've tried it a couple different ways with
    > no luck. Could someone please give me some guidance on this? Thank
    > you in advance for the help!

    David Morgan Guest

  3. #3

    Default Re: Move data from Excel to SQL Server via ASP

    For of all, thanks for the advice. I'm not too fimiliar with DTS
    packages, but was able to create one using the GUI provided in
    Enterprise Manager.

    I'm not sure what you meant by saving the package as a job. I tried
    saving it as a package in SQL Server and as a VB file, but can't call
    it from a stored procudure using the EXEC command. What am I missing?
    Could you please elaborate? Thanks again!!!



    "David Morgan" <daviddavidmorgan.me.uk> wrote in message news:<uWtR97NeEHA.3864TK2MSFTNGP10.phx.gbl>...
    > If the Excel file will always be the same structure, (and after some
    > preliminary checks to make sure it is etc.), set-up a DTS package and then
    > execute it when desired from ASP.
    >
    > If you save the DTS package as a job, (from memory... )... you will be able
    > to execute it using sp_execute_job.
    >
    > From my system where I do this:
    >
    > objConn.Execute "usp_ImportPayslipsDTS", , adExecuteNoRecords +
    > adCmdStoredProc
    >
    >
    > CREATE Procedure usp_ImportPayslipsDTS
    > As
    > set nocount on
    > EXEC msdb.dbo.sp_start_job job_name = 'Payslips Import'
    > return
    >
    >
    >
    >
    > "midwesthills" <midwesthillshotmail.com> wrote in message
    > news:1f28b412.0408020548.1388b938posting.google.c om...
    > > I'm trying to import data from an Excel spreadsheet, and write it into
    > > a SQL Server database. I know how to read the data from Excel and how
    > > to write the data to SQL Server. The part I'm struggling with is
    > > moving the data from Excel to the DB. I'd like to just open a
    > > recordset from Excel, then write that same recordset to the DB-- just
    > > not sure how that's done. I've tried it a couple different ways with
    > > no luck. Could someone please give me some guidance on this? Thank
    > > you in advance for the help!
    midwesthills Guest

  4. #4

    Default Re: Move data from Excel to SQL Server via ASP

    midwesthills wrote:
    > For of all, thanks for the advice. I'm not too fimiliar with DTS
    > packages, but was able to create one using the GUI provided in
    > Enterprise Manager.
    >
    > I'm not sure what you meant by saving the package as a job. I tried
    > saving it as a package in SQL Server and as a VB file, but can't call
    > it from a stored procudure using the EXEC command. What am I missing?
    > Could you please elaborate? Thanks again!!!
    >
    Go to [url]www.sqldts.com[/url] where you will find various articles describing how to
    execute packages from stored procedures, asp, etc.

    Bob Barrows
    --
    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] Guest

  5. #5

    Default Re: Move data from Excel to SQL Server via ASP

    Schedule DTS Package for Later Execution - Any time, will remove it later.
    Save DTS Package - SQL Server

    Give the package a name etc.

    Go to Management \ SQL Server Agent \ Jobs

    Double click your job that appears there, switch to Schedules and delete the
    schedule.

    Then you can use the sp_start_job proc as below.



    "midwesthills" <midwesthillshotmail.com> wrote in message
    news:1f28b412.0408030357.a4e30fcposting.google.co m...
    > For of all, thanks for the advice. I'm not too fimiliar with DTS
    > packages, but was able to create one using the GUI provided in
    > Enterprise Manager.
    >
    > I'm not sure what you meant by saving the package as a job. I tried
    > saving it as a package in SQL Server and as a VB file, but can't call
    > it from a stored procudure using the EXEC command. What am I missing?
    > Could you please elaborate? Thanks again!!!
    >
    >
    >
    > "David Morgan" <daviddavidmorgan.me.uk> wrote in message
    news:<uWtR97NeEHA.3864TK2MSFTNGP10.phx.gbl>...
    > > If the Excel file will always be the same structure, (and after some
    > > preliminary checks to make sure it is etc.), set-up a DTS package and
    then
    > > execute it when desired from ASP.
    > >
    > > If you save the DTS package as a job, (from memory... )... you will be
    able
    > > to execute it using sp_execute_job.
    > >
    > > From my system where I do this:
    > >
    > > objConn.Execute "usp_ImportPayslipsDTS", , adExecuteNoRecords +
    > > adCmdStoredProc
    > >
    > >
    > > CREATE Procedure usp_ImportPayslipsDTS
    > > As
    > > set nocount on
    > > EXEC msdb.dbo.sp_start_job job_name = 'Payslips Import'
    > > return
    > >
    > >
    > >
    > >
    > > "midwesthills" <midwesthillshotmail.com> wrote in message
    > > news:1f28b412.0408020548.1388b938posting.google.c om...
    > > > I'm trying to import data from an Excel spreadsheet, and write it into
    > > > a SQL Server database. I know how to read the data from Excel and how
    > > > to write the data to SQL Server. The part I'm struggling with is
    > > > moving the data from Excel to the DB. I'd like to just open a
    > > > recordset from Excel, then write that same recordset to the DB-- just
    > > > not sure how that's done. I've tried it a couple different ways with
    > > > no luck. Could someone please give me some guidance on this? Thank
    > > > you in advance for the help!

    David Morgan Guest

  6. #6

    Default Re: Move data from Excel to SQL Server via ASP

    Is this better than using DTS.Package object to step through the
    package? Does either method inheriently use transactions, or do I
    need to code that in as well?

    Thanks! You've been a great help!!!



    "David Morgan" <daviddavidmorgan.me.uk> wrote in message news:<u$oJFSVeEHA.3944tk2msftngp13.phx.gbl>...
    > Schedule DTS Package for Later Execution - Any time, will remove it later.
    > Save DTS Package - SQL Server
    >
    > Give the package a name etc.
    >
    > Go to Management \ SQL Server Agent \ Jobs
    >
    > Double click your job that appears there, switch to Schedules and delete the
    > schedule.
    >
    > Then you can use the sp_start_job proc as below.
    >
    >
    >
    > "midwesthills" <midwesthillshotmail.com> wrote in message
    > news:1f28b412.0408030357.a4e30fcposting.google.co m...
    > > For of all, thanks for the advice. I'm not too fimiliar with DTS
    > > packages, but was able to create one using the GUI provided in
    > > Enterprise Manager.
    > >
    > > I'm not sure what you meant by saving the package as a job. I tried
    > > saving it as a package in SQL Server and as a VB file, but can't call
    > > it from a stored procudure using the EXEC command. What am I missing?
    > > Could you please elaborate? Thanks again!!!
    > >
    > >
    > >
    > > "David Morgan" <daviddavidmorgan.me.uk> wrote in message
    > news:<uWtR97NeEHA.3864TK2MSFTNGP10.phx.gbl>...
    > > > If the Excel file will always be the same structure, (and after some
    > > > preliminary checks to make sure it is etc.), set-up a DTS package and
    > then
    > > > execute it when desired from ASP.
    > > >
    > > > If you save the DTS package as a job, (from memory... )... you will be
    > able
    > > > to execute it using sp_execute_job.
    > > >
    > > > From my system where I do this:
    > > >
    > > > objConn.Execute "usp_ImportPayslipsDTS", , adExecuteNoRecords +
    > > > adCmdStoredProc
    > > >
    > > >
    > > > CREATE Procedure usp_ImportPayslipsDTS
    > > > As
    > > > set nocount on
    > > > EXEC msdb.dbo.sp_start_job job_name = 'Payslips Import'
    > > > return
    > > >
    > > >
    > > >
    > > >
    > > > "midwesthills" <midwesthillshotmail.com> wrote in message
    > > > news:1f28b412.0408020548.1388b938posting.google.c om...
    > > > > I'm trying to import data from an Excel spreadsheet, and write it into
    > > > > a SQL Server database. I know how to read the data from Excel and how
    > > > > to write the data to SQL Server. The part I'm struggling with is
    > > > > moving the data from Excel to the DB. I'd like to just open a
    > > > > recordset from Excel, then write that same recordset to the DB-- just
    > > > > not sure how that's done. I've tried it a couple different ways with
    > > > > no luck. Could someone please give me some guidance on this? Thank
    > > > > you in advance for the help!
    midwesthills Guest

  7. #7

    Default Re: Move data from Excel to SQL Server via ASP

    No, it's no better, and all it does is kick off the process. It does not
    wait for it to finish and only can only return the result of submitting the
    job.

    Go for the sqldts.com approach if you wish, I did, but decided that I didn't
    need all of that stuff and I think permissions caused me a few probs. Not
    sure if the article on the website covers permissions but I would be
    surprised if the I_USR account was allowed to execute the DTS Objects.

    I would imagine that both methods use transactions. I would not bother
    coding it in as I am sure DTS rolls back on failure although I am not
    certain.

    "midwesthills" <midwesthillshotmail.com> wrote in message
    news:1f28b412.0408031159.bec18b5posting.google.co m...
    > Is this better than using DTS.Package object to step through the
    > package? Does either method inheriently use transactions, or do I
    > need to code that in as well?
    >
    > Thanks! You've been a great help!!!
    >
    >
    >
    > "David Morgan" <daviddavidmorgan.me.uk> wrote in message
    news:<u$oJFSVeEHA.3944tk2msftngp13.phx.gbl>...
    > > Schedule DTS Package for Later Execution - Any time, will remove it
    later.
    > > Save DTS Package - SQL Server
    > >
    > > Give the package a name etc.
    > >
    > > Go to Management \ SQL Server Agent \ Jobs
    > >
    > > Double click your job that appears there, switch to Schedules and delete
    the
    > > schedule.
    > >
    > > Then you can use the sp_start_job proc as below.
    > >
    > >
    > >
    > > "midwesthills" <midwesthillshotmail.com> wrote in message
    > > news:1f28b412.0408030357.a4e30fcposting.google.co m...
    > > > For of all, thanks for the advice. I'm not too fimiliar with DTS
    > > > packages, but was able to create one using the GUI provided in
    > > > Enterprise Manager.
    > > >
    > > > I'm not sure what you meant by saving the package as a job. I tried
    > > > saving it as a package in SQL Server and as a VB file, but can't call
    > > > it from a stored procudure using the EXEC command. What am I missing?
    > > > Could you please elaborate? Thanks again!!!
    > > >
    > > >
    > > >
    > > > "David Morgan" <daviddavidmorgan.me.uk> wrote in message
    > > news:<uWtR97NeEHA.3864TK2MSFTNGP10.phx.gbl>...
    > > > > If the Excel file will always be the same structure, (and after some
    > > > > preliminary checks to make sure it is etc.), set-up a DTS package
    and
    > > then
    > > > > execute it when desired from ASP.
    > > > >
    > > > > If you save the DTS package as a job, (from memory... )... you will
    be
    > > able
    > > > > to execute it using sp_execute_job.
    > > > >
    > > > > From my system where I do this:
    > > > >
    > > > > objConn.Execute "usp_ImportPayslipsDTS", , adExecuteNoRecords +
    > > > > adCmdStoredProc
    > > > >
    > > > >
    > > > > CREATE Procedure usp_ImportPayslipsDTS
    > > > > As
    > > > > set nocount on
    > > > > EXEC msdb.dbo.sp_start_job job_name = 'Payslips Import'
    > > > > return
    > > > >
    > > > >
    > > > >
    > > > >
    > > > > "midwesthills" <midwesthillshotmail.com> wrote in message
    > > > > news:1f28b412.0408020548.1388b938posting.google.c om...
    > > > > > I'm trying to import data from an Excel spreadsheet, and write it
    into
    > > > > > a SQL Server database. I know how to read the data from Excel and
    how
    > > > > > to write the data to SQL Server. The part I'm struggling with is
    > > > > > moving the data from Excel to the DB. I'd like to just open a
    > > > > > recordset from Excel, then write that same recordset to the DB--
    just
    > > > > > not sure how that's done. I've tried it a couple different ways
    with
    > > > > > no luck. Could someone please give me some guidance on this?
    Thank
    > > > > > you in advance for the help!

    David Morgan Guest

Similar Threads

  1. Replies: 2
    Last Post: March 7th, 12:43 PM
  2. Web Services a Secure Way to Move Student Data
    By Jim Bayers in forum ASP.NET Web Services
    Replies: 1
    Last Post: January 4th, 07:59 PM
  3. Replies: 2
    Last Post: January 15th, 03:43 PM
  4. Best way to move data across multiple pages?
    By David Hearn in forum ASP.NET General
    Replies: 2
    Last Post: August 9th, 11:41 AM
  5. Extending client to access Excel files (most data still in SQL Server)
    By Christopher Stolte in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 16th, 01:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139