reading text file

[Toracle]

hey people,

i have no basic idea of reading from a text file .. anyone has any gd websites or can anyone give me a simple run through? i got my textfile as such

4140483656|2|4140483656|232|12/10/2005|2005102|SGD|Credit|70.00|
4141838597|1 2|4141838597|037|12/10/2005|2005102|SGD|Credit|1,269.00|
4141837031|1 2 3 4|4141837031|618|13/10/2005|2005102|SGD|Credit|7,836.00|
4140500984|1 2|4140500984|160|13/10/2005|2005102|SGD|Cash|458.00|
4141817093|3|4141817093|618|13/10/2005|2005102|SGD|Credit|690.00|
<-needed->|<-not needed--->|<-needed->|<-----not needed--------->|


necessary fields are separated by |

anyone can help?

[rahul.kul]

HI Toracle..

You can use the Following code to open a Text File and load all the Text from it by Line-By Line..

Dim iFile as integer
 iFile = FreeFile 
	Open strFilename For Input Access Read Lock Read Write As #iFile
	' Loop through each line, looking for the key
	Do Until EOF(iFile)
		Line Input #iFile, strLine
		MyStr= MyStr & chr(13) & strLine 
	Loop

Finally You can use Msgbox MyStr to view your File...

You can apply other function to part the string (use StrLine Variable)
you can use Use Mid Function in that Function.

Regards !

[rahul.kul]

If your have string like this.. (for Example)

Mystr = 4140483656^232#12/10/2005@Credit

Here ^, #, @ are the Sepreators.. and then I can use Mid and Instr functions to get the Strings...!
Lile this..

String1 = Mid(Mystr,1, Instr(1, Mystr,"^")-1) will return 4140483656
String2 = Mid(Mystr, Instr(1, Mystr,"^")+1, Instr(1, Mystr,"#")-1) will return 232
....

and so on.. !

So i advice you to use different seprators instead of single | symbol as it is hard to part it..!

[Shuja Ali]

If your have string like this.. (for Example)

Mystr = 4140483656^232#12/10/2005@Credit

Here ^, #, @ are the Sepreators.. and then I can use Mid and Instr functions to get the Strings...!
Lile this..

String1 = Mid(Mystr,1, Instr(1, Mystr,"^")-1) will return 4140483656
String2 = Mid(Mystr, Instr(1, Mystr,"^")+1, Instr(1, Mystr,"#")-1) will return 232
....

and so on.. !

So i advice you to use different seprators instead of single | symbol as it is hard to part it..!

Not exactly, the | (pipe sign) is always a better seperator than any other character. A little modification of your code will give each field properly

	Dim iFile As Integer, sData() As String, strLine As String
	Dim sVar   ' a variant
	iFile = FreeFile
	Open strFilename For Input Access Read Lock Read Write As #iFile
	' Loop through each line, looking for the key
	Do Until EOF(iFile)
		Line Input #iFile, strLine
		sData = Split(strLine, "|") 'here you get all the data of one line in an array
		'write your code for processing the data here, now whole of your data is contained within the sData Array
		For Each sVar In sData
			 Debug.Print sVar   'print the data just an example.
		Next
	Loop

[HanneSThEGreaT]

So i advice you to use different seprators instead of single | symbol as it is hard to part it..!

Why you say so Rahul ¿

I prefer to use CSV's (commas), Tab's, Enter's, as well as |, and I can achieve the same thing with all of them

[rahul.kul]

Hi Shuja..

Okay.. I agree. with you.. but why givinh it an Erro when I try for it..?

 MsgBox Split("Rahul#Kul", "#")

[Toracle]

Not exactly, the | (pipe sign) is always a better seperator than any other character. A little modification of your code will give each field properly

	Dim iFile As Integer, sData() As String, strLine As String
	Dim sVar   ' a variant
	iFile = FreeFile
	Open strFilename For Input Access Read Lock Read Write As #iFile
	' Loop through each line, looking for the key
	Do Until EOF(iFile)
		Line Input #iFile, strLine
		sData = Split(strLine, "|") 'here you get all the data of one line in an array
		'write your code for processing the data here, now whole of your data is contained within the sData Array
		For Each sVar In sData
			 Debug.Print sVar   'print the data just an example.
		Next
	Loop

hi guys thanks for your help! i need some time to digest the codes. I have to use the | by default as if u already realize this is an airline report. and this report comes from the airline cant request for them to suit my own format.. another thing is i need to use the text until the first | to reference to my database. is it possible? meaning that i have a listview. i will have all the record in my database and when the airline sends me meaning that i will use this textfile and search in my database.. only those that is same in the database and textfile will be shown... possible?

[Shuja Ali]

Hi Shuja..

Okay.. I agree. with you.. but why givinh it an Erro when I try for it..?

 MsgBox Split("Rahul#Kul", "#")

Split function returns an Array, and you cannot display an array like this. MsgBox requires a string, so it might be giving you a Type Mismatch error. Try this instead

    Dim sTemp() As String, sVar As Variant
    sTemp = Split("Rahul#Kul", "#")
    For Each sVar In sTemp
        MsgBox sVar
    Next

[rahul.kul]

Thanks Shuja !!!

It's Working.. Keep it up !

[Shuja Ali]

hi guys thanks for your help! i need some time to digest the codes. I have to use the | by default as if u already realize this is an airline report. and this report comes from the airline cant request for them to suit my own format.. another thing is i need to use the text until the first | to reference to my database. is it possible? meaning that i have a listview. i will have all the record in my database and when the airline sends me meaning that i will use this textfile and search in my database.. only those that is same in the database and textfile will be shown... possible?

This is very much possible. You will have to open connection to the database and use the specific fields from the File (once you get the data from the file) to search in the database using a query.
Some thing similar to this will help

    Dim iFile As Integer, strLine As String
    Dim sVar As String   ' a variant
    iFile = FreeFile
    Open strFilename For Input Access Read Lock Read Write As #iFile
    ' Loop through each line, looking for the key
    Do Until EOF(iFile)
        Line Input #iFile, strLine
        'To get the specific fields you can either use MID function, or the split.
        'say if i have to get the data from 10th position of the line till 15th position, i will do like this
        sVar = Mid(strLine, 10, 5)
        'use svar to search for the data in the database
        rs.Open "Select  from SOMETABLE Where FIELD = '" & sVar & "'", databaseConnection
        If Not rs.EOF Then
            'data is present show it in the datagrid
        End If
    Loop

try to read through the code and see what exactly is happening. This will give you an idea atleast of wha needds to be done.

[Toracle]

guys i tried to do abit of the codes by copying here and there within this forum.. can u help me see if i did anything wrong?

the error lies here

For Each sVar In sData
Debug.Print sVar 'print the data just an example.
Next

it says
Compile Error: For Each Control variable on array must be variant


anyone can help??



Private Sub btnImport_Click()

Dim dcnRefund As New ADODB.Connection
dcnRefund.CursorLocation = adUseClient
dcnRefund.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source = D:\Alvin Tan\My Documents\Refund\Refund.mdb;"

Set rstImport = New ADODB.Recordset
Dim item As ListItem
Dim iFile As Integer, sData() As String, strLine As String
Dim sVar As String ' a variant
iFile = FreeFile
Open txtFileName For Input Access Read Lock Read Write As #iFile

' Loop through each line, looking for the key
Do Until EOF(iFile)
Line Input #iFile, strLine
sData = Split(strLine, "|")
'To get the specific fields you can either use MID function, or the split.
'say if i have to get the data from 10th position of the line till 15th position, i will do like this
' sVar = Mid(strLine, 10, 5)
'use svar to search for the data in the database
For Each sVar In sData
Debug.Print sVar 'print the data just an example.
Next

rstImport.Open "SELECT * , tblRefund.RefundID AS RefundID FROM tblRefund INNER JOIN tblRefundCal ON tblRefund.RefundID=tblRefundCal.RefundID WHERE tblRefund.TicketNo='" & sVar & "'", dcnRefund

If Not rstImport.EOF Then
rstImport.MoveFirst
Do Until rstImport.EOF
Set item = lstRefundNoticeImport.ListItems.Add(, , rstImport!RefundID)
item.SubItems(1) = rstImport!CreatedDate & ""
item.SubItems(2) = rstImport!PaxName & ""
item.SubItems(3) = rstImport!TicketNo & ""
item.SubItems(4) = rstImport!NettRefundAmt & ""
rstImport.MoveNext
Loop
End If

Loop

End Sub

[Shuja Ali]

Change sVar to a Variant rather than a String. When you use For Each Loop to loop through an Array, you need to initialize the variable as Variant. So this statement should look like this

Dim sVar As Variant

And when you run this query after executing the Loop, the sVar variable will have the data from the last field of the file. So you may end up with incorrect results. If you know the exact position of the Field which you want to use in your query then you could use it like this

rstImport.Open "SELECT * , tblRefund.RefundID AS RefundID FROM tblRefund INNER JOIN tblRefundCal ON tblRefund.RefundID=tblRefundCal.RefundID WHERE tblRefund.TicketNo='" & sData(10) & "'", dcnRefund

10 here is just an example, this means the query wil use the 11th field from the array which we got after reading a line from the file.
Relook at your code and see what is happening and what actually you want it to do.

[Toracle]

if i used

Open txtFileName For Input Access Read Lock Read Write As #iFile


to open my textfile,

is there any code i can use to close it?

[HanneSThEGreaT]

if i used

Open txtFileName For Input Access Read Lock Read Write As #iFile


to open my textfile,

is there any code i can use to close it?

Close #iFile

[Toracle]

Not exactly, the | (pipe sign) is always a better seperator than any other character. A little modification of your code will give each field properly

	Dim iFile As Integer, sData() As String, strLine As String
	Dim sVar   ' a variant
	iFile = FreeFile
	Open strFilename For Input Access Read Lock Read Write As #iFile
	' Loop through each line, looking for the key
	Do Until EOF(iFile)
		Line Input #iFile, strLine
		sData = Split(strLine, "|") 'here you get all the data of one line in an array
		'write your code for processing the data here, now whole of your data is contained within the sData Array
		For Each sVar In sData
			 Debug.Print sVar   'print the data just an example.
		Next
	Loop

hey people i got a question again .. lets say i use the codes above ... my data would become like this rite?

from 4140483656|2|4140483656|232|12/10/2005|2005102|SGD|Credit|70.00|

to

4140483656
2
4140483656
232
12/10/2005
2005102
SGD
Credit
70.00

how can i code so that i capture only the first row which is 4140483656 and the fifth row 12/10/2005 only. is it possible?