By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,968 Members | 1,871 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,968 IT Pros & Developers. It's quick & easy.

Import text file to MS Access - Skip first 11 lines

P: 4
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
May 14 '07 #1
Share this Question
Share on Google+
3 Replies


Expert
P: 97
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)

Expand|Select|Wrap|Line Numbers
  1.     Const ForReading = 1, ForWriting = 2, ForAppending = 3
  2.     Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
  3.     Dim ft, fs, t, s, temp, sample
  4.     Set fs = CreateObject("Scripting.FileSystemObject")
  5.     Set ft = CreateObject("Scripting.FilesystemObject")
  6.     fs.opentextfile "sample.txt" 'your text file
  7.     ft.createtextfile "temp.txt" 'create a temporary file
  8.     Set s = fs.getfile("sample.txt")
  9.     Set t = fs.getfile("temp.txt")
  10.     Set temp = t.OpenAsTextStream(ForWriting, TristateUseDefault)
  11.     Set sample = s.OpenAsTextStream(ForReading, TristateUseDefault)
  12.  
  13.     For x = 1 To 11  'read first 11 lines
  14.         sample.readline
  15.     Next x
  16.  
  17.     Do While Not sample.atendofstream    'read remainder of file until end
  18.         temp.writeline (sample.readline) 'write a line at a time
  19.     Loop
  20.  
  21.     temp.Close
  22.     sample.Close
  23.  
  24.     DoCmd.TransferText acImportFixed, _
  25.                         "Your import spec", _
  26.                         "your table name", _
  27.                         "temp.txt", _
  28.                         True
  29.  
  30.  
See my previous post for selecting a file using a dialog box
May 15 '07 #2

P: 4
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)

Expand|Select|Wrap|Line Numbers
  1.     Const ForReading = 1, ForWriting = 2, ForAppending = 3
  2.     Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
  3.     Dim ft, fs, t, s, temp, sample
  4.     Set fs = CreateObject("Scripting.FileSystemObject")
  5.     Set ft = CreateObject("Scripting.FilesystemObject")
  6.     fs.opentextfile "sample.txt" 'your text file
  7.     ft.createtextfile "temp.txt" 'create a temporary file
  8.     Set s = fs.getfile("sample.txt")
  9.     Set t = fs.getfile("temp.txt")
  10.     Set temp = t.OpenAsTextStream(ForWriting, TristateUseDefault)
  11.     Set sample = s.OpenAsTextStream(ForReading, TristateUseDefault)
  12.  
  13.     For x = 1 To 11  'read first 11 lines
  14.         sample.readline
  15.     Next x
  16.  
  17.     Do While Not sample.atendofstream    'read remainder of file until end
  18.         temp.writeline (sample.readline) 'write a line at a time
  19.     Loop
  20.  
  21.     temp.Close
  22.     sample.Close
  23.  
  24.     DoCmd.TransferText acImportFixed, _
  25.                         "Your import spec", _
  26.                         "your table name", _
  27.                         "temp.txt", _
  28.                         True
  29.  
  30.  
See my previous post for selecting a file using a dialog box
Aug 17 '07 #3

P: 1
Wow, this is so useful! I've got about 15 data files that I'm using it with, no problem at all. Thank you!
Feb 20 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.