Read a file and import its data into a table (2 Viewers)

[BlueIshDan]

Whatcha need to know in VB?

 

[anishkgt]

hi BlueIshDan,

Dim TextLine As String
FileOpen(1, "c:\test.txt", OpenMode.Input)
While Not EOF(1)
TextLine = LineInput(1)
MsgBox(TextLine)
End While
FileClose(1)

In the above code i need to read line number 9 and pick characters from 4 to 18 and display it in a text box(for now). Similarly i need to do for the other lines with different character count. if i could get the code for Line 9 then i guess i can do for the rest.

 

[namliam]

I think I told and showed you a few times already :banghead:

You then use If left(textline,x) = "Asomething" then....
x would depend on what defines your line, if it is A12, then obviously x would be 3
To find out what line you are at and use functions like Left, Right and Mid to extract the information you need from the line ....

:banghead:

I assume the first four chars of any line will identify the "line type"
I.e. on line A040 you know you can find the airline name from character 11 to 24
Research Line Input or is it Input line, that should get you started.

:banghead:

Well reading each line is atleast the starting point, walk before you can run.

Once you can read each line, you can then handle the different lines using an

If Left(yourLine,3) = "A12" then
   'handle the A12 line
endif
If Left(yourLine,3) = "A14" then
   'handle the A14 line
endif

etc...

:banghead:

 

[anishkgt]

obviously if you've showed, it means i can't get it. what does yourline ,3 mean. and what comes after the then.

 

[anishkgt]

To start with, i've made it so far but i get an error for passenger
"Error 1 Value of type 'String' cannot be converted to 'System.Windows.Forms.TextBox'. C:\Users\georget\AppData\Local\Temporary Projects\WindowsApplication2\Form1.vb 9 20 WindowsApplication2
"
Public Class Form1

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim Passenger As String
Dim intEmpFileNbr As Integer
Dim strEmpRecord As String
intEmpFileNbr = FreeFile()
Passenger = Microsoft.VisualBasic.Left(strEmpRecord, 4)
TextBox1 = Passenger
End Sub
End Class

 

[anishkgt]

ok managed to clear that error the correct was "TextBox1.Text = Passenger" now i get the error on this line
" Passenger = Microsoft.VisualBasic.Left(strEmpRecord, 4)"
where strEmpRecord shows an error as

"Variable 'strEmpRecord' is used before it has been assigned a value. A null reference exception could result at runtime.
"
and probably due to that there is no value displayed in the text box when executed. How do i resolve this ?

 

[namliam]

Yourline as I explained along the way is a variable that would hold the complete line that you read...
3 as in the LEFT 3 characters of your line

How did you go from something that was atleast working to something that is as broken as you have now?

hi BlueIshDan,

Dim TextLine As String
FileOpen(1, "c:\test.txt", OpenMode.Input)
While Not EOF(1)
TextLine = LineInput(1)
MsgBox(TextLine)
End While
FileClose(1)

In the above code i need to read line number 9 and pick characters from 4 to 18 and display it in a text box(for now). Similarly i need to do for the other lines with different character count. if i could get the code for Line 9 then i guess i can do for the rest.

        Dim TextLine As String
        FileOpen(1, "c:\test.txt", OpenMode.Input)
        While Not EOF(1)
            TextLine = LineInput(1)
            MsgBox("'" & Left(TextLine,3) & "' are the first 3 characters" )
            MsgBox("'" & Mid(TextLine,3,3) & "' are the next 3 characters" )
            MsgBox("'" & mid(TextLine,20,10) & "' 20th to 30th characters" )
            MsgBox("'" & Right(TextLine,3) & "' are the last 3 characters" )

        End While
        FileClose(1)

You try to identify the line you are processing by for example the first 3 characters of your line... not using the line numbers, because that is likely going to be unrealiable due to more records or other influences.

 

[anishkgt]

I pasted the above code and i get the below error

"Public Property Left As Integer' has no parameters and its return type cannot be indexed."

 

[namliam]

Left should be a normal function that you should be able to use?

I dont have much / if any / experience coding VB directly, always used VBA.
Why do you choose to do VB instead of using VBA inside the database?

 

[BlueIshDan]

This has gone down a rabbit hole!

 

[anishkgt]

Namliam, thought this part would only work in vb. Will try this out in access vba.

 

[anishkgt]

shows a syntax error at Private Sub Command0_Click()

 

[namliam]

Post your full procedure...

I hope you arent using the vb code, like
having
FileOpen(1, "c:\test.txt", OpenMode.Input)
Instead of...
Open "C:\test.txt" For Input As #1

 

[anishkgt]

I was using the VB code in access, thought the codes where the same. here is the whole code


Private Sub Command0_Click()
Dim TextLine As String
Open "c:\test.txt" For Input As #1
While Not EOF(1)
TextLine = LineInput(1)
MsgBox ("'" & Left(TextLine, 3) & "' are the first 3 characters")
MsgBox ("'" & Mid(TextLine, 3, 3) & "' are the next 3 characters")
MsgBox ("'" & Mid(TextLine, 20, 10) & "' 20th to 30th characters")
MsgBox ("'" & Right(TextLine, 3) & "' are the last 3 characters")

End While
FileClose (1)
End Sub

 

[BlueIshDan]

use my function for reading the lines...

 

[namliam]

No, VB and VBA are NOT the same

Please use code tags when you post code, see the link in my signature for details.

From the link I gave you waaaay at the start....

Dim TextLine
Open "TESTFILE" For Input As #1   ' Open file.
Do While Not EOF(1)   ' Loop until end of file.
   Line Input #1, TextLine   ' Read line into variable.
   Debug.Print TextLine   ' Print to the Immediate window.
Loop
Close #1   ' Close file.

Adding the message boxxes to it...

Dim TextLine as String 'Idiots at M$ never add AS ... 
Open "TESTFILE" For Input As #1   ' Open file.
Do While Not EOF(1)   ' Loop until end of file.
   Line Input #1, TextLine   ' Read line into variable.
'Slightly more friendly to have only one box instead of 4
   MsgBox ("'" & Left(TextLine, 3) & "' are the first 3 characters" & Vbnewline & _
           "'" & Mid(TextLine, 3, 3) & "' are the next 3 characters" & Vbnewline & _
           "'" & Mid(TextLine, 20, 10) & "' 20th to 30th characters" & Vbnewline & _
           "'" & Right(TextLine, 3) & "' are the last 3 characters"
   Debug.Print TextLine   ' Print to the Immediate window.
Loop
Close #1   ' Close file.

 

[namliam]

use my function for reading the lines...

What happened to the rabbit hole?

 

[BlueIshDan]

The rabbits didn't want me and kicked me out? I don't know. lol

 

[anishkgt]

Hi namliam,
i get the below error when clicking on the button in the form

Compile error:synatax error

I've pasted the below code in the On Click event of the button property

Option Compare Database

Private Sub Command0_Click()
Dim TextLine As String 'Idiots at M$ never add AS ...
Open "c:\test.txt" For Input As #1   ' Open file.
Do While Not EOF(1)   ' Loop until end of file.
   Line Input #1, TextLine   ' Read line into variable.
'Slightly more friendly to have only one box instead of 4
   MsgBox ("'" & Left(TextLine, 3) & "' are the first 3 characters" & Vbnewline & _
           "'" & Mid(TextLine, 3, 3) & "' are the next 3 characters" & Vbnewline & _
           "'" & Mid(TextLine, 20, 10) & "' 20th to 30th characters" & Vbnewline & _
           "'" & Right(TextLine, 3) & "' are the last 3 characters"
   Debug.Print TextLine   ' Print to the Immediate window.
Loop
Close #1   ' Close file.
End Sub

 

[namliam]

Hi namliam,
i get the below error when clicking on the button in the form

Compile error:synatax error

I've pasted the below code in the On Click event of the button property

Option Compare Database

Private Sub Command0_Click()
Dim TextLine As String 'Idiots at M$ never add AS ...
Open "c:\test.txt" For Input As #1   ' Open file.
Do While Not EOF(1)   ' Loop until end of file.
   Line Input #1, TextLine   ' Read line into variable.
'Slightly more friendly to have only one box instead of 4
   MsgBox ("'" & Left(TextLine, 3) & "' are the first 3 characters" & Vbnewline & _
           "'" & Mid(TextLine, 3, 3) & "' are the next 3 characters" & Vbnewline & _
           "'" & Mid(TextLine, 20, 10) & "' 20th to 30th characters" & Vbnewline & _
           "'" & Right(TextLine, 3) & "' are the last 3 characters"[COLOR="Red"])[/COLOR]
   Debug.Print TextLine   ' Print to the Immediate window.
Loop
Close #1   ' Close file.
End Sub

I removed one to many brackets, fixed above.