Obtaining data from text files

  • Thread starter Thread starter Sean Bartleet
  • Start date Start date
S

Sean Bartleet

Hi,

I have 100 text files all with the same format in the header part. I wish to
extract the 9th line form each file and get the data on this line into a
spreadsheet.

Any ideas will be appreciated.

Thanks.

Sean Bartleet
 
Sean,

This is quite easy to do with a bit of VB code. You would need to provide a
couple of sample files, or at least a few 9th lines (copy and paste from the
files). Also:
* Are all the files in the same folder?
* Do the files follow a certain naming convention?
* Is this a one-off tack, or will it be repeated? In the latter case:
- will the folder holding the files be constant?
- when new files come, will the old ones be deleted / overwritten, or
will they still be there?

Nikos
 
Hi,

You need to do some data streaming. As Nikos says, it's
important in this case that the files you're importing
follow the same naming convention & are preferably in the
same directory.

quick explanation.
I do this sort of this by setting up a list of file names
in a column in excel. Set up a loop in the VB code to
open each file as a Filehandle. Have an loop within that
loop opening a new line of the file you're importing from
until it reaches the end of the file. In your case, have
a variable counting up from 0 upwards at the start of
that 2nd loop. When that variant = 9, you're looking at
the 9th line and you can return the line of data in the
file as a string.



example code: This is dramatically reduced from something
I'm working on now. it says I have 10 filenames in cells
A1:a10 on a spreadsheet, put the 9th line of each file in
cells b1:b10.

----------------------------------------------------------
Option Explicit

dim varFile_count, VarLine as variant
dim StrData, openfile as string


do ' this loop is for each of 10 file names
in column a on my spreadsheet

varFile_count = varFile_Count + 1 ' the count
of files. Increments up by one for each file

OpenFile = "\\BLACKBOX\SSTEM\" & Cells
(VarFile_count + 2, 1) & ".TXT" ' select text file name
from column A on my spreadsheet

Filehandle = FreeFile() 'assign filehandle

Open OpenFile For Input As #Filehandle ' open a
new filehandle for each site loop

'var_line should be set to 0

Do 'a loop for the the Individual files
Line Input #Filehandle, StrData 'input each
line in the file as a string of data

Var_line = Var_Line + 1 ' count of lines down
in an RPV

If Var_Line = 9 Then ' test the line


cells(varfile_count,1).value =
StrData 'this would put the complete 9th line of
your file

'in cells b1 to b10 on your active worksheet -
using the file count to determine position in the sheet
cells(varfile_count,1).value = mid(strdata,
1,4) ' example, you can use MID to pull out specific bits
from the 9th line if you want

End If

Loop Until EOF(1) ' loops through RPV till the end


loop until varFile_count = 10
 
Hi,

Cool, thanks for the code, I will try it over the weekend.

Sean

Steven D said:
Hi,

You need to do some data streaming. As Nikos says, it's
important in this case that the files you're importing
follow the same naming convention & are preferably in the
same directory.
Click to expand...
....
 
Back
Top