Import txt files

[hgus393]

Hi all,
This is my first post in this forum. I am a avid excel user but I am realizing the power of access. I have tried to find a thread that matches what I am looking for but to no avail. Basically what I am trying to do is to import several text files, via vba, that have headers and lines in the beginning. I have looked at some previous code example and found the following:
I not sure if I have to use any code tags so please excuse my ignorance..

Sub help()
Dim s As String
Dim rs As Recordset
Dim path As String
Set rs = CurrentDb.OpenRecordset("FxRates")
path = "C:temp\file.txt"

Close #1
Open path For Input Lock Read As #1
Do While Not EOF(1)
Line Input #1, s
rs.AddNew
rs!Currency = Mid(s, 1, 3)
rs!CurrencyRates = CDbl(Mid(s, 4, 9))
rs.Update
Loop

Close #1
End sub

How can I force the macro to start on say line 8 instead of beginning on line 1?
Thankful for any help!
Cheers
Robert

 

[namliam]

First, welcome to the forum.

Next if you post code please use [ code] and [ /code] around the code (without the spaces) this way the indentation (which I hope you are doing) is preserved on the forum

Then, please disambiguate.
Dim rs As Recordset
Should read... DAO.Recordset or ADO.Recordset...

On your problem, check out the "Docmd.TransferText" command in the access help for importing text files... this is much easier.

As for your "real" question, just load in the number of lines before you actually start...
i.e.
Open path For Input Lock Read As #1
Line Input #1, s
Line Input #1, s
Do While Not EOF(1)
Line Input #1, s
....

To skip the first 3 lines. But really check out the Docmd, you should use that instead!

 

[hgus393]

Thank you Namliam for your reply,
Will do the code tags next time and disambiguate! I have looked at the transfer spreadsheet command, however if I want to skip the first rows with headers and stuff then I don't see any other way than below....
Cheers
Robert

First, welcome to the forum.

Next if you post code please use [ code] and [ /code] around the code (without the spaces) this way the indentation (which I hope you are doing) is preserved on the forum

Then, please disambiguate.
Dim rs As Recordset
Should read... DAO.Recordset or ADO.Recordset...

On your problem, check out the "Docmd.TransferText" command in the access help for importing text files... this is much easier.

As for your "real" question, just load in the number of lines before you actually start...
i.e.
Open path For Input Lock Read As #1
Line Input #1, s
Line Input #1, s
Do While Not EOF(1)
Line Input #1, s
....

To skip the first 3 lines. But really check out the Docmd, you should use that instead!

 

[namliam]

Still transferTEXT will load the file fast and easy, you can then delete (again fast and easy) any extra lines you do not want.

Offcourse this manual work around will work as well, but if we are talking good numbers, this will be considerably slower than the TransferText method

 

[DCrake]

Another workaround that involves an additional step is a combination of both Open file for Input and TransferText

Use the Open file For input first

Dim nIndex As Integer
Open X For Input As #1
Open Y For Output As #2
Do While Not EOF(1)
     Line Input #1, s   
     If nIndex > 8 Then 'Skip first 8 lines (Header)
        Print #2, s
     Endif
     nIndex = nIndex +1
Loop
Close #1
Close #2

David


Then use the TransferText command on file Y (This does not contain any header information about the file

 

[hgus393]

Yes I agree it is extremly much faster and easier. Can I in code remove the first 8 rows in an access table?
//Robert

Still transferTEXT will load the file fast and easy, you can then delete (again fast and easy) any extra lines you do not want.

Offcourse this manual work around will work as well, but if we are talking good numbers, this will be considerably slower than the TransferText method

 

[namliam]

Depends, how can you identify the first 8 lines? Is there someway to do that? Or do you just need to skip the "first 8" ??

I will probably come down to a delete query beeing run or something.

 

[hgus393]

I have come to the realisation that using transfertext + delete query is optimal (at least very much quicker). Thank you Namliam for all your help!!
//Robert

Depends, how can you identify the first 8 lines? Is there someway to do that? Or do you just need to skip the "first 8" ??

I will probably come down to a delete query beeing run or something.