Delimited text File Import

thanks mate

but i was hoping that i could get help freom within vb6 using vb6 code

many thanks

hi Phil,

thanks for your help there but was wandering if you could lend me another one of your brain cells please
i copy and pasted the code you showed me, changed the names accordingly to my table and database and also text file name

and i see it says at the top of the code to construct the sql..

so i inserted my field names but i dont know what the values are ..

maybe if i post my code so far then it would possibly explain better than me

CODE

Public Function ImportTextFile(cn As Object, _
ByVal tblName As String, FileFullPath As String, _
Optional FieldDelimiter As String = ",", _
Optional RecordDelimiter As String = vbCrLf) As Boolean

Dim cmd As New adodb.Command
Dim rs As New adodb.Recordset
Dim sFileContents As String
Dim iFileNum As Integer
Dim sTableSplit() As String
Dim sRecordSplit() As String
Dim lCtr As Integer
Dim iCtr As Integer
Dim iFieldCtr As Integer
Dim lRecordCount As Long
Dim iFieldsToImport As Integer

'These variables prevent having to requery a recordset for each record
Dim asFieldNames() As String
Dim abFieldIsString() As Boolean
Dim iFieldCount As Integer
Dim sSQL As String
Dim bQuote As Boolean

'On Error GoTo errHandler
If Not TypeOf cn Is adodb.Connection Then Exit Function
If Dir(FileFullPath) = "" Then Exit Function

If cn.State = 0 Then cn.Open
Set cmd.ActiveConnection = cn
cmd.CommandText = tblName
cmd.CommandType = adCmdTable
Set rs = cmd.Execute
iFieldCount = rs.Fields.Count
rs.Close

ReDim asFieldNames(iFieldCount - 1) As String
ReDim abFieldIsString(iFieldCount - 1) As Boolean

For iCtr = 0 To iFieldCount - 1
asFieldNames(iCtr) = "[" & rs.Fields(iCtr).Name & "]"
abFieldIsString(iCtr) = FieldIsString(rs.Fields(iCtr))
Next


iFileNum = FreeFile
Open FileFullPath For Input As #iFileNum
sFileContents = Input(LOF(iFileNum), #iFileNum)
Close #iFileNum
'split file contents into rows
sTableSplit = Split(sFileContents, RecordDelimiter)
lRecordCount = UBound(sTableSplit)
'make it "all or nothing: whole text
'file or none of it
cn.BeginTrans

For lCtr = 0 To lRecordCount - 1
'split record into field values

sRecordSplit = Split(sTableSplit(lCtr), FieldDelimiter)
iFieldsToImport = IIf(UBound(sRecordSplit) + 1 < _
iFieldCount, UBound(sRecordSplit) + 1, iFieldCount)

'construct sql
sSQL = "INSERT INTO " & tblName & " ([Path],[Size],[Created],[R],[S],[A],[H]) "

For iCtr = 0 To iFieldsToImport - 1
bQuote = abFieldIsString(iCtr)
sSQL = sSQL & asFieldNames(iCtr)
If iCtr < iFieldsToImport - 1 Then sSQL = sSQL & ","
Next iCtr

sSQL = sSQL & ") VALUES ("

For iCtr = 0 To iFieldsToImport - 1
If abFieldIsString(iCtr) Then
sSQL = sSQL & prepStringForSQL(sRecordSplit(iCtr))
Else
sSQL = sSQL & sRecordSplit(iCtr)
End If

If iCtr < iFieldsToImport - 1 Then sSQL = sSQL & ","
Next iCtr

sSQL = sSQL & ")"
cn.Execute sSQL

Next lCtr

cn.CommitTrans
rs.Close
Close #iFileNum
Set rs = Nothing
Set cmd = Nothing

ImportTextFile = True
Exit Function

errHandler:
On Error Resume Next
If cn.State <> 0 Then cn.RollbackTrans
If iFileNum > 0 Then Close #iFileNum
If rs.State <> 0 Then rs.Close
Set rs = Nothing
Set cmd = Nothing

End Function

Private Function FieldIsString(FieldObject As adodb.Field) _
As Boolean

Select Case FieldObject.Type
Case adBSTR, adChar, adVarChar, adWChar, adVarWChar, _
adLongVarChar, adLongVarWChar
FieldIsString = True
Case Else
FieldIsString = False
End Select

End Function

Private Function prepStringForSQL(ByVal sValue As String) _
As String

Dim sAns As String
sAns = replace(sValue, Chr(39), "''")
sAns = "'" & sAns & "'"
prepStringForSQL = sAns

End Function

Private Sub Command3_Click()
Dim cn As New adodb.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\pcc\data\db1.mdb"
cn.Open
ImportTextFile cn, "table1", "C:\pcc\output\kodak.txt"
cmd.Execute

End Sub

END CODE

Please help cause i am getting a Syntax error in my "Insert into Statement"
as i cant give the value for my fields cause i am not to sure on how to approach that

Many Thanks in advance :eek:

hi again lol

yes i have actually run the code but yet still i get the same problem

a syntax error in my insert into statement

and then highlights the following

cn.execute sSql

and now i am out of ideas since this import is something i have never done beofer

please help again lol

thanks in advance and thanks for the help as of yet

this is now my insert statement
sSQL = "INSERT INTO " & tblName & " ("

and this is my execute command button

Private Sub Command3_Click()
Dim cn As New adodb.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\pcc\data\db1.mdb"
cn.Open
ImportTextFile cn, "table1", "C:\pcc\output\kodak easyshare.txt"
cmd.Execute

End Sub

but it still tells me there is an syntax error in my insert statement no matter what i do too it

i have tried all i know about ado and nothing is right

anymore suggestions please

thanks

INSERT INTO table1 ([Path],[Size],[Created],[r],[s],[a],[h]) VALUES ('d:\OTTBP\icm54.cab','413625','04/08/2005 08:20:20','R=True','A=False','S=False','H=False')
INSERT INTO table1 ([Path],[Size],[Created],[r],[s],[a],[h]) VALUES ('d:\OTTBP\icm55.cab','435265','04/08/2005 08:20:39','R=True','A=False','S=False','H=False')
INSERT INTO table1 () VALUES ()

that was written in the immediate window at the bottom i can see one error just looking at it i assume that the fact that it is placing the ',' between each value but to my knowledge it is supposed to be placing " ',' " meanint that it is just missing the inverted comma marks
but i dont know where to insert them into

thanks

i can now also say that it goes mental with my insert into statement when there are no more entrie to import

thankx again

yay got it working after all this time ... my thanks and gratitude to you all for your assistance... i have but one question left

now that all the entries within my text file are being imported successfully is it possible to add a constant field to the import section , such as since this is a directory scan of a cd can i possible generate a four constant field with the cd name , i have the code to retrieve the cd volume information into a text box (Text3) but am wondering how and where to insert into my code to get it to add a new field called CDTIT and enter a constant for each entry in my text file as text3 ..

please help

many thanks in advance
you guys are great