Read a file and import its data into a table

Whatcha need to know in VB?
 
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.
 
I think I told and showed you a few times already :( :banghead:

namliam said:
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 ....
Click to expand...
:banghead:
namliam said:
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.
Click to expand...
:banghead:
namliam said:
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
Code: 
If Left(yourLine,3) = "A12" then
   'handle the A12 line
endif
If Left(yourLine,3) = "A14" then
   'handle the A14 line
endif
etc...
Click to expand...

:banghead:
 
obviously if you've showed, it means i can't get it. what does yourline ,3 mean. and what comes after the then.
 
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
 
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 ?
 
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?

anishkgt said:
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.
Click to expand...
Code: 
        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.
 
Last edited:
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."
 
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?
 
This has gone down a rabbit hole!
business-cat-meme-generator-and-for-that-reason-i-m-out-4eccda.jpg
 
Namliam, thought this part would only work in vb. Will try this out in access vba.
 
shows a syntax error at Private Sub Command0_Click()
 
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
 
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
 
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....
Code: 
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...
Code: 
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.
 
Hi namliam,
i get the below error when clicking on the button in the form
Code: 
Compile error:synatax error
I've pasted the below code in the On Click event of the button property

Code: 
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
 
anishkgt said:
Hi namliam,
i get the below error when clicking on the button in the form
Code: 
Compile error:synatax error
I've pasted the below code in the On Click event of the button property

Code: 
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
Click to expand...

I removed one to many brackets, fixed above.
 
You must log in or register to reply here.

Similar threads

I
Get a field ID
Replies
3
Views
606
Isaac
Isaac
M
Importing PowerQuery results into SQL Server
Replies
3
Views
267
arnelgp
arnelgp
T
  • Question Question
Advice needed please. Importing Sharepoint Text file into List and Deleting previous days data
Replies
4
Views
803
Isaac
Isaac
oxicottin
Solved Import from excel and delete unwanted data
Replies
14
Views
761
oxicottin
oxicottin
brucemc777
Solved Can one import a named table from Excel into either a new table, or an empty table created for the purpose, in Access?
Replies
1
Views
499
azhar2006
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom