Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: Excel to MS Sql server

    Hi:

    win2k, xp
    excel 2k
    MS SQL server 7.0

    Here is the deal:

    I have users entering data on an excel spreadsheet. I want that data to go to Ms SQl Server (local) database and into a table called logcall_table. How do I do it? More prcisely, how do i do it in detail? I can not find answers on the net and I am really lost. you can also refer to "SQL from Excel to MS SQL Server 7.0" my earlier post on this issue for more detail on how I have been trying to do it.

    Thank you for your help.

    Alex

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    11
    Hi,
    Open Enterprise Manager.
    Tools -> Data Transformation Services -> Import Data ...
    Next -> Data Source = Microsoft Excel XX
    Enter the file name and continue with the Wizard !!!
    Hope that it helps,
    Handymac

  3. #3
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Excel to MS Sql server

    HI,

    thanks for answering, but i want to sql the info to the server and update the db that way. The problem i faced with is the connection to the server from excel. How do i write a procedure to connect to the server and have all my sql in there?

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Do you want to update a sql table as you are entering in excel or do you want to import the excel file into a sql table ?
    If you do not have SQL Server Books Online (BOL) installed - please do so. The majority of questions asked in the SQL Server forum could be eliminated if people had access to bol. BOL

  5. #5
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188
    if i understand you right, you want users to enter data to an excel spreadsheet, and they will "push a button in excel sheet" to update the entries into sql db when they finish data entry.

    if its what you want, google for excel macro manual, VB6 and ODBC database connectivity.

    my idea is to write some macro that connect to SQL as users finish data entry and push a botton say "Update to database"

    hope this helps
    qha_vn

  6. #6
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Excel to MS SQL Server

    qha_vn,

    That is exactly what I want to do.
    Here is what I have done so far for access but that does not work for MS SQL Server and I can not figure out the way to code the connection. This is the way I connected to MS Access on my local machine. Now I have SQL Server and I want the user to push a button and sql the data in to the LOGCALL_TABLE in SQL Server. Additionally, should the application requier MS SQL Server Object Libraries to function?

    Code:
        Dim conn As ADODB.Connection
        Dim cmd As ADODB.Command
        Dim AppPath, app As String
       
        'AppPath = "c:\LOGCALL\LOGCALL.mdb"
        AppPath = "c:\LOGCALL\test_Data.MDF"
        'AppPath = "\\Richard\ADS_KNOW_HOW\USERSTATS\LOGCALL.mdb"
        Set conn = New ADODB.Connection
        Set cmd = New ADODB.Command
       
        'conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AppPath & ";Persist Security Info=False"
        'conn.ConnectionString = "Provider=SQLServer;Data Source=" & AppPath & ";Persist Security Info=False"
        conn.ConnectionString = "driver={SQL Server};server=Interweb;Trusted_Connection=Yes;uid=sa;pwd=Pass;database=sqlInterweb"
        conn.ConnectionTimeout = 30
        conn.Open
       
        Set cmd.ActiveConnection = conn
        'When the call is RESOLVED
        If Range("a" & CStr(ActiveCell.Row)).Value <> "" Then
            cmd.CommandText = "INSERT INTO test VALUES ('" & Range("a" & CStr(ActiveCell.Row)).Value & _
            "','" & Range("b" & CStr(ActiveCell.Row)).Value & "');"
        End If
       
        cmd.Execute , , adCmdText
        conn.Close

  7. #7
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188
    Alex,

    you are going to the right direction. have you tried ODBC? sorry i dont have much time now to write the code for you. can anybody help?
    qha_vn

Posting Permissions

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