VB application to Import Excel data into SQL?

[ggodwin]

I am just looking to see if anyone here has a VB application that can import data into SQL from .xls,.csv,.mdb

Rather than rewriting one I would like to just piggy back. I just need a single import.

[zeezee]

SQL 2000 has a Data Import & Export Wizard. Did you try that?
BTW whats your SQL version?

[ggodwin]

The server is MS SQL 2005. I am using SQL Sever managment express and for the life of me.....

I can not get anything to work..No Bulk Insert (Server host issue). No DTS or SSIS..

So I can kind of thrown in the towel figured some one has hard coded one into an application some where.

[UITDEV]

Sorry its a bit late to reply, but for the benefit of others....

If the version is SQL Server 2005 Express, then you wont have SSIS (DTS is called SSIS in version 2005). You will need at least the Standard version. You can order a "developer" copy for a nominal cost or download the trial version for 120 days I believe they give you.. Then you will gain access to SSIS.

[TysonLPrice]

I am just looking to see if anyone here has a VB application that can import data into SQL from .xls,.csv,.mdb

Rather than rewriting one I would like to just piggy back. I just need a single import.

This is from a one time load that is really not written very well. It worked for what I needed at the time and could be a shell for you. It just wrote to a temporary table for me to work with in Query Analyzer

Option Explicit
Private cnTest As New ADODB.Connection
Private rsTempRecordset As New ADODB.Recordset
Private strClaimNumber As String
Private strFullName As String
Private strDOI As String
Private strSSN As String
Private sConnString As String
Private strFileName As String
Private lngRecordCounter As Long
Private lngDoEvents As Long
Private LCount As Long
Private strSQL As String
Private xl  As Excel.Application
Private xlWorkBook As Excel.Workbook
Private xlWorksheet As Excel.Worksheet
Private strLocation As String
Private strCorp As String
Private strSCMSNA As String
Private strFirstName As String
Private strLastName As String
Private strSourceAddress As String
Private strTargetAddress As String

Private Sub Command1_Click()
    
    Call CreateTable
    Call ReadText
    
    MsgBox "Clicking OK will drop the table and exit"
    
    End
    
End Sub
Private Sub CreateTable()

    strSQL = "if object_id('tempdb..##AD_Migration_Conversion_Table') is not null drop table ##AD_Migration_Conversion_Table " & _
    "create table ##AD_Migration_Conversion_Table(Location VarChar(50),Corp varchar(50), SCMSNA VarChar(50), FirstName varchar(20)" & _
    ", LastName VarChar(50), SourceAddress VarChar(50), TargetAddress VarChar(50)) "
    
    rsTempRecordset.Open strSQL, cnTest, adOpenForwardOnly, adLockReadOnly

End Sub

Private Sub Form_Load()
    
   'sConnString = "Server=cmisql;Database=Manhattan;Driver=SQL Server;Trusted_Connection=Yes;DSN=cmisql"
   'sConnString = "Server=chssql;Database=Manhattan;Driver=SQL Server;Trusted_Connection=Yes;DSN=chssql"
   'sConnString = "Server=CommonSQL;Database=accounts;Driver=SQL Server;Trusted_Connection=Yes;DSN=CommonSQL"
   'sConnString = "Server=devsql\tpa1;Database=manhattan;Driver=SQL Server;Trusted_Connection=Yes;DSN=devsql\tpa1"
   'sConnString = "Server=uasql\cmisql;Database=manhattan;Driver=SQL Server;Trusted_Connection=Yes;DSN=uasql\cmisql"
    sConnString = "Server=uasql\commonsql;Database=accounts;Driver=SQL Server;Trusted_Connection=Yes;DSN=uasql\commonsql"
   
    With cnTest
        .ConnectionString = sConnString
        .ConnectionTimeout = 4
        .CursorLocation = adUseClient
        .Open
    End With

End Sub

Private Sub ReadText()
    
    strFileName = "C:\ad migration\conversiontable.xls"
    
    Set xl = New Excel.Application
    
    xl.Visible = True
    
    xl.Application.Workbooks.Open (strFileName)
    
    Set xlWorksheet = xl.Sheets(1)
    xlWorksheet.Activate
    
    ProgressBar1.Min = 0
    ProgressBar1.Max = xlWorksheet.UsedRange.Rows.Count
    ProgressBar1.Value = 0
    ProgressBar1.Visible = True
    
   ' strSQL = "if object_id('tempdb..##AD_Migration_Conversion_Table') is not null drop table ##AD_Migration_Conversion_Table " & _
   '"create table ##AD_Migration_Conversion_Table(ClaimNumber VarChar(12)) "
    
   ' rsTempRecordset.Open strSQL, cnTest, adOpenForwardOnly, adLockReadOnly
    
    With xl
        For LCount = 2 To xlWorksheet.UsedRange.Rows.Count
            strLocation = .Cells(LCount, 1).Value
            strCorp = .Cells(LCount, 2).Value
            strSCMSNA = .Cells(LCount, 3).Value
            strFirstName = .Cells(LCount, 4).Value
            strLastName = .Cells(LCount, 5).Value
            strSourceAddress = .Cells(LCount, 6).Value
            strTargetAddress = .Cells(LCount, 7).Value
            ProgressBar1.Value = ProgressBar1.Value + 1
            lblReportStatus.Caption = Int(ProgressBar1.Value * 100 / ProgressBar1.Max) & " % Completed"
            lblReportStatus.Refresh
            If lngDoEvents > 100 Then
                DoEvents
                lngDoEvents = 0
            Else
                lngDoEvents = lngDoEvents + 1
                Call InsertTable
            End If
        Next
    End With
    
    xl.Visible = False
    Set xlWorksheet = Nothing
    'xlWorkBook.Close
    Set xlWorkBook = Nothing
    xl.Quit
    Set xl = Nothing

End Sub

Private Sub InsertTable()

    strSQL = "Insert into ##AD_Migration_Conversion_Table(Location,corp,scmsna,firstname,lastname,sourceaddress,targetaddress) " & _
    "values('" & strLocation & "','" & strCorp & "','" & strSCMSNA & "','" & strFirstName & "','" & _
     strLastName & "','" & strSourceAddress & "','" & strTargetAddress & "')"
    
    rsTempRecordset.Open strSQL, cnTest, adOpenForwardOnly, adLockReadOnly


End Sub

[Hack]

Post the InsertTable sub.

[TysonLPrice]

Post the InsertTable sub.

Oops...bad cut and paste. I edited it into my prior post. Thanks!