Data in excel file to SQL Server Database table

mike55

mike55

Well-known member
Joined
Mar 26, 2004
Messages
726
Location
Ireland
Hi,

I have managed to take the data stored in a SQL Server database table and have written it to and excel file. I am now trying to figure out what is the best approach for doing the reverse to this, i.e. excel to SQL Server.

Any suggestions? Am going to look at excel -> xml -> sql server.

Mike55
 
You may want to look at SQLs Data Transformation Services - you can find then in Enterprise Manager, they should be able to allow you to set this up and even automate it.
 
PlausiblyDamp said:
You may want to look at SQLs Data Transformation Services - you can find then in Enterprise Manager, they should be able to allow you to set this up and even automate it.
Click to expand...


SQLs DTS is not an option in this case as this function must be available to the users of the application. Effectively, they will provide the location of a file on their local machine, they then click the process button, I then go to their machine, upload the file to the server, transfer the data to the SQL database, and hey presto were done. One of the biggest problems will be security and authentication, i.e. prevent duplicate data being submitted.

Is it possible to call the SQLs DTS from code?? Wouldnt think so, but it may be a long shot option if it can be done.

Mike55
 
You can call a DTS package from code as Ive maintained code that does it, unfortunately I dont have access to that code anymore. :o But it can be done.
 
if the Xp_cmdshell is not disabled, create a stored proc and exec DTSrun

if not then there is a com object

Code: 
 Public Sub Call_DTSPKG(ByVal Name As String, ByVal ServerName As String, ByVal UserName As String, ByVal Password As String)
        *****************************************************************************************
        Function: Call_DTSPKG
        PURPOSE: Runs a DTS
        PARAMETERS: Name As String, ServerName As String, Username As String, Password As String
        CALLED By: Mail_Sort
        SIDE EFFECTS: NONE
        
        
        You have to add the ref Microsoft DTS Package from the com objects menu or you will
        get a error on the line Dim DTSItem As New DTS.Package
        
        *****************************************************************************************

        On Error GoTo MyErrHndl
        Dim DTSItem As New DTS.Package
        Dim Msg As String


        DTSItem.LoadFromSQLServer(ServerName, UserName, Password, 0, , , , Name)
        DTSItem.WriteCompletionStatusToNTEventLog = True
        DTSItem.FailOnError = True
        DTSItem.Execute()


        DTSItem = Nothing

        Exit Sub
MyErrHndl:
        MsgBox("DTS Failed" & vbCrLf & vbCrLf & Err.Description)
    End Sub
 
Back
Top