How to seperate the text file data using Mid Function?

[vbgirl]

i have an question... this is my text file data
1234567,08072008,2
8767768,08072008,4
9988776,08072008,6
7891234,09072008,8
4567891,10072008,1
5213789,11072008,2
7854123,11072008,3
4561237,14072008,1
7879145,15072008,2
5218510,16072008,8

i would like to separate it to 3 part.
this code izit correct?

Dim a(50) As String
Dim c(50) As String
Dim ProductID(50) As String
Dim i As Integer
i = 0
ProductID(i) = UCase(Left(a(i), InStr(a(i), ",") - 1))
a(i) = Mid(a(i), Len(ProductID(i)) + 1, Len(a(i)))
c(i) = Right(c(i), 0)
MsgBox a(i), vbOKOnly, "Text File"
i = i + 1

[QVeen72]

Hi,

Have you Transferred all f your contents to array a..?

If not, You need to Open the Text File First:
Open "C:\MyFile.txt" For Input as #1

and Loop Thru File Contents using
Line Input...

Regards
Veena

[vbgirl]

yes i have
Dim a(50) As String
Dim c(50) As String
Dim ProductID(50) As String
Dim i As Integer
i = 0

cdbOpenFile.Filter = "Text Files (*.txt)|*.txt| "

'Specify default file name to open
cdbOpenFile.FileName = ""
' Specify default filter to *.txt
cdbOpenFile.FilterIndex = 1

' Display the Open dialog box, and
' save the selected file in the
' variable FileSelect
cdbOpenFile.ShowOpen

Open cdbOpenFile.FileName For Input As #1
Do Until EOF(1)

Line Input #1, a(i)
ProductID(i) = UCase(Left(a(i), InStr(a(i), ",") - 1))
a(i) = Mid(a(i), Len(ProductID(i)) + 2, Len(a(i)))

'MsgBox a(i), vbOKOnly, "Text File"
i = i + 1
Loop
Close #1

this is the code that i do..

[AUGXIS]

hi, can you try this code

Dim strText As String
Dim strText1 As String
Dim xwords As Integer
Dim xwords1 As Integer
Dim cPOs, cPos1

Set objRead = objAddress.OpenTextFile(App.Path + "\mytxtfile.txt", ForReading, False)
While Not objRead.AtEndOfStream
    strText = objRead.ReadLine
    
    For xwords = 1 To Len(strText)
        If InStr(xwords, strText, ",") >= 10 Then
        cPOs = xwords
        Text1 = Mid(strText, 1, cPOs - 2)
        strText1 = Mid(strText, cPOs, Len(strText))
        Exit For
        End If
    Next xwords
    
    For xwords1 = 1 To Len(strText1)
        If InStr(xwords1, strText1, ",") <= 8 Then
        cPos1 = xwords1
        Text2 = Mid(strText1, 1, cPos1 - 2)
        Text3 = Mid(strText1, cPos1, Len(strText1))
        Exit For
        End If
    Next xwords1
    
    grid.AddItem Text1 & vbTab & Text2 & vbTab & Text3
    
    Text1 = ""
    Text2 = ""
    Text3 = ""

Wend

objRead.Close

[QVeen72]

Hi,

Just Use Split function..

Dim TArr
Line Input #1, a(i)
TArr = Split(a(i),",")
ProductID(i) = TArr(0)
a(i) = TArr(1)
c(i) = TArr(2)

Regards
Veena

[vbgirl]

Hi,

Just Use Split function..

Dim TArr
Line Input #1, a(i)
TArr = Split(a(i),",")
ProductID(i) = TArr(0)
a(i) = TArr(1)
c(i) = TArr(2)

Regards
Veena

thx... wat is TArr?

[hartoksi]

If it is fixed lengt all time it is not necessary to write such long code
for x=0 to ? step 17 '17 is the length of each line
Part1=mid$(line x,i,7)
Part2=mid$(line x,9,8)
part3=right$(line x,1)
next i

[QVeen72]

Hi,

Initially, TArr, is a Variant. When assigned with results of "Split" function , it becomes an Array..

REgards
Veena

[vbgirl]

Hi,

Initially, TArr, is a Variant. When assigned with results of "Split" function , it becomes an Array..

REgards
Veena

oh i see.. but my code still got problem why it only show out one data when update?

Public cnName As Connection
Public rsId As Recordset

Public cnName As Connection
Public rsId As Recordset



Private Sub cmdImport_Click()
Dim a(50) As String
Dim c(50) As String
Dim ProductID(50) As String
Dim i As Integer
i = 0

          cdbOpenFile.Filter = "Text Files (*.txt)|*.txt| "
          
          'Specify default file name to open
          cdbOpenFile.FileName = ""
          ' Specify default filter to *.txt
          cdbOpenFile.FilterIndex = 1
          
          ' Display the Open dialog box, and
          ' save the selected file in the
          ' variable FileSelect
            cdbOpenFile.ShowOpen



Open cdbOpenFile.FileName For Input As #1
Do Until EOF(1)
Dim id
Line Input #1, a(i)
id = Split(a(i), ".")
ProductID(i) = id(0)
a(i) = id(1)
c(i) = id(2)
'MsgBox a(i), vbOKOnly, "Text File"
i = i + 1
Loop
Close #1

Dim b As Integer
b = i

Path = App.Path
Set cnName = New ADODB.Connection
With cnName
    .CursorLocation = adUseClient
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path & "\name.mdb;Persist Security Info=False;Jet OLEDB"
    .Open
End With

Set rsId = New ADODB.Recordset
rsId.Open "ID", cnName, adOpenKeyset, adLockOptimistic

'With rsId
'.MoveFirst
'While Not .EOF
'MsgBox .Fields(0), vbOKOnly, "Access file"
'.MoveNext
'Wend
'End With
With rsId
.MoveFirst
Dim d As Integer
d = 0

For i = 0 To b
Do While Not .EOF
If ProductID(d) = rsId.Fields(0) Then
rsId.Fields(5) = a(i)
rsId.Update
End If
rsId.MoveNext
Loop
rsId.MoveFirst
d = d + 1
Next
MsgBox "Done", vbOKOnly + vbInformation, "Complete"
End With

'when data done all will show a message box "DONE"
'MsgBox "Done", vbOKOnly + vbInformation, "Complete"
End Sub

please help me thx

[QVeen72]

Hi,

I guess, You Can use "FIND" method of ADO Recordset...
Try This :

After opening the connection...

Set rsId = New ADODB.Recordset
rsId.Open "ID", cnName, adOpenKeyset, adLockOptimistic
For i = 0 To b
RSID.MoveFirst
RSID.Find "PID='" & ProductID(i) & "'"
If Not RSID.EOF Then
rsId.Fields(5) = a(i)
rsId.Update
End If
Next

MsgBox "Done", vbOKOnly + vbInformation, "Complete"
End With

Regards
Veena