Importing and Formating Large Text File - Really Stuck!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a text file that is about 7Mb ish that contains over 100000 lines of
data
Each line has a Date and Time Stamp followed by the text

10/11/2005 00:00:01 anytext of no fixed length and can be upwards of 500
characters including multiple carriage returns.

There is no end of line character or delimiter that iendtifies when this
text finishes

I am seeking a way to import this data into an Access table in order that I
can interrogate the text using a Form or other method yet to be decided,
however need it into an access table first

Any pointers would be greatly appreciated

MAny thanks
 
Alan said:
I have a text file that is about 7Mb ish that contains over 100000
lines of data
Each line has a Date and Time Stamp followed by the text

10/11/2005 00:00:01 anytext of no fixed length and can be upwards of
500 characters including multiple carriage returns.

There is no end of line character or delimiter that iendtifies when
this text finishes
Click to expand...

So when you say "lines", you really mean "logical lines", because the
standard line-end character combination CR+LF doesn't actually delimit
the "lines" you're talking about.
I am seeking a way to import this data into an Access table in order
that I can interrogate the text using a Form or other method yet to
be decided, however need it into an access table first

Any pointers would be greatly appreciated
Click to expand...

There's really nothing to separate one "logical line" from another?
From what you say, I think you'll have to read the entire file contents
into a string variable, and then scan through them looking for character
patterns that look like a date-time specification.

Reading the whole file into a string is easy:

Dim strFileText As String
Dim intFile As Integer

intFile = FreeFile()

Open "YourFilePath" For Input As #intFile
strFileText = Input(LOF(intFile), intFile)
Close #intFile

7MB is not too big to handle this way.

Parsing strFileText into logical lines once you've got it is another
matter. You could probably do something like this:

'----- start of rough "air code" -----
Dim strLine As String
Dim lngCurrLine As Long
Dim lngNextLine As Long

lngCurrLine = 1

' ... (code here to make sure this position is a date-time spec) ...

Do Until lngCurrLine > Len(strFileText)

' Start searching after the current line's timestamp
For lngNextLine = (lngCurrLine + 19) To Len(strFileText)
If Mid$(strFileText, lngNextLine, 19) Like "##/##/####
##:##:##" _
Then
Exit For
End If
Next lngNextLine

strLine = Mid$(strFileText, lngCurrLine, lngNextLine -
lngCurrLine)

' ... (here's where you write this line into a table, probably
via
' a recordset you previously opened) ...

lngCurrLine = lngNextLine

Loop
'----- end of rough "air code" -----

Or you might use a Regular Expression object, but I'm not so familiar
with those.
 
Assuming that nothing else works, you can always fall back to the 'Line
Input #' statement:

Dim TextLine

Open "TESTFILE.txt" For Input As #1 ' Open file.

Do While Not EOF(1) ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
Debug.Print TextLine ' Print to the Immediate window.
Loop
Close #1 ' Close file.
 
Steve.... Many Thanks
However if possible would like to have it formatted in a table with

Field 1 ... Date
Field 2 ... Time
Field 3 ... Text

Is this a viable option ???

Again thanks for your help
 
yupp,

use the cvdate function on the first 19 number of letters of a line (read
into variable)
if valid date save previous data to table
else concat line to data variable

pointers:
use recordset & appendchunk

good luck

Pieter
 
There is also a plain INPUT # statement, but it sounded like your data was
not organized enough to use.
 
Back
Top