how can I convert data with fields in rows to fields as columns

P

PiyushAg

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....
 
J

JLatham

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
 
J

JLatham

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 said:
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 said:
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....
Click to expand...
Click to expand...
 
P

PiyushAg

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 said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Extract data in rows (with line breaks) to form a table 0
import an Excel file to create a Gantt chart in Visio; Error:do not have the required mapped columns 0
How can I update macro to delete zero amount columns? 2
Convert rows to columns 5
text lines to rows & columns 1
How to pull in data from rows and columns if there are blanks? 0
how can i present a JPG in a window having cell with the path? 2
Combining data from multiple rows 2

Top