Uploading Multiple Records From .xls To Sql Server As Single Batch (ado/jet/odbc)

  • Here's what I'm trying to do. I have an Excel 2003 worksheet named "xTally" that I would like to upload to a specific table named TEMP_DWORTally in an SQL Server 2005 database. This table contains a trigger (confirmed to be functioning correctly) which serves as both a security and a data transformation layer performing lookups, etc. on new records and then transfering them to a permanent table. (The user name and password used to accomplish this have been given access to this table only.)


    Here is the code I'm using in Excel VBA to perform the upload:



    You'll notice that my connection string connects to the Excel file itself, and the SQL command links to the proper database table using ODBC. The reason I'm not connecting first to the server directly and then linking to the file is because I'm not aware of any method for having the server pull data out of Excel while the workbook is open. It's also worth noting that I'll eventually have a number of similar Excel files used by various people, so it seems much easier to send data to the database than it is to have the database pull data in from many source files on different computers.


    The upload process itself works just fine, except that it seems to be loading the data to the database one row at a time -- which is a problem, since the trigger I have on the target table ends up firing for each row rather than at the end of the upload process. (I should note that it doesn't fire for each row when I run similar procedures directly within SQL Server, so it would seem to be an issue with either JET or ODBC.) I need to have all of the records uploaded at once in a single batch so that the trigger will fire only once per upload.


    After banging my head on my desk for two days, I'm at a loss to explain why this problem is arising. Any help on the above would be much, much appreciated.


    Thanks,



    --Todeswalzer

  • Re: Uploading Multiple Records From .xls To Sql Server As Single Batch (ado/jet/odbc)


    I don't understand why it is firing record by record but could you create a copy of the table (a "staging" table without the trigger) then run it in and insert from the staging table ?


    I would think the best way to handle this long term is by using SSIS (SQL Server Intergration Services). It takes some learning but with SSIS you can control the process fully including how to transform each field (eg a Unicode string in Excel to a particular type of string and code page in SQL), you can use the sql agent to kick off the SSIS packages at anytime and you can control what happens if it errors (if it can not find one of the Excel workbooks for example). If you have to push the data (ie you can not force a save on the Excels) maybe you could push into staging tables and use SSIS from there ?

  • Re: Uploading Multiple Records From .xls To Sql Server As Single Batch (ado/jet/odbc)


    carlmack,


    Thanks for your response and suggestions. I did consider building an SSIS package when deciding on the approach to take regarding the transfer of data from Excel into SQL Server, but opted against it for three reasons:


    [INDENT]1. It doesn't solve the pulling-data-into-SQL-Server problem (as mentioned above);


    2. It seems like overkill when a fairly straightforward trigger could do the necessary work; and


    3. It would require Excel to initiate the package once data had been transfered to the staging table. (Running it on a schedule isn't feasible since updates will not be made on a schedule -- it all depends on when users choose to enter their information. The data must be moved out of the staging table immediately upon being entered because duplicate records are guaranteed if multiple updates occur between "transformation/distribution" runs. I would really prefer to have the Server resolve data conflicts rather than Excel.)[/INDENT]


    My primary aim here is to have Excel's handling of the data minimized -- ideally, I want Excel to simply send the data that it has, and have the Server work out the details. This way maintenance/upgrades would be streamlined, and the logical demands placed on Excel would be simplified (no need to consider mutliple "what-if" scenarios).


    Ultimately, I may have to rewrite my trigger as a stored procedure that would be invoked by Excel immediately upon the data transfer completing. However, I want to avoid this if possible because it fragments the process, and may also also lead to timing issues since I want to have each individual upload considered as a self-contained "package". (Relying on Excel to initiate the stored procedure could sometimes lead to the procedure running late, and so including data uploaded by other people, whereas a trigger would guarantee that each upload is handled sequentially and individually.)


    Thanks again for the help. I hope we can get to the bottom of this.



    --Todeswalzer

  • Re: Uploading Multiple Records From .xls To Sql Server As Single Batch (ado/jet/odbc)


    Can you create the staging table and a stored procedure to move the data from the staging table to the live table? That way, Excel would run the data to the staging table and upon completion of the upload the same ADO connection could be used to call the stored procedure to move the data from the staging table to the live table. That way, your trigger would remain intact and would only fire once for the stored procedure.

  • Re: Uploading Multiple Records From .xls To Sql Server As Single Batch (ado/jet/odbc)


    Hi Todeswalzer


    Ok I understand where you are coming from and why SSIS is out.


    I think my first point - which Mavyak's has put more clearly - could still be a valid work around. You can 1. load a staging table then immediately 2. insert from the staging table to the live table and 3. delete the staging table contents all with the same connection.


    On the other hand it would be interesting to work out what is going on. Can you script out the trigger (or triggers) and post it up so we can then try to simulate it.


    Cheers
    Carl

  • Re: Uploading Multiple Records From .xls To Sql Server As Single Batch (ado/jet/odbc)


    Try adding data to the Table from an Array...


    When you use INSERT INTO it's extremely slow and the fastest method is to use an array when transferring data from excel...


    Try playing around with this code, it uploads records extremely fast with a Trigger on the table. Read up on locks you may want to use adLockPessimistic instead, but i favour adLockOptimistic when uploading data...