|
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.... |
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.... |
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.... |
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 |
| All times are GMT +1. The time now is 01:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com