import txt file skip first 10 lines? (1 Viewer)

[Access_guy49]

Hey everyone!
Is there a way to import a text file but skip the first 10, or 15 lines?
I have an output file from a program that our Hydrologist uses to predict spring melt runoff flooding. The output is a db file (not dbf) so i can only open it with text editor and bring it into access as a txt file.

The file has a bunch of comments at the top explaining the file, i want to skip them. I can't change the output from the software used, i was just hoping to get the first 10 lines out of the way so that i can automatically import the data using VBA. I want this to be simple for them to use. I want them to click a button and select the file, and the code will do the rest.

Any help/advice/liturature would be greatly appreciated.

 

[vbaInet]

You would employ File I/O functions to read the contents of the file to your destination table. Here's a link:

http://www.applecore99.com/gen/gen029.asp

Then as you go through the loop, ignore the first 10 lines.

 

[jdraw]

Hey everyone!
Is there a way to import a text file but skip the first 10, or 15 lines?
I have an output file from a program that our Hydrologist uses to predict spring melt runoff flooding. The output is a db file (not dbf) so i can only open it with text editor and bring it into access as a txt file.

The file has a bunch of comments at the top explaining the file, i want to skip them. I can't change the output from the software used, i was just hoping to get the first 10 lines out of the way so that i can automatically import the data using VBA. I want this to be simple for them to use. I want them to click a button and select the file, and the code will do the rest.

Any help/advice/liturature would be greatly appreciated.

You could import to a temp table. Then, knowing what makes a comment line, filter those lines out as you move data from the temp table to your "real" table using an append query.

Just a thought.

 

[Access_guy49]

You would employ File I/O functions to read the contents of the file to your destination table. Here's a link:

http://www.applecore99.com/gen/gen029.asp

Then as you go through the loop, ignore the first 10 lines.

I'll try this method first because i think it will give me a little better control over what i want to do with this file, Thank you BOTH! (i'll let you know how it worked out)

 

[SOS]

I just use the Open File as #1 and then just set a count variable to go through until I want the data. Very simple and straightforward.

 

[Access_guy49]

I just use the Open File as #1 and then just set a count variable to go through until I want the data. Very simple and straightforward.

I have never used that before, but i'll google it up and see if i can learn how to use that, it sounds easy enough.

 

[vbaInet]

It certainly does give you much control. Have a look at the Dictionary object as well, it may come in handy for associating a certain ID with a field. Here's a function for counting the number of lines in a file (paste into a module):

Public Function LinesInFile(ByVal file_name As String) As Long
On Error GoTo Err_Handler
' Function used for counting the number of lines in a file

    Dim fnum As Integer
    Dim lines As Long
    Dim one_line As String

    fnum = FreeFile
    Open file_name For Input As #fnum
    Do While Not EOF(fnum)
        Line Input #fnum, one_line
        lines = lines + 1
    Loop
    Close fnum

    LinesInFile = lines

Exit_Err_Handler:
    Exit Function

Err_Routine:
    MsgBox Err.Description
    Resume Exit_Err_Handler
    
End Function

 

[Access_guy49]

OK new question, once i read through a file. lets say i have a test file and once i get into the data it looks like this:

(sorry, sapces were not being displayed so i put [tab] in to show where the spaces are in the file line.)
306Staffa [Tab] FEB/16/10 [tab] 16.26 [tab] 33.02 [tab] 0.20 [tab] F [tab] A [tab] 100


Where it is tab delimeted. Do i read this into an array? or DAO recordset (which i'm also new too)
Is there an easy way to split this line up or do i have to use the left mid and right methods to hunt out the values i want?

 

[vbaInet]

You Split() the string using vbTab as your delimiter and save it into an array.

Dim myArray() as String

myArray = Split(recordFromInput, vbTab)

myArray will now contain the fields in each position of the array.

 

[SOS]

Tab, comma, it doesn't really matter which. You can bring it in using many methods and if you use the line by line method of Open X As #1 method like I stated before you could parse it like:

Dim strHold As String
Dim varSplit As String
Dim rst As DAO.Database
 
Set rst = CurrentDb.OpenRecordset("TableNameHere")
 
Open "C:\Test.txt" For Input As #1
 
Do Until EOF(1)
LineInput #1, strHold
 
varSplit=Split(strHold, Chr(9))
 
rst.AddNew
rst(0).Value = varSplit(0)
rst(1).Value = varSplit(1)
rst(2).Value = varSplit(2)
'...etc.
rst.Update
 
Loop
 
Close #1
rst.Close
Set rst = Nothing

 

[Access_guy49]

OK new question, what if it turns out those "tabs" are actually spaces, (usually 5, but it seems between the sitename and the date it is more like 10) any ideas how to split this out? i'm not sure what to do with it.

 

[SOS]

Have you tried the split code to see what it does on the actual data? I would do that before anything.

 

[vbaInet]

Also, try copying and pasting a sample into Excel just to see how many columns you get in between. You could programatically test for vbTab as well using InStr() counting the number of occurences

 

[Access_guy49]

I did try the code as it was, and it would just return the entire line. So u used the replace fucntion and just replaced spaces with vbtab. it works now! Thank you!!!!!!!

 

[SOS]

Cool, glad you got things working. Good thing we have many people on the forum as each contributes a piece or part (sometimes the whole) and that can help everyone out. Continued luck on the project.

 

[vbaInet]

Great job Access_guy49. Glad to have had some input.

 

[SOS]

Great job Access_guy49. Glad to have had some input.

And glad to have you here vbaInet. You're on fire here.

 

[vbaInet]

And glad to have you here vbaInet. You're on fire here.

Thanks for the kind gesture.

Hehe! I'm following your lead SOS.

 

[Access_guy49]

Tab, comma, it doesn't really matter which. You can bring it in using many methods and if you use the line by line method of Open X As #1 method like I stated before you could parse it like:

Dim strHold As String
Dim varSplit As String
Dim rst As DAO.Database
 
Set rst = CurrentDb.OpenRecordset("TableNameHere")
 
Open "C:\Test.txt" For Input As #1
 
Do Until EOF(1)
LineInput #1, strHold
 
varSplit=Split(strHold, Chr(9))
 
rst.AddNew
rst(0).Value = varSplit(0)
rst(1).Value = varSplit(1)
rst(2).Value = varSplit(2)
'...etc.
rst.Update
 
Loop
 
Close #1
rst.Close
Set rst = Nothing

I get an error at rst.addnew
Method or datamember not found.

Any ideas? I've looked on every website i can think of and they all show the same thing, looks just like this.

 

[vbaInet]

Little typo there.

Dim rst as Dao.Database

Dim rst as Dao.Recordset

rst should be a recordset.