Importing Text without Delimiters.

    I receive a text file each day that has no delimiters separating the records. As far as I can tell, though, each record ends with "CURRENT TIME ##:##:##"



    What I would like to do is import this text file into Excel each day and separate the records into rows and columns. I have tried using text to columns, but it
    does not work that well because there are no delimiters, so I end up having to try to line up the columns manually. If I could just get the
    file into Excel with one record per row and the data in columns, that would save so much time for me. Any idea as to how I can do that?



    I have attached a sample of what the file looks like. Any help is greatly appreciated.
    forum.ozgrid.com/index.php?attachment/71118/

    Re: Importing Text without Delimiters.


    Based on the test file you provided it looks like there are 3 'logical' records in this file (ending in "CURRENT TIME hh:mm:ss")


    One way to read the text file is to use the FileSystemObject and read the file as a text stream, parsing the data as you go.


    Based on that method, attached is one way to read the sample data you provided into 3 logical rows of data in a new workbook.
    NOTE: You will need to add a reference in the VBA project to Microsoft Scripting Runtime before you can run this demo.
    You can find out how to do this easily enough, here is one reference:
    http://stackoverflow.com/quest…e-filesystemobject-in-vba


    This piece of sample code has some limited error handling and also assumes that the very last bit of text in any input file will contain "CURRENT TIME hh:mm:ss" (as the end of the last logical record)
    forum.ozgrid.com/index.php?attachment/71123/
    This doesn't put your data into separate columns as you still need to figure out how to do that bit based on what the record structure is.

    Re: Importing Text without Delimiters.


    gijsmo,


    Thank you for your response. I checked out the file you attached, and it works just fine. It will definitely save me a lot of time. I just need to figure out to separate the data into columns now. Thanks again. :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login