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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

how can i present a JPG in a window having cell with the path? 2
How do I rotate data in a spreadsheet so that rows become columns? 4
How can I copy filtered data to subsequent worksheets? 1
How do I move data in columns (XL) to rows in Access table 3
How do I change data that has been entered in a column to a row? 2
How can I automate the CNTR + " to repeat the data in field / reco 6
Too many fields - How to have it shown on form for data entry (MVPsHelp) 4
In an Access report, how can I count specific records in a group? 1

Back
Top