Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Importing and Formating Large Text File - Really Stuck!

Discussion in 'Information Technology' started by Alan, Nov 11, 2005.

  1. Alan

    Alan
    Expand Collapse
    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
     
  2. [MVP] S.Clark

    [MVP] S.Clark
    Expand Collapse
    Guest

    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 Clark, Access MVP
    http://www.fmsinc.com/consulting
    *FREE* Access Tips: http://www.fmsinc.com/free/tips.html

    "Alan" <Alan@discussions.microsoft.com> wrote in message
    news:A1B43256-34BE-4956-9B1A-3C5D9CA1D9EB@microsoft.com...
    >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
    >
    >
     
  3. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Alan" <Alan@discussions.microsoft.com> wrote in message
    news:A1B43256-34BE-4956-9B1A-3C5D9CA1D9EB@microsoft.com
    > 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


    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


    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.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  4. Alan

    Alan
    Expand Collapse
    Guest

    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

    "[MVP] S.Clark" wrote:

    > 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 Clark, Access MVP
    > http://www.fmsinc.com/consulting
    > *FREE* Access Tips: http://www.fmsinc.com/free/tips.html
    >
    > "Alan" <Alan@discussions.microsoft.com> wrote in message
    > news:A1B43256-34BE-4956-9B1A-3C5D9CA1D9EB@microsoft.com...
    > >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
    > >
    > >

    >
    >
    >
     
  5. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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


    "Alan" <Alan@discussions.microsoft.com> wrote in message
    news:705F46E3-1AB9-47F9-8FBF-A27502F549D1@microsoft.com...
    > 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
    >
    > "[MVP] S.Clark" wrote:
    >
    >> 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 Clark, Access MVP
    >> http://www.fmsinc.com/consulting
    >> *FREE* Access Tips: http://www.fmsinc.com/free/tips.html
    >>
    >> "Alan" <Alan@discussions.microsoft.com> wrote in message
    >> news:A1B43256-34BE-4956-9B1A-3C5D9CA1D9EB@microsoft.com...
    >> >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
    >> >
    >> >

    >>
    >>
    >>
     
  6. [MVP] S.Clark

    [MVP] S.Clark
    Expand Collapse
    Guest

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

    "Alan" <Alan@discussions.microsoft.com> wrote in message
    news:705F46E3-1AB9-47F9-8FBF-A27502F549D1@microsoft.com...
    > 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
    >
    > "[MVP] S.Clark" wrote:
    >
    >> 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 Clark, Access MVP
    >> http://www.fmsinc.com/consulting
    >> *FREE* Access Tips: http://www.fmsinc.com/free/tips.html
    >>
    >> "Alan" <Alan@discussions.microsoft.com> wrote in message
    >> news:A1B43256-34BE-4956-9B1A-3C5D9CA1D9EB@microsoft.com...
    >> >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
    >> >
    >> >

    >>
    >>
    >>
     

Share This Page