URGENT : Parsing of a file of info to be imported in to an access data base.
I have a large file of text to input into a mdb data file.
The following function opens a text file, and is supposed to extract an entire line of text
URGENT : Parsing of a file of info to be imported in to an access data base.
' ok here is where the work is done
' get our file str
Dim strfile$
Dim sLine
strfile = ff.Text
'
Open strfile For Input As #1
While Not (EOF(1))
Input #1, sLine
sLine = Replace(sLine, Chr(44), "~")
'parse data and save in Access .mdb
Call ParseString(sLine)
txtImport.Text = txtImport.Text & vbCrLf & "++++++++++++++++" & vbCrLf & sLine & vbCrLf
DoEvents
Wend
Close #1
End Sub
The text to be extraxted in one line is
Finemore, Stewart Andrew|1/20050722160450/97
the problems arise in that the function treats the "," as the EOL - so only p[art of the string is extracted ...
Any idea ?
The following function opens a text file, and is supposed to extract an entire line of text
URGENT : Parsing of a file of info to be imported in to an access data base.
' ok here is where the work is done
' get our file str
Dim strfile$
Dim sLine
strfile = ff.Text
'
Open strfile For Input As #1
While Not (EOF(1))
Input #1, sLine
sLine = Replace(sLine, Chr(44), "~")
'parse data and save in Access .mdb
Call ParseString(sLine)
txtImport.Text = txtImport.Text & vbCrLf & "++++++++++++++++" & vbCrLf & sLine & vbCrLf
DoEvents
Wend
Close #1
End Sub
The text to be extraxted in one line is
Finemore, Stewart Andrew|1/20050722160450/97
the problems arise in that the function treats the "," as the EOL - so only p[art of the string is extracted ...
Any idea ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What does your ParseString Function look like?
Maybe this example can help:
' add a reference to Microsoft ActiveX Data Objects Library:
Private Sub Command1_Click()
Dim DataBase As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
DataBase = "C:\Db1.mdb"
' open Database Connection
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OL
cn.CursorLocation = adUseClient
cn.Open
' open recordset
Set rs = New ADODB.Recordset
rs.Open "Select * From Table1", cn, adOpenKeyset, adLockOptimistic
Dim strfile As String
Dim sLine As String
Dim arrLine() As String
Dim ff As Integer
strfile = ff.Text
ff = FreeFile
Open strfile For Input As #ff
While Not (EOF(ff))
Line Input #ff, sLine
arrLine = Split(sLine, "|")
rs.AddNew
Dim x As Long
For x = LBound(arrLine) To UBound(arrLine)
If Trim$(arrLine(x)) <> "" Then
rs.Fields(x).Value = arrLine(x)
End If
Next x
rs.Update
DoEvents
Wend
Close #ff
' close recordset
rs.Close
Set rs = Nothing
' close connection
cn.Close
Set cn = Nothing
End Sub
' add a reference to Microsoft ActiveX Data Objects Library:
Private Sub Command1_Click()
Dim DataBase As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
DataBase = "C:\Db1.mdb"
' open Database Connection
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OL
cn.CursorLocation = adUseClient
cn.Open
' open recordset
Set rs = New ADODB.Recordset
rs.Open "Select * From Table1", cn, adOpenKeyset, adLockOptimistic
Dim strfile As String
Dim sLine As String
Dim arrLine() As String
Dim ff As Integer
strfile = ff.Text
ff = FreeFile
Open strfile For Input As #ff
While Not (EOF(ff))
Line Input #ff, sLine
arrLine = Split(sLine, "|")
rs.AddNew
Dim x As Long
For x = LBound(arrLine) To UBound(arrLine)
If Trim$(arrLine(x)) <> "" Then
rs.Fields(x).Value = arrLine(x)
End If
Next x
rs.Update
DoEvents
Wend
Close #ff
' close recordset
rs.Close
Set rs = Nothing
' close connection
cn.Close
Set cn = Nothing
End Sub
ASKER
Daniel_J_C you hit the nail on the head , thank you ....
vinnyd79 as usual your comments were useful ....
Many thanks guys
Darrin
vinnyd79 as usual your comments were useful ....
Many thanks guys
Darrin