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.:D
Printable View
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.:D
SQL 2000 has a Data Import & Export Wizard. Did you try that?
BTW whats your SQL version?
:wave:
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.:confused: :confused:
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.
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 AnalyzerQuote:
Originally Posted by ggodwin
Code: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
Post the InsertTable sub.
Oops...bad cut and paste. I edited it into my prior post. Thanks!Quote:
Originally Posted by Hack