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/972|286 Tracey Street||Lavington|NSW|2641|0260490558|0260409558|0400158520|275.00|22-07-2005|22-04-2006||Monthly||||||||||34587.97|590.30|04-06-1970|"

the problems arise in that the function treats the "," as the EOL - so only p[art of the string is extracted ...

Any idea ?




LVL 1
dwe0608Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

daniel_j_cCommented:
ouch! you're using the oldest file io routines invented! anyway, instead of saying

Input #1, sLine

use

Line Input #1, sLine
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vinnyd79Commented:
What does your ParseString Function look like?
0
vinnyd79Commented:
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.OLEDB.4.0;Data Source='" & DataBase & "'"
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
0
dwe0608Author Commented:
Daniel_J_C you hit the nail on the head , thank you ....

vinnyd79 as usual your comments were useful ....

Many thanks guys

Darrin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.