Is it possible to move one row to multiple columns?

[cloobless]

Hi, I've been trying to get a solution for what I thought would be simple. I have a very large flat text file of thousands of records. It's all in a single linear format with line breaks for each line of data. The data is grouped in thousands of groups in exactly the same way. I'm trying to automatically parse the data so that each group is parsed into separate columns. The data in the flat file looks like the following (except there are thousands of groups, not three...):

{note that the first three lines are always the same and there is never a change in their frequency}
{note that the "sender" lines are variable; sometimes there's none, sometimes five senders, sometimes two, etc.}
{note the line break after the final sender and the next set of data}

---
Existing:

DATEdata
SUBJECTdata
Locationdata
sender--1
sender--2
sender--3

DATEdata
SUBJECTdata
Locationdata
sender--1
sender--2
sender--3
sender--4

DATEdata
SUBJECTdata
Locationdata
sender--1


Possible?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Subject[/TD]
[TD]Location[/TD]
[TD]Sender[/TD]
[/TR]
[TR]
[TD]2010-11-05[/TD]
[TD]recess[/TD]
[TD]c:[/TD]
[TD]sender--1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sender--2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sender--3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2010-11-06[/TD]
[TD]oranges[/TD]
[TD]c:\\oranges[/TD]
[TD]sender--1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sender--2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sender--3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sender--4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2010-11-08[/TD]
[TD]karate[/TD]
[TD]d:[/TD]
[TD]sender--1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD](next)[/TD]
[TD](next)[/TD]
[TD](next)[/TD]
[TD](next)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](next)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](next)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](next)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](next)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD](next)[/TD]
[TD](next)[/TD]
[TD](next)[/TD]
[TD](next)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](next)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](next)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Leith Ross]

Hello Cloobless,

Do the date lines and subject lines have the words DATE and SUBJECT? For example, would the date appear like DATE 2010-11-08?

 

[cloobless]

Hi, Leith. No, the actual lines of the data only have the type of content described. They are not prepended with a consistent real text label, unfortunately.

 

[Leith Ross]

Hello cloobless,

Thanks for that information. Will the date be in the form of 2010-11-08 or is the format you want for the worksheet?

 

[cloobless]

Hi, Leith. The format is already in the source (text/flat file). It could just be moved over as plain text -- it doesn't need to be converted or formatted differently in the destination, if I understand why you might be asking. This is true of all of the source data -- it can call be treated as plain "General" text and number. Here is an example of a real date in the data: 2010-11-02 09:40:50

 

[mpsddata]

This is sort of similar to a question I just posted so I tried my approach on my problem (which didn't work) on yours. It's not terribly elegant (no fancy VBA or macros) but I think it would work.

First, I pasted the data into Word.
I found all the instances of two paragraph marks in a row, and replaced with @ using find/replace
I then found all the instances of one paragraph mark, and replaced with a comma
I then found all the @ and replaced with paragraph marks

That made the file look like this
DATEdata,SUBJECTdata,Locationdata,sender--1,sender--2,sender--3
DATEdata,SUBJECTdata,Locationdata,sender--1,sender--2,sender--3,sender--4
DATEdata,SUBJECTdata,Locationdata,sender--1,

I then pasted into Excel and used Text to Columns, using the comma as a delimiter.

It doesn't quite give you what you want, but you could then do some more manipulation of the file to get what you're looking for. How variable are the number of senders (what's the max?) Like I said, not fancy, but it would definitely work.

 

[Leith Ross]

Hello cloobless,

Thanks, that helps with the coding the solution. I am writing the code now.

 

[Leith Ross]

Hello cloobless,

This macro will let you select the text file you want to open and import. It is assumed the worksheet has headers. The data will start in row 2 of the active sheet and be in columns "A:D".

Add a new VBA Module to your workbook and paste the code shown into it. You can then call the macro using the keys ALT+F8 and selecting the macro or you can assign it to a button on the worksheet.

Sub ImportTextFile()


    Dim cnt         As Long
    Dim DataIn()    As Byte
    Dim DataOut     As Variant
    Dim Filename    As String
    Dim Lines       As Variant
    Dim n           As Long
    Dim Rng         As Range
    Dim Text        As String
    Dim Wks         As Worksheet
    
        Set Wks = ActiveSheet
        
        Set Rng = Wks.Range("A2:D2")
        
        Filename = Application.GetOpenFilename("Text Files,*.txt")
        If Filename = "False" Then Exit Sub
        
            Intersect(Wks.UsedRange, Wks.UsedRange.Offset(1, 0)).ClearContents
            
            Open Filename For Binary Access Read As #1
                ReDim DataIn(LOF(1))
                Get #1, , DataIn
            Close #1
            
            Text = StrConv(DataIn, vbUnicode)
            
            Lines = Split(Text, vbCrLf)
            
                ReDim DataOut(1 To 4, 1 To 1)
            
                For cnt = 0 To UBound(Lines) - 1
                    If Lines(cnt) = "" Then
                        n = 0
                        Rng.Resize(UBound(DataOut, 2), UBound(DataOut, 1)).Value = Application.Transpose(DataOut)
                        Set Rng = Rng.Offset(UBound(DataOut, 2), 0)
                        ReDim DataOut(1 To 4, 1 To 1)
                    Else
                        n = n + 1
                        ReDim Preserve DataOut(1 To 4, 1 To n)
                        If n = 1 Then
                            DataOut(1, 1) = Lines(cnt)
                            DataOut(2, 1) = Lines(cnt + 1)
                            DataOut(3, 1) = Lines(cnt + 2)
                            cnt = cnt + 3
                        End If
                        DataOut(4, n) = Lines(cnt)
                    End If
                Next cnt
            
            Rng.Resize(UBound(DataOut, 2), UBound(DataOut, 1)).Value = Application.Transpose(DataOut)
            Set Rng = Rng.Offset(UBound(DataOut, 2), 0)
            
        Wks.Columns("A:D").AutoFit
            
End Sub

 

[cloobless]

LR -- Just got back from family stuff and hope you didn't wonder where I went. I'm just blown away that you helped me like this. I was hoping for an idea and you went above and beyond.

What is your background? Programmer? What kinds of projects are you interested in? What languages?

I can't thank you enough.

 

[cloobless]

Also, I'm in a different time zone so might not be back around tonight but will check this thread tomorrow.
Thanks again, Leith Ross.