sql insert recs from txt file

[roswald]

I want to read through a text file and then insert the values from those recs into a SQL Server 2000 table using the INSERT stmt.
Here is the text file code.

F = FreeFile
Open "\\sthcepicprod1\imports\ANSI\CCT\CCTPreSort.txt" For Input As F

'Declare recordset settings
Set rsSort = New ADODB.Recordset
With rsSort
Set .ActiveConnection = Nothing
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
'Define recordset fields and properties
With .Fields
.Append "Long1", adChar, 39
.Append "Memno", adChar, 18
.Append "Long2", adChar, 125
.Append "Long3", adChar, 81
.Append "FromDate", adChar, 7
.Append "ThruDate", adChar, 7
.Append "Long4", adChar, 116
.Append "Long5", adChar, 187
End With
.Open
End With

'Write data elements to recordset
Do While Not EOF(F)
Line Input #F, sLine
Long1 = Mid(sLine, 1, 39)
Memno = Mid(sLine, 40, 18)
Long2 = Mid(sLine, 58, 125)
Long3 = Mid(sLine, 183, 81)
FromDate = Mid(sLine, 264, 7)
ThruDate = Mid(sLine, 271, 7)
Long4 = Mid(sLine, 278, 116)
Long5 = Mid(sLine, 394, 187)

Loop


Is there a way to delclare my SQL recordset and in the cmd statement use this text code in the VALUES part. i.e.
WITH sqlcmd
.commandtext "insert into dbo.atable values(Could something from the text code go in here)

If someone could give me an example to get me going in the right path I sure would appreciate it.

Thanks alot,

Bob

 

[hookbob]

Hi Bob,

I hope this will help you. I've used the INSERT INTO statement a few times and came up with some restrictions. You need to get a variable list (called varlist below) and a values list (called valuelist below).

varlist is a list of all the field names in the table you are going to insert into separated like a CSV file
eg. "AccNo","AccName","Address1","Address2"
Use the following function to get this variable :

Declare Function GetVarList(dboTable as Adodb.Recordset) as String
Dim i as Integer
GetVarList = dboTable.Fields(0).Name
For i = 1 to dboTable.Fields.Count - 1
GetVarList = GetVarList & """,""" & dboTable.Fields(i).Name
Next i
End Function


valuelist is the list of values to be inserted in the fields defined in varlist using the same format.
eg. "000001","Bloggs Limited","Lazy Street","Smithville"
This variable must be set up in the same way as the varlist
eg. valuelist = Mid(sLine,1,39) & """,""" & Mid(sLine,40,18) & """,""" & Mid(sLine,58,125) etc...

Then you need to execute the command :


SQLCommand = "INSERT INTO dbo.aTable (" & varlist & &quot VALUES (" & valuelist & &quot"
dbo.Execute SQLCommand

Let me know if this helps.

Bobby.