On Mon, 14 Jul 2014 11:47:16 +0100, Neil Robinson wrote:


Hi Guys,

I have a text document which contains details of around 100 of
individuals. I need to get it into a spreadsheet somehow.

The text is formated as follows

Name: Name 1
Age: Age 1
email address: email address 1
date of registration: date of registration 1
subscription expiry: subscription expiry 1
outstanding fee: Oustanding fee 1

Name: Name 2
Age: Age 2
email address: email address 2
date of registration: date of registration 2
subscription expiry: subscription expiry 2
outstanding fee: Oustanding fee 2

and so on. I've tried the standrad way of importing a text doc but as it
is not in columns it doesn't work.

Is there any way of importing / formatting this infomation into a
spreadsheet so the headings run across the top of the sheet and the data
populates below?

Thanks for any help anyone can give me.

Neil

Here's one way of doing this. It involves VBA and macros. It may be overly complicated, but can be easily maintained should your requirements change with time.

We make the following assumptions and do no error checking. Assumptions can be changed and error checking can be added, if needed.

The first line of the file, and the first line of each "segment", starts with Name:
There is a blank line between each "segment".
The "Age" field has an integer number
The "date of registration" and "subscription expiry" are both Excel Dates
The "Outstanding Fee" field might be a decimal number. If not filled in, it is equal to zero.

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Class Module and
paste the code below into the window that opens.

=====================================
Option Explicit
Private pName As String
Private pAge As Long 'need to be double if not in integer years
Private pEmail As String
Private pRegDate As Date
Private pRegExp As Date
Private pFee As Double

Public Property Get Name() As String
Name = pName
End Property
Public Property Let Name(Value As String)
pName = Value
End Property

Public Property Get Age() As Long
Age = pAge
End Property
Public Property Let Age(Value As Long)
pAge = Value
End Property

Public Property Get Email() As String
Email = pEmail
End Property
Public Property Let Email(Value As String)
pEmail = Value
End Property

Public Property Get RegDate() As Date
RegDate = pRegDate
End Property
Public Property Let RegDate(Value As Date)
pRegDate = Value
End Property

Public Property Get RegExp() As Date
RegExp = pRegExp
End Property
Public Property Let RegExp(Value As Date)
pRegExp = Value
End Property

Public Property Get Fee() As Double
Fee = pFee
End Property
Public Property Let Fee(Value As Double)
pFee = Value
End Property
==================================

Then, select the Class Module (probably called Class1) and hit <F4. Then change the Name to Individ.


From the top menu, select Tools/References and, from the dropdown, check "Microsoft Scripting Runtime"
Next select Insert/Module from the top menu and paste this next code into the regular module that opens:

=============================================
Option Explicit
Option Base 1
'Set reference to Microsoft Scripting Runtime
Sub GetFileAndFormat()
Dim FSO As FileSystemObject
Dim TS As TextStream
Dim FN As Variant
Dim cIndivid As Individ
Dim colIndivids As Collection
Dim S As Variant, V As Variant
Dim vRes() As Variant
Dim I As Long

FN = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If FN = False Then Exit Sub

Set FSO = New FileSystemObject
Set TS = FSO.OpenTextFile(FN, ForReading)
Set colIndivids = New Collection
Do
S = Split(TS.ReadLine, ":")
If UBound(S) = -1 Then ReDim S(0 To 1)
Select Case S(0)
Case Is = "Name"
Set cIndivid = New Individ
cIndivid.Name = S(1)
Case Is = "Age"
cIndivid.Age = S(1)
Case Is = "email address"
cIndivid.Email = S(1)
Case Is = "date of registration"
cIndivid.RegDate = S(1)
Case Is = "subscription expiry"
cIndivid.RegExp = S(1)
Case Is = "outstanding fee"
cIndivid.Fee = Val(S(1))
Case Is = ""
colIndivids.Add cIndivid, cIndivid.Name & cIndivid.Email
Case Else
MsgBox ("Bad Label")
Exit Sub
End Select
Loop Until TS.AtEndOfStream
colIndivids.Add cIndivid, cIndivid.Name & cIndivid.Email

ReDim vRes(0 To colIndivids.Count, 1 To 6)
V = VBA.Array("Name", "Age", "Email", "Date of Registration", "Subscription Expiry", "Outstanding Fee")
For I = 0 To UBound(V)
vRes(0, I + 1) = V(I)
Next I
For I = 1 To colIndivids.Count
Set cIndivid = colIndivids(I)
With cIndivid
vRes(I, 1) = .Name
vRes(I, 2) = .Age
vRes(I, 3) = .Email
vRes(I, 4) = .RegDate
vRes(I, 5) = .RegExp
vRes(I, 6) = .Fee
End With
Next I

Cells(1, 1).Resize(UBound(vRes, 1) + 1, UBound(vRes, 2)) = vRes

End Sub
===============================================




To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN.