How do I convert a messy .txt file to excel?

[Juanp89]

Hello,

I have a very messy .txt report that I need to convert data into an excel spreadsheet in order to manipulate the data and interpret accurately. The .txt report has multiple repeating headers, spaces with dash lines etc. I have tried multiple times to convert the data to excel and I have not been successful. Can someone please help me by providing steps on what I need to do to convert this data into a readable excel spreadsheet?

The first attachment (image 001) is a screenshot of what the .txt data looks like. This is what I am trying to concert into excel.

I appreciate any help you can provide!

 

[Joe4]

Welcome to the Board!

In a former life, I used to have to work with data like this. In some cases, I was able to get them to send the data in a different format that is easier to work with, instead of a "printed" report, which is a HUGE pain to work with. But in other cases, I was not, and was stuck working with it.

It was a very long and slow process to work through. Basically, I would bring it into Excel in multiple rows but all data in column A (no field splits initially).
Then I would loop through each row, one line at a time, and decide what to do with each one, based on the prefix (i.e. what is in the first 10 characters of the cell), or heading in the row above.
It took quite a long to develop, and is probably not feasible to do if you are not experienced in writing VBA code in Excel.

If you are looking to have someone do it for you, this is probably more than can be expected from free Excel help. The focus of this forum is to help you do it yourself, or answer very pointed specific questions. The amount of work for someone to do the whole thing for you would probably fall into a Consultant project categorization. There are consulting services provided here: Consulting Services, if you are interested in having someone do this for you.

Note that whoever helps you will want to also see an example of what you want your expected output to look like, based on the sample you provided, as there is a lot of header and trailer information, and we cannot possibly know what you want to see and in what format (people need to know what your expected output looks like so they know what they are building!).

 

[Micron]

I took it that the data was a text file but I guess not. What about creating a file with OCR (optical character recognition) scanning? Then if the OP has the time to research (i.e. look for code that does certain things) maybe *Juan89 could parse the lines into a sheet using code. I imagine that is what a consultant would do. Then again, if the creator can print it, it's probably a text file already. It looks like it would be tab delimited.

 

[Joe4]

Me too (in regards to "I took it that the data was a text file..."). I used to work with very similar data years ago. It is printed reports saved/exported as txt files.
So I guess "space-delimited" text file is probably the best explanation of it.
But there are various different formats/layouts depending on while line you are on, due to headers, footers, groupings, blank lines, other characters, etc.
It really is a bear to work with...

 

[Artik]

The text file shown in the image does not look like some very nasty one although it will need some work. You can see that the data is collected in two sets of columns.
The first set includes rows starting with a string of spaces and “ITEM”. The second set includes rows starting with a string of spaces and “ACTION DATE” and subsequent rows starting with a string of spaces and a date.
Special treatment is given to data starting with the line “MR045”.... and ending with a series of dashes across the page.
In my opinion, the task should be done in several stages.
The first stage is to retrieve the contents of the file into an array, for example, using code like this:

Dim fso As Object
Dim file As Object
Dim strFileContent As String
Dim varrFileContent As Variant

Set fso = CreateObject("Scripting.FileSystemObject")
Set file = fso.OpenTextFile(strFilename, 1) ' 1 = ForReading

strFileContent = file.ReadAll
file.Close

varrFileContent = Split(strFileContent, vbCr) ' examine how the line ends and apply the appropriate separator (vbCr, vbCrLf, vbLf)

Then analyze each row and separate the read data into two text files. To one, the first set of columns, and to the other - the second set. Add a few spaces and a line number to each of the read rows at the end.
Something I will call the page header (lines from “MR045” to the line with dashes) perhaps should be saved to the third file, not forgetting to add the line number at the end.
When we finish analyzing the lines, we close the resulting two (or three) new files. In the next step, we import the files into a single sheet using the import wizard and choosing a fixed column width (in everyday life we more often use the first option - Separated, but not this time). If we set the appropriate number of spaces before the row number when separating the rows to the corresponding files, we can get the row numbers in one column after import. Now all we need to do is sort the data against the column containing the row numbers and we should get data similar to the original file, but each column will already have a fixed location.
And it seems that you can start working with the data properly.

Artik

 

[Juanp89]

Hi all,

Im not too experienced with macros or power query in order to do this. Can someone please provide the step by step process on how to convert this.txt data to excel?

I appreciate any help you can provide!

 

[Artik]

Im not too experienced with macros or power query in order to do this.

I think post #2, especially its second half is what you are looking for.

Artik

 

[Joe4]

As we said, the purpose of this board is not to do consulting work for free, but rather to offer suggestions and answer pointed, specific questions.
Your task is not a trivial one that will only take a few minutes, but quite an involved one that will probably take a considerable amount of time to develop.
If you do not have much experience with Macros or Power Query, I am afraid that you will probably find this task a bit daunting to do yourself.
If you are looking for someone to do this for you, you will want to enlist the help of Consulting Services (either the ones on this board or elsewhere).