Import text file to MS Access - Skip first 11 lines

[tjuiowa]

Hi

I am trying to import a text file into a MS Access database. The first 11 lines of the text file are in a different format, so i would like to skip those lines and start at line 12. is there a quick and easy way to skip lines using VB in Access. Here is a sample of my text file:

[FILE]

SDS 2.2 AD Results 1.0
Filename
PlateID
Assay Type
Run DateTime
Operator

Sample Information
Marker Setting Information
Marker Name Quality Value Threshold

Well Sample Name Marker Name Allele X Rn Allele Y Rn
1 snp IRF6-SNP13 0.313 0.700
2 snp IRF6-SNP13 0.507 0.634
3 snp IRF6-SNP13 0.434 0.741
[/FILE]


Any help on skipping to line 12 would be greatly appreciated. Thanks in advance

TJ

[kepston]

You can skip the first 11 lines using the following code to generate a temporary file, then import from that file. I have tested the code with your sample (with the exception of the actual import - DoCmd.TransferText)

    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
    Dim ft, fs, t, s, temp, sample
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set ft = CreateObject("Scripting.FilesystemObject")
    fs.opentextfile "sample.txt" 'your text file
    ft.createtextfile "temp.txt" 'create a temporary file
    Set s = fs.getfile("sample.txt")
    Set t = fs.getfile("temp.txt")
    Set temp = t.OpenAsTextStream(ForWriting, TristateUseDefault)
    Set sample = s.OpenAsTextStream(ForReading, TristateUseDefault)
 
    For x = 1 To 11  'read first 11 lines
        sample.readline
    Next x
 
    Do While Not sample.atendofstream    'read remainder of file until end
        temp.writeline (sample.readline) 'write a line at a time
    Loop
 
    temp.Close
    sample.Close
 
    DoCmd.TransferText acImportFixed, _
                        "Your import spec", _
                        "your table name", _
                        "temp.txt", _
                        True
 
 

See my previous post for selecting a file using a dialog box

[tjuiowa]

Thank you so much...this was a big help!

You can skip the first 11 lines using the following code to generate a temporary file, then import from that file. I have tested the code with your sample (with the exception of the actual import - DoCmd.TransferText)

    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
    Dim ft, fs, t, s, temp, sample
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set ft = CreateObject("Scripting.FilesystemObject")
    fs.opentextfile "sample.txt" 'your text file
    ft.createtextfile "temp.txt" 'create a temporary file
    Set s = fs.getfile("sample.txt")
    Set t = fs.getfile("temp.txt")
    Set temp = t.OpenAsTextStream(ForWriting, TristateUseDefault)
    Set sample = s.OpenAsTextStream(ForReading, TristateUseDefault)
 
    For x = 1 To 11  'read first 11 lines
        sample.readline
    Next x
 
    Do While Not sample.atendofstream    'read remainder of file until end
        temp.writeline (sample.readline) 'write a line at a time
    Loop
 
    temp.Close
    sample.Close
 
    DoCmd.TransferText acImportFixed, _
                        "Your import spec", _
                        "your table name", _
                        "temp.txt", _
                        True
 
 

See my previous post for selecting a file using a dialog box

[PappyBlueRibs]

Wow, this is so useful! I've got about 15 data files that I'm using it with, no problem at all. Thank you!