Import Specific lines from Text File

[geoffcodd]

Hi there,

I have a text file which I need to import the 28th, 30th, 78th and 80th line from.

Can this be done?

Thanks
Geoff

 

[fuzzygeek]

Possibly

Yes, but not as straight forward as you might like.
Using the filesystemobject and textstream object, you could set up an automation that reads the file and copies the lines you indicated into a new text file which, in turn, you could then import the data. Or you could automate the process to make a copy of the file and then delete all but those lines that you indicated. Then import the data from that file.

 

[modest]

Or if the data holds the same format for each line... copy it in to a temp table and then pick the ones you want. This is faster cuz access loads tables in memory when you run queries on it. Memory is faster than hard drive speed.

 

[WayneRyan]

geoff,

Simplistic view of it ...

Dim i As Long
Dim buffer As String

Open "C:\SomeFile.txt" For Input As #1


Line Input #1, buffer
i = 1
While Not EOF(1)
   If i = 28 Or i = 30 or i = 78 or i = 80 Then
      DoCmd.RunSQL "Insert Into YourTable (Values ...)"
   End If
   Line Input #1, buffer
   i = i + 1
   Wend
Close #1

Wayne

 

[Happy YN]

If you could construct part of the text file as an ini file you could retrieve the item using profileGetItem (do a search on this forum)

 

[modest]

I like Wayne's answer best. Here's his a little more fine tuned.

    Dim i As Long
    Dim buffer As String
    Dim strSQL As String
    
    Open "C:\Documents and Settings\Windows Profile\Desktop\File Name.txt" For Input As #1
    
    Line Input #1, buffer
    i = 1
    While Not EOF(1)
        
        If i = 28 Or i = 30 Or i = 78 Or i = 80 Then
            DoCmd.SetWarnings False
            strSQL = "INSERT INTO [Table Name] ([Column Name]) VALUES ('" & buffer & "')"
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
        End If
        
        Line Input #1, buffer
        i = i + 1
    Wend
    
    Close #1