Data Formatting & Parsing

[rrstudio2]

I have an ugly import file that I am trying to fix up so that I can
actually use it. The format looks like (the | is a new new)

Doe, John

ID | 1234
Phone | 5551234567

Smith, Adam

ID | 4567

Doe, Jane

ID | 4444
StId | 12345
Phone | 4444444444

etc

I would like to make this a more standard format with the data in
columns (Doe | John | 1234 | 5551234567) and am wondering how other
people would do this? My original idea was to see if the string had a
comma, if so append the cells above that...something like
=if(find(a2,",")>1, a2, a1&":"&a2). In theory that should give me a
long text string sepearted by :, then I can do text to column. In
reality, I am having problems making this work, plus it is sort of ugly
because then I have to figure out which text strings contain all the
data. Surely there must be a better way to do this. How would you do
this?

-Andrew V. Romero

 

[moon]

In Sheet1, A1: Name, B1: ID, C1: StId, D1: Phone

Private Sub WorksheetButtonImport_Click()
Const FOR_READING = 1
Dim wb As Workbook
Dim ws As Worksheet
Dim FSO, TXT
Dim r, s As Integer
Dim line As String
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
Set FSO = CreateObject("Scripting.FileSystemObject")
Set TXT = FSO.OpenTextFile("sample.txt", FOR_READING, True)
ws.Activate
r = 1
ws.Cells(r, 1).Select
While Len(ActiveCell.Value) > 0
r = r + 1
ws.Cells(r, 1).Select
Wend
Do While TXT.AtEndOfStream <> True
line = TXT.ReadLine
If line <> "" And Left(line, 2) <> "ID" And Left(line, 5) <> "Phone"
And Left(line, 4) <> "StId" Then
ws.Cells(r, 1).Value = line
r = r + 1
ElseIf Left(line, 2) = "ID" Then
For s = 1 To Len(line)
If Mid(line, s, 1) = "|" Then
ws.Cells(r - 1, 2).Value = Right(line, s)
Exit For
End If
Next s
ElseIf Left(line, 4) = "StId" Then
For s = 1 To Len(line)
If Mid(line, s, 1) = "|" Then
ws.Cells(r - 1, 3).Value = Right(line, s)
Exit For
End If
Next s
ElseIf Left(line, 5) = "Phone" Then
For s = 1 To Len(line)
If Mid(line, s, 1) = "|" Then
ws.Cells(r - 1, 4).Value = Right(line, s)
Exit For
End If
Next s
End If
Loop
TXT.Close
Set ws = Nothing
Set wb = Nothing
Set TXT = Nothing
Set FSO = Nothing
End Sub

 

[moon]

this is faster...

Do While TXT.AtEndOfStream <> True
line = TXT.ReadLine
If line <> "" And Left(line, 2) <> "ID" And Left(line, 5) <> "Phone"
And Left(line, 4) <> "StId" Then
ws.Cells(r, 1).Value = line
r = r + 1
ElseIf Left(line, 2) = "ID" Then
ws.Cells(r - 1, 2).Value = Mid(line, 6, Len(line) - 2)
ElseIf Left(line, 4) = "StId" Then
ws.Cells(r - 1, 3).Value = Mid(line, 8, Len(line) - 2)
ElseIf Left(line, 5) = "Phone" Then
ws.Cells(r - 1, 4).Value = Mid(line, 9, Len(line) - 2)
End If
Loop

 

[rrstudio2]

Thanks for the examples. I really like getting VB code because I am
not very good with VB coding. One problem I was having is that I
wasn't sure how many different ID types there were (it is a many
thousand line file). So I am not sure if ID, STID, PHONE were the only
codes I needed to screen for. I ended up looking for a comman in
column one to determine where new records started. So first I used
this formula to screen for new records
=IF(ISERROR(FIND(",",A1,1)>0),"","new")

Then a few columns over I added another formula to combine the various
record parts into one text string delimted by a : so I could seperate
out the data elements into individual columns. It uses the above
formula to determine where to start and end new records
=IF(K2="new",A2,IF(OR(A2="",B2=""),L1,L1&":"&B2))

I had a lot of issues trying to only get the : in between "fields" but
the above formula that screens for blank lines and blank data seemed to
prevent me from getting ::: in the text string. So then, I knew the
lline right before the new row contained all the data for one record,
so I only kept the last line. I then used text to column to seperate
out based on :. It seemed to work out, but it certainly isn't pretty.

Anyone else have other ideas on how to handle this?
-Andrew V. Romero