Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 14th 14, 11:47 AM
Junior Member
 
First recorded activity by ExcelBanter: Jul 2014
Posts: 2
Default Text File to Excel Spreadshet

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

However the information is not uniform - if there is no outstanding fee or we don't have an email, the line will not exsist -

Name: Name 3
Age: Age 3
email address: email address 3
date of registration: date of registration 3
subscription expiry: subscription expiry 3

Name: Name4
Age: Age 4
date of registration: date of registration 4
subscription expiry: subscription expiry 4
outstanding fee: Oustanding fee 4


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

Last edited by Neil Robinson : July 14th 14 at 12:22 PM

  #2   Report Post  
Old July 14th 14, 01:09 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,693
Default Text File to Excel Spreadshet

Hi Neil,

Am Mon, 14 Jul 2014 11:47:16 +0100 schrieb Neil Robinson:

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


your data in Sheet1 column A. Then the following code will write your
data with the expected formatting to Sheet2:

Sub Transpose()
Dim LRow As Long, i As Long, n As Long
Dim arrHeaders As Variant
Dim myRng As Range, rngC As Range


arrHeaders = Array("Name", "Age", "Email Address", "Date of
registration", _
"Subscription expiry", "Outstanding fee")

Sheets("Sheet2").Range("A1:F1") = arrHeaders
n = 2
i = 1

With Sheets("Sheet1")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("A1:A" & LRow)
n = IIf(i Mod 7 = 0, n + 1, n)
If Len(rngC) 0 Then
Sheets("Sheet2").Cells(n, i) = _
Mid(.Cells(rngC.Row, 1), _
InStr(.Cells(rngC.Row, 1), ":") + 2, 99)
End If
i = IIf(i Mod 7 = 0, 1, i + 1)
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #3   Report Post  
Old July 14th 14, 07:09 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2010
Posts: 1,045
Default Text File to Excel Spreadshet

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


After you import it, just use the Text-to-columns wizard on the Data ribbon and split using the colon as a delimiter.
  #4   Report Post  
Old July 14th 14, 07:11 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2010
Posts: 1,045
Default Text File to Excel Spreadshet

On Mon, 14 Jul 2014 14:09:38 -0400, Ron Rosenfeld wrote:

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


After you import it, just use the Text-to-columns wizard on the Data ribbon and split using the colon as a delimiter.


OOPs, ignore this answer, I did not read your answer thoroughly

  #5   Report Post  
Old July 15th 14, 01:40 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2010
Posts: 1,045
Default Text File to Excel Spreadshet

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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to read data from a spreadshet without opening it? NateBuckley Excel Programming 2 June 12th 08 07:55 AM
Spreadshet Help Aquagirl New Users to Excel 1 February 15th 07 04:10 AM
How do I set an excel macro to run when the spreadshet is opened MH UK Excel Programming 1 February 15th 06 04:17 PM
Why does spreadshet not print out with the actual font size? DeltaPearl Excel Discussion (Misc queries) 1 January 31st 06 05:49 PM
Protect spreadshet, but allow user edit range Souris Excel Programming 2 August 22nd 05 05:26 AM


All times are GMT +1. The time now is 06:18 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017