Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

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 ?




0
dwe0608
Asked:
dwe0608
  • 2
1 Solution
 
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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now