Transposing text in text file to Excel Sheet.
 

Hi,

I am working on a code which will process a text file (TempFile.txt),
which contains a data copied from an Outlook .msg file containing a
table (single table-2 Columns data). The text file contains text
extracted from various MS Outlook .msg files & copied to a
TempFile.txt.

The data in generated textfile looks like :

Agent's Name
Thomas Rody

Gender
Male

etc....

What i want to do is to transpose the data under the same labels used
as Column Headers in an Excel sheet.


Code will look something like....

Sub ProcessTempFile(TempFileName as String, Datasheet as Worksheet)
....

I donot know how to extract data from a text file & tranpose
horizontally under each header column e.g.

Column A Column B Column C Column D
---------------------------------------------------
Agent's Name Thomas Rody Gender Male

Please Help!

Transposing text in text file to Excel Sheet.
 

see if this will get you going. i put my tempfile.txt in c:\files. look for
that in the code and change it to your location. also, watch for outlook
express truncating any lines of code


Dim RowNdx As Integer
Dim ColNdx As Integer
Dim WholeLine As String
Dim FName As Variant
Dim sRow As Integer
Dim lRow As Range
Dim fil As Variant
Dim FileDir As Variant
Dim x As Integer
Dim FilesInPath As String
Dim MyFiles() As String
Dim NumberOfFiles As Long
Dim lastrow As Long
Dim LastCol
Dim xR As Integer
Sub ImportText()
Application.ScreenUpdating = False
FileDir = "C:\Files\"
Sheets("sheet1").Range("A1").Select
ColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row
sRow = RowNdx

' test for existing data
If Range("a1") "" Then
MsgBox "Append And Clear Before" & Chr(10) & "Performing Another Import"
Exit Sub
End If

' determine # of files
FilesInPath = Dir(FileDir & "\*.txt")
NumberOfFiles = 0
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

' perform import of email files
Do While FilesInPath < ""
Open FileDir & FilesInPath For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
Cells(RowNdx, ColNdx).Value = WholeLine
ColNdx = ColNdx + 1

Wend
RowNdx = RowNdx + 1
Close #1

NumberOfFiles = NumberOfFiles + 1

ReDim Preserve MyFiles(1 To NumberOfFiles)
MyFiles(NumberOfFiles) = FilesInPath
FilesInPath = Dir()
Loop

Application.ScreenUpdating = True

End Sub


--


Gary


"sifar" wrote in message
oups.com...
Hi,

I am working on a code which will process a text file (TempFile.txt),
which contains a data copied from an Outlook .msg file containing a
table (single table-2 Columns data). The text file contains text
extracted from various MS Outlook .msg files & copied to a
TempFile.txt.

The data in generated textfile looks like :

Agent's Name
Thomas Rody

Gender
Male

etc....

What i want to do is to transpose the data under the same labels used
as Column Headers in an Excel sheet.


Code will look something like....

Sub ProcessTempFile(TempFileName as String, Datasheet as Worksheet)
...

I donot know how to extract data from a text file & tranpose
horizontally under each header column e.g.

Column A Column B Column C Column D
---------------------------------------------------
Agent's Name Thomas Rody Gender Male

Please Help!

Transposing text in text file to Excel Sheet.
 

Hi Gary,

The TempFile looks like this:
Start of text file.......................................

Subject: Modem Order

! Fill in the dotted boxes !


--------------------------------------------------------------------------------


MODEM ORDER [FAULTY MODEM]





1. CSA Name
jazz melano

2. Team Leader's name
Mani Ratnam

3. Date
23/09/2005

4. Time
0:13:00 PM GMT

5. Customer Forename
Wendy

6. Customer Surname
dorchester

7. Customer Email Address
test.gonet.co.uk

8. Address 1 (House Number/Name)
22, looney circle

9. Address 2 (Road)
FairyLand

10. Address 3 (Town/City)
Sussex

11. Address 4 (Country)
Rodeo

12. Postcode
SR2 0AL

13. FSBB Number
FSBB21112214

14. Account Number


15. NLD Number


16. Additional Notes


End of text file --------------------------------------------------
Now the Text file not only contains just one Escalation data extracted
from MSOutlook Inbox but many with the same Subject.
Each label value is placed under same label columns in an Excel sheet
named "Modem Order" starting from the 2nd Row (as 1st Row contains the
Labels).

e.g Column Labels in 1st Row of Excel sheet are :
1. CSA Name
2. Team Leader's name
3. Date
So, need to compare somehow the data in textfile with Excel Column
Labels & then paste their values under them in Excel Worksheet. e.g.

1. CSA Name 2. Team Leader's name 3. Date
Mani Ratnam Mani Ratnam 23/09/2005
Jazz Melano James Hadley 25/09/2005
Hosey Rathod Uri Geller 01/10/2005
Anthony Gonalves Johnny Liver 03/10/2005

Etc....

How to do this????