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.

  • 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:…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)
    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.


    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!