Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > how can I convert data with fields in rows to fields as columns
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 4
Default how can I convert data with fields in rows to fields as columns
I want to import data from a text file into Excel. The data consists of 1000
records, each record has 13 fields, each field and its corresponding value
(separated by a :)is in a row in the text file. A sample of 2 records in text
file is as follows:

Image Info:
Image Name: cslegalvb_1182334115_C1_HDR
Date: 1182334115
Full Date:
Policy: BR_VM_nt_fs_cs_21_52-4
Save As : (STS_SA_IMAGE)
Stream Format: TAR
Type: STS_IMG_FULL
Server Name: csnans02
LSU Name: /vol/fs_21_dsu7
Size: 4096
Block Size: 8192
Exports:
Status: (STS_II_IMAGE_CREATED | STS_II_FILES_CREATED |
STS_II_BUSY_READ)
Image Group : ()
Image Info:
Image Name: cslegalvb_1182334115_C1_TIR
Date: 1182334115
Full Date:
Policy: BR_VM_nt_fs_cs_21_52-4
Save As : (STS_SA_IMAGE)
Stream Format: TAR
Type: STS_IMG_FULL
Server Name: csnans02
LSU Name: /vol/fs_21_dsu7
Size: 2531328
Block Size: 8192
Exports:
Status: (STS_II_IMAGE_CREATED | STS_II_FILES_CREATED |
STS_II_BUSY_READ)
Image Group : ()

How do I import this text file such that each record is contained in a row
and value of each field is contained in columns?

Thanks in advance for any pointers....
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 3,365
Default how can I convert data with fields in rows to fields as columns
Try downloading this Excel (97-2003 format) file. It has a macro that should
do the job for you if all is as described. I have assumed that the file with
the data that you've shown is exactly as shown and that it is a .txt file.
http://www.jlathamsite.com/uploads/CustomTextReader.xls
Just click the link and save to your hard drive.
The macro name is ParseCustomFile and you can get to it with
Tools | Macro |Macros

The code for the macro is as follows:
Sub ParseCustomFile()
'these are the field indicators
Const newRecordStart = "Image Info:"
Const iName = "Image Name:"
Const iDate = "Full Date:"
Const iSaveAs = "Save As :" ' note space before :
Const iStream = "Stream Format:"
Const iType = "Type:"
Const iServer = "Server Name:"
Const iLSUName = "LSU Name:"
Const iSize = "Size:"
Const iBSize = "Block Size:"
Const iExports = "Exports:"
Const iStatus = "Status:"
Const iGroup = "Image Group :" ' note space before :

Dim fName As Variant
Dim fNumber As Integer
Dim rawData As String
Dim iData As String
Dim iField As String
Dim rOffset As Long
Dim cOffset As Integer

'change *.txt in next line if the file
'is of different type, as *.dat or other.
fName = _
Application.GetOpenFilename("Text Files (*.txt), *.txt")
If fName = False Then
'user hit [Cancel] button
Exit Sub ' quit
End If

'presumes that you have headers in row 1
'of the active sheet for the information fields
rOffset = _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row - 1

fNumber = FreeFile()
Open fName For Input As #fNumber
Do While Not (EOF(fNumber))
Line Input #fNumber, rawData
rawData = Trim(rawData)
If InStr(rawData, newRecordStart) = 1 Then
rOffset = rOffset + 1
cOffset = 0
End If

iField = iName
cOffset = 0
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iDate
cOffset = 1
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iSaveAs
cOffset = 2
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iStream
cOffset = 3
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iType
cOffset = 4
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iServer
cOffset = 5
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iLSUName
cOffset = 6
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iSize
cOffset = 7
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iBSize
cOffset = 8
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iExports
cOffset = 9
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iStatus
cOffset = 10
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iGroup
cOffset = 11
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If
Loop
Close #fNumber
End Sub


"PiyushAg" wrote:

I want to import data from a text file into Excel. The data consists of 1000
records, each record has 13 fields, each field and its corresponding value
(separated by a :)is in a row in the text file. A sample of 2 records in text
file is as follows:

Image Info:
Image Name: cslegalvb_1182334115_C1_HDR
Date: 1182334115
Full Date:
Policy: BR_VM_nt_fs_cs_21_52-4
Save As : (STS_SA_IMAGE)
Stream Format: TAR
Type: STS_IMG_FULL
Server Name: csnans02
LSU Name: /vol/fs_21_dsu7
Size: 4096
Block Size: 8192
Exports:
Status: (STS_II_IMAGE_CREATED | STS_II_FILES_CREATED |
STS_II_BUSY_READ)
Image Group : ()
Image Info:
Image Name: cslegalvb_1182334115_C1_TIR
Date: 1182334115
Full Date:
Policy: BR_VM_nt_fs_cs_21_52-4
Save As : (STS_SA_IMAGE)
Stream Format: TAR
Type: STS_IMG_FULL
Server Name: csnans02
LSU Name: /vol/fs_21_dsu7
Size: 2531328
Block Size: 8192
Exports:
Status: (STS_II_IMAGE_CREATED | STS_II_FILES_CREATED |
STS_II_BUSY_READ)
Image Group : ()

How do I import this text file such that each record is contained in a row
and value of each field is contained in columns?

Thanks in advance for any pointers....
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 3,365
Default how can I convert data with fields in rows to fields as column
Oops! I left out processing the Policy field. New code below, new workbook
uploaded, same link as above.

Sub ParseCustomFile()
'these are the field indicators
Const newRecordStart = "Image Info:"
Const iName = "Image Name:"
Const iDate = "Full Date:"
Const iPolicy = "Policy:"
Const iSaveAs = "Save As :" ' note space before :
Const iStream = "Stream Format:"
Const iType = "Type:"
Const iServer = "Server Name:"
Const iLSUName = "LSU Name:"
Const iSize = "Size:"
Const iBSize = "Block Size:"
Const iExports = "Exports:"
Const iStatus = "Status:"
Const iGroup = "Image Group :" ' note space before :

Dim fName As Variant
Dim fNumber As Integer
Dim rawData As String
Dim iData As String
Dim iField As String
Dim rOffset As Long
Dim cOffset As Integer

'change *.txt in next line if the file
'is of different type, as *.dat or other.
fName = _
Application.GetOpenFilename("Text Files (*.txt), *.txt")
If fName = False Then
'user hit [Cancel] button
Exit Sub ' quit
End If

'presumes that you have headers in row 1
'of the active sheet for the information fields
rOffset = _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row - 1

fNumber = FreeFile()
Open fName For Input As #fNumber
Do While Not (EOF(fNumber))
Line Input #fNumber, rawData
rawData = Trim(rawData)
If InStr(rawData, newRecordStart) = 1 Then
rOffset = rOffset + 1
cOffset = 0
End If

iField = iName
cOffset = 0
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iDate
cOffset = 1
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iPolicy
cOffset = 2
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iSaveAs
cOffset = 3
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iStream
cOffset = 4
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iType
cOffset = 5
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iServer
cOffset = 6
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iLSUName
cOffset = 7
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iSize
cOffset = 8
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iBSize
cOffset = 9
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iExports
cOffset = 10
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iStatus
cOffset = 11
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iGroup
cOffset = 12
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If
Loop
Close #fNumber
End Sub


"JLatham" wrote:

Try downloading this Excel (97-2003 format) file. It has a macro that should
do the job for you if all is as described. I have assumed that the file with
the data that you've shown is exactly as shown and that it is a .txt file.
http://www.jlathamsite.com/uploads/CustomTextReader.xls
Just click the link and save to your hard drive.
The macro name is ParseCustomFile and you can get to it with
Tools | Macro |Macros

The code for the macro is as follows:
Sub ParseCustomFile()
'these are the field indicators
Const newRecordStart = "Image Info:"
Const iName = "Image Name:"
Const iDate = "Full Date:"
Const iSaveAs = "Save As :" ' note space before :
Const iStream = "Stream Format:"
Const iType = "Type:"
Const iServer = "Server Name:"
Const iLSUName = "LSU Name:"
Const iSize = "Size:"
Const iBSize = "Block Size:"
Const iExports = "Exports:"
Const iStatus = "Status:"
Const iGroup = "Image Group :" ' note space before :

Dim fName As Variant
Dim fNumber As Integer
Dim rawData As String
Dim iData As String
Dim iField As String
Dim rOffset As Long
Dim cOffset As Integer

'change *.txt in next line if the file
'is of different type, as *.dat or other.
fName = _
Application.GetOpenFilename("Text Files (*.txt), *.txt")
If fName = False Then
'user hit [Cancel] button
Exit Sub ' quit
End If

'presumes that you have headers in row 1
'of the active sheet for the information fields
rOffset = _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row - 1

fNumber = FreeFile()
Open fName For Input As #fNumber
Do While Not (EOF(fNumber))
Line Input #fNumber, rawData
rawData = Trim(rawData)
If InStr(rawData, newRecordStart) = 1 Then
rOffset = rOffset + 1
cOffset = 0
End If

iField = iName
cOffset = 0
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iDate
cOffset = 1
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iSaveAs
cOffset = 2
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iStream
cOffset = 3
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iType
cOffset = 4
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iServer
cOffset = 5
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iLSUName
cOffset = 6
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iSize
cOffset = 7
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iBSize
cOffset = 8
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iExports
cOffset = 9
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iStatus
cOffset = 10
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iGroup
cOffset = 11
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If
Loop
Close #fNumber
End Sub


"PiyushAg" wrote:

I want to import data from a text file into Excel. The data consists of 1000
records, each record has 13 fields, each field and its corresponding value
(separated by a :)is in a row in the text file. A sample of 2 records in text
file is as follows:

Image Info:
Image Name: cslegalvb_1182334115_C1_HDR
Date: 1182334115
Full Date:
Policy: BR_VM_nt_fs_cs_21_52-4
Save As : (STS_SA_IMAGE)
Stream Format: TAR
Type: STS_IMG_FULL
Server Name: csnans02
LSU Name: /vol/fs_21_dsu7
Size: 4096
Block Size: 8192
Exports:
Status: (STS_II_IMAGE_CREATED | STS_II_FILES_CREATED |
STS_II_BUSY_READ)
Image Group : ()
Image Info:
Image Name: cslegalvb_1182334115_C1_TIR
Date: 1182334115
Full Date:
Policy: BR_VM_nt_fs_cs_21_52-4
Save As : (STS_SA_IMAGE)
Stream Format: TAR
Type: STS_IMG_FULL
Server Name: csnans02
LSU Name: /vol/fs_21_dsu7
Size: 2531328
Block Size: 8192
Exports:
Status: (STS_II_IMAGE_CREATED | STS_II_FILES_CREATED |
STS_II_BUSY_READ)
Image Group : ()

How do I import this text file such that each record is contained in a row
and value of each field is contained in columns?

Thanks in advance for any pointers....
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 4
Default how can I convert data with fields in rows to fields as column
Dear JLatham,
Thank you for creating the Macro and uploading the spreadsheet. The macro
works great except I noticed that the "Date" field (2nd field) is not getting
processed. I am not macro savy and do not want to much around with the Macro.
Would you be able to modify the Macro to also include the "Date" field?
This scores a 10....
Many thanks
Regards
PiyushAg

"JLatham" wrote:

Oops! I left out processing the Policy field. New code below, new workbook
uploaded, same link as above.

Sub ParseCustomFile()
'these are the field indicators
Const newRecordStart = "Image Info:"
Const iName = "Image Name:"
Const iDate = "Full Date:"
Const iPolicy = "Policy:"
Const iSaveAs = "Save As :" ' note space before :
Const iStream = "Stream Format:"
Const iType = "Type:"
Const iServer = "Server Name:"
Const iLSUName = "LSU Name:"
Const iSize = "Size:"
Const iBSize = "Block Size:"
Const iExports = "Exports:"
Const iStatus = "Status:"
Const iGroup = "Image Group :" ' note space before :

Dim fName As Variant
Dim fNumber As Integer
Dim rawData As String
Dim iData As String
Dim iField As String
Dim rOffset As Long
Dim cOffset As Integer

'change *.txt in next line if the file
'is of different type, as *.dat or other.
fName = _
Application.GetOpenFilename("Text Files (*.txt), *.txt")
If fName = False Then
'user hit [Cancel] button
Exit Sub ' quit
End If

'presumes that you have headers in row 1
'of the active sheet for the information fields
rOffset = _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row - 1

fNumber = FreeFile()
Open fName For Input As #fNumber
Do While Not (EOF(fNumber))
Line Input #fNumber, rawData
rawData = Trim(rawData)
If InStr(rawData, newRecordStart) = 1 Then
rOffset = rOffset + 1
cOffset = 0
End If

iField = iName
cOffset = 0
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iDate
cOffset = 1
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iPolicy
cOffset = 2
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iSaveAs
cOffset = 3
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iStream
cOffset = 4
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iType
cOffset = 5
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iServer
cOffset = 6
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iLSUName
cOffset = 7
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iSize
cOffset = 8
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iBSize
cOffset = 9
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iExports
cOffset = 10
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iStatus
cOffset = 11
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iGroup
cOffset = 12
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If
Loop
Close #fNumber
End Sub


"JLatham" wrote:

Try downloading this Excel (97-2003 format) file. It has a macro that should
do the job for you if all is as described. I have assumed that the file with
the data that you've shown is exactly as shown and that it is a .txt file.
http://www.jlathamsite.com/uploads/CustomTextReader.xls
Just click the link and save to your hard drive.
The macro name is ParseCustomFile and you can get to it with
Tools | Macro |Macros

The code for the macro is as follows:
Sub ParseCustomFile()
'these are the field indicators
Const newRecordStart = "Image Info:"
Const iName = "Image Name:"
Const iDate = "Full Date:"
Const iSaveAs = "Save As :" ' note space before :
Const iStream = "Stream Format:"
Const iType = "Type:"
Const iServer = "Server Name:"
Const iLSUName = "LSU Name:"
Const iSize = "Size:"
Const iBSize = "Block Size:"
Const iExports = "Exports:"
Const iStatus = "Status:"
Const iGroup = "Image Group :" ' note space before :

Dim fName As Variant
Dim fNumber As Integer
Dim rawData As String
Dim iData As String
Dim iField As String
Dim rOffset As Long
Dim cOffset As Integer

'change *.txt in next line if the file
'is of different type, as *.dat or other.
fName = _
Application.GetOpenFilename("Text Files (*.txt), *.txt")
If fName = False Then
'user hit [Cancel] button
Exit Sub ' quit
End If

'presumes that you have headers in row 1
'of the active sheet for the information fields
rOffset = _
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row - 1

fNumber = FreeFile()
Open fName For Input As #fNumber
Do While Not (EOF(fNumber))
Line Input #fNumber, rawData
rawData = Trim(rawData)
If InStr(rawData, newRecordStart) = 1 Then
rOffset = rOffset + 1
cOffset = 0
End If

iField = iName
cOffset = 0
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iDate
cOffset = 1
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iSaveAs
cOffset = 2
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
End If

iField = iStream
cOffset = 3
If InStr(rawData, iField) = 1 Then
If Len(rawData) Len(iField) Then
iData = Right(rawData, Len(rawData) - Len(iField))
Else
iData = ""
End If
Range("A1").Offset(rOffset, cOffset) = iData
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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
Make pivot table with multiple "data" fields in columns not rows? cdomby Excel Discussion (Misc queries) 1 January 15th 07 04:54 PM
Excel SHOULD NOT AUTO-CHANGE formated text fields to DATE FIELDS! PSSSD Excel Worksheet Functions 2 August 8th 06 09:31 PM
number of fields in the row fields in pivot table UT Excel Discussion (Misc queries) 0 April 13th 06 01:17 AM
Need pie chart with number of fields instead of info in fields Lloyd Pratt Charts and Charting in Excel 1 September 21st 05 11:56 PM


All times are GMT +1. The time now is 11:14 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement