complicated text-to-columns macro?

[Tekture]

I'm using an old AS400 mainframe system here. I can export a report that i want into a txt file and then import to excel but i'm running into problems when i try to separate all this data. Unfortunatly when the report exports to txt file, it does not keep the column widths as seen in the mainframe. When i load to excel either by opening and doing text to columns there, or by cutting and pasting and then doing text to columns, i get the same problem. I can't use fixed width because of random column widths, and i can't use delimited because each one is different. (IE: a description could have 5 spaces or none)

The good news, i am hoping, is i can use a flag of sorts that each Item section has (item section is about 7 columns by 5 rows, then a space to separate the next item section, and so on, up to over 3000)
In the 2nd line, first row of one item section, it will always say "Level:"

Does anyone know a way to get text to columns to work OR Will i be able to make a macro that can somehow find that flag ("level:") and use it to find my data and do something with it. I'm thinking a macro should be possible, but i haven't a clue where to begin.

I can upload an example if someone needs a visual.

 

[Tekture]

I was able to do all 3000 lines today by hand (and a few shortcuts) but i would still like to know if this concept is possible. After thinking about it some more, maybe this is a better way to explain: Every other row is off by half, so row 1, 3, 5, 7, etc... are all lined up, and the rest 2,4,6, etc are all lined up, but do not match the first set. If you did text to columns, one of those sets of cells will be cut in half in order to make the other one line up.

It's not quite as simple as that because there are multiple rows, but that's the gist of it.

 

[Brianwarnock]

A sample would be interesting, not sure when I'll get to it but you never know.

Brian

 

[Tekture]

Example is attached.

 

[Paulch]

Hi There

Perhaps I'm missing the point, but can't you use the space between the data as the delimiter? I just tried it on the sample supplied, and apart from the formatting, it is just about there.

Hope this helps.

Paul

 

[Tekture]

Well, that's how I did it, but had to go back through and do a lot of things by hand. It's not obvious in the example, but there are places that are blank and that will throw that column off. The item number sometimes has a D, sometimes not. With 3,000 lines, this took me half a day to get it all together.

I was just hoping there was an easier way.

 

[boblarson]

I was just hoping there was an easier way.

With inconsistent data it is virtually impossible to automate it all.

 

[Paulch]

Hi there. Sorry for the delay - as usual trying to do too many things at once.

As Bob says, if the data is inconsistent, then automation is impossible. However, from the small sample you gave, and from your second comment, it would seem that there are repeating patterns within the data which would lend themselves to multiple blocks of automation. The problem would be to identify the trigger for each block - an exercise in logic, really. There is probably a lot of work involved in identifying the patterns - the code loops would be relatively straightforward. The only question really is - is it worth it? How often do you have to do this task, monthly? How much time would you ultimately save?

If you think it's worth doing, put a copy of the full 3000 line txt file up, and I'm sure we can come up with something!

Cheers

Paul

 

[Brianwarnock]

To attempt it or not does depend on how frequently this is needed and the time constraints etc. It looks fairly straightforeward, but as the data supplied is not the whole story, eg where can there be blanks, who can really say.

Brian

 

[Tekture]

Well, this report is ran once a month. My side of the work load is only a 3,000 item section. The entire inventory is some 20,000 items long. If i could automate it, I can pass it along to others. Currently, we just look at everything the way it comes out on a txt report.

The months section will always show a zero, so there's no problem with inconsistent data there. All I really need would be the item number and the month section. The item number will always be greater than or equal to 4 digits and i'm pretty sure there are no 4 digit numbers listed on that line (description or otherwise) But there very well could be a 4 digit num on the next few lines in the price col.

I had originally thought up something like this:
Find "JAN", go up one line, find the first number > 4 and delete everything after it. Then just delete the last line (go down to 6th line and delete it all) Once you get here, just run a normal txt to columns.

Thanks to each of you for your time and help.

 

[namliam]

Your problem - I am glad to say - doesnt exist... Or I think it doesnt.

Your problem is that it LOOKS like the format is not kept, however this is only the difference between a TRUETYPE font and a FIXEDTYPE font.
Truetype will take less space for an I and more space for a W, while Fixed displays same space for all characters. If you change your font to Courier new your fixed format "returns" -- well atleast for the months part --

The 'inconsistant spacing' is where TRIM really shines, as it will remove any duplicate spacing... 3 formula's brought together will solve this for you.
In B2: =FIND(" ",TRIM(A2),FIND(" ",TRIM(A2),FIND(" ",TRIM(A2),1+1)+1))
In C2: =FIND(" ", TRIM(A2),B2+1)
In D2: =MID(TRIM(A2),B2+1,C2-1-B2)

It can also be done in one formula... something like:

=MID(TRIM(A2),FIND(" ",TRIM(A2),FIND(" ",TRIM(A2),FIND(" ",TRIM(A2),1+1)+1))+1,FIND(" ", TRIM(A2),FIND(" ",TRIM(A2),FIND(" ",TRIM(A2),FIND(" ",TRIM(A2),1+1)+1))+1)-1-FIND(" ",TRIM(A2),FIND(" ",TRIM(A2),FIND(" ",TRIM(A2),1+1)+1)))

Does the same


Pulling the values per month can either be done simularly or with simple MID functions
For the value of January
i.e. =VALUE(MID(A4,10,7))
and. =VALUE(MID(A4,17,10))
I would then expand those to pick up the values I want in columns per month and add a column in front:
=LEFT(A2,7)
Then filter on this column, and only show those containing "Device " ...

Presto done, standard report mining I love it

 

[Tekture]

Wow, I certaintly learned a lot with that post namliam. After messing around with it, your first set of formulas assume that there will only be 1 space located in the description line (Device 1) but this is not always the case. For ex, this is one description line: "Gloves, leather, Size 6".

The text to columns will work with the months section becuase there should always be a place holder there (0).

I'll have to remember those functions for next time becuase data minning seems to be a regular need for me.

 

[Brianwarnock]

Wow, I certaintly learned a lot with that post namliam. After messing around with it, your first set of formulas assume that there will only be 1 space located in the description line (Device 1) but this is not always the case. For ex, this is one description line: "Gloves, leather, Size 6".

.

How do you expect to get sensible help when your example and original info is so lacking.

Brian

 

[namliam]

OK, So how about something along these lines...
I made a start for it up to you to finish it

Sub reFormat()
    Dim LastRow As Integer
    Dim I As Integer
    Dim ThisString As String
    Range("A65000").End(xlUp).Select
    LastRow = ActiveCell.Row
    Range("A1").Select
    For I = 1 To LastRow
        Range("A" & I).Select
        Select Case Left(Range("A" & I), 7)
            Case "    NOR"
                ThisString = Range("A" & I - 1)
                Sheets("Sheet3").Activate
                ActiveCell.Value = fnGetFirst5Numbers(ThisString)
                ActiveCell.Cells(1, 2) = fnGetName(ThisString)
                ActiveCell.Cells(2, 1).Select
                Sheets("Import").Activate
            Case "    JAN"
            Case "    APR"
            Case "    JUL"
            Case "    OCT"
        End Select
    Next I
        
    
End Sub

Function fnGetFirst5Numbers(ThisString As String) As Integer
    Dim Y As Integer
    For Y = 1 To Len(ThisString)
        If Mid(ThisString, Y) Like "#####*" Then
            fnGetFirst5Numbers = Mid(ThisString, Y, 5)
            Exit Function
        End If
    Next Y
End Function

Function fnGetName(ThisString As String) As String
    fnGetName = Trim(Left(ThisString, InStr(1, ThisString, fnGetFirst5Numbers(ThisString)) - 1))
End Function