| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing ASCII text into worksheet
I need a program that will read information from an ASCII file, which data is
organized in fixed width. The width of the first column is 8 characters long and the columns that follow are in width of 16 characters long. For rows that start with GRID*, the 2nd column needs to be imported into a worksheet in Excel in column A. For the same row, the data in the 4th column needs to be imported into the same worksheet into column B and the data in 5th column needs to be imported into the same worksheet in column C. For rows that start with *, the 2nd column needs to be imported into the same row above in column D. The following is some sample data: $ $ --------------- begin exec section --------------------------- $ CEND $ $ $ ---------------- end exec section --------------------------- $ --------------- begin case control --------------------------- $ TITLE = ECHO = NONE OUTPUT METHOD = 10 SPC = 1 $ $ $ ---------------- end case control --------------------------- $ --------------- begin bulk data --------------------------- $ BEGIN BULK PARAM, PARAM, E GRID* 11948 02.547415380E+0035.913292032E+002+ * 1.392468901E+003 0 0 GRID* 11949 02.568299386E+0035.823013553E+002+ * 1.404233550E+003 0 0 GRID* 11950 02.560183765E+0036.110349623E+002+ * 1.393538123E+003 0 0 GRID* 11951 02.585286928E+0036.047367958E+002+ * 1.406622591E+003 0 0 GRID* 11952 02.604819484E+0034.976212049E+002+ * 1.437870452E+003 0 0 GRID* 11953 02.587617792E+0035.171256767E+002+ * 1.426662596E+003 0 0 GRID* 11954 02.579635566E+0034.921914698E+002+ * 1.427609606E+003 0 0 GRID* 11955 02.608029258E+0035.197200252E+002+ * 1.435260482E+003 0 0 GRID* 11956 02.509450618E+0031.571528462E+002+ * 1.430737695E+003 0 0 GRID* 11957 02.511725472E+0031.316694151E+002+ * 1.432848509E+003 0 0 The results should be like this: Column A Column B Column C Column D 11948 2.547415380E+003 5.913292032E+002 1.392468901E+003 11949 2.568299386E+003 5.823013553E+002 1.393538123E+003 etc... Can anyone help? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Importing ASCII text into worksheet
I had to make some assumptions about your data. You said the first field was
9 characters wide, but that is not what you posted for those starting with *, so I assumed what you posted is what is actually in the file (if not, the code should still work). Also, I am assuming that, as shown in your example, **each** line starting with GRID* is followed by **one, and only one** line starting with * (if this is not the case, then the code will **not** work correctly for you). Change the filename in the Open statement to your text file's path and name... Sub LoadDateValues() Dim X As Long Dim FileNum As Long Dim RowNum As Long Dim TotalFile As String Dim Lines() As String FileNum = FreeFile Open "C:\TEMP\TestData.txt" For Binary As #FileNum TotalFile = Space(LOF(FileNum)) Get #FileNum, , TotalFile Close #FileNum TotalFile = Mid$(TotalFile, InStr(TotalFile, "GRID*")) Lines = Split(TotalFile, vbNewLine) RowNum = 1 With Worksheets("Sheet1") For X = 0 To UBound(Lines) If Left$(Lines(X), 4) = "GRID" Then .Cells(RowNum, "A").Value = Trim$(Mid$(Lines(X), 17, 16)) .Cells(RowNum, "B").Value = Trim$(Mid$(Lines(X), 41, 16)) .Cells(RowNum, "C").Value = Trim$(Mid$(Lines(X), 57, 16)) Else .Cells(RowNum, "D").Value = Trim$(Left$(Trim$(Mid$(Lines(X), 2)), 16)) RowNum = RowNum + 1 End If Next End With End Sub Rick "nutrition" wrote in message ... I need a program that will read information from an ASCII file, which data is organized in fixed width. The width of the first column is 8 characters long and the columns that follow are in width of 16 characters long. For rows that start with GRID*, the 2nd column needs to be imported into a worksheet in Excel in column A. For the same row, the data in the 4th column needs to be imported into the same worksheet into column B and the data in 5th column needs to be imported into the same worksheet in column C. For rows that start with *, the 2nd column needs to be imported into the same row above in column D. The following is some sample data: $ $ --------------- begin exec section --------------------------- $ CEND $ $ $ ---------------- end exec section --------------------------- $ --------------- begin case control --------------------------- $ TITLE = ECHO = NONE OUTPUT METHOD = 10 SPC = 1 $ $ $ ---------------- end case control --------------------------- $ --------------- begin bulk data --------------------------- $ BEGIN BULK PARAM, PARAM, E GRID* 11948 02.547415380E+0035.913292032E+002+ * 1.392468901E+003 0 0 GRID* 11949 02.568299386E+0035.823013553E+002+ * 1.404233550E+003 0 0 GRID* 11950 02.560183765E+0036.110349623E+002+ * 1.393538123E+003 0 0 GRID* 11951 02.585286928E+0036.047367958E+002+ * 1.406622591E+003 0 0 GRID* 11952 02.604819484E+0034.976212049E+002+ * 1.437870452E+003 0 0 GRID* 11953 02.587617792E+0035.171256767E+002+ * 1.426662596E+003 0 0 GRID* 11954 02.579635566E+0034.921914698E+002+ * 1.427609606E+003 0 0 GRID* 11955 02.608029258E+0035.197200252E+002+ * 1.435260482E+003 0 0 GRID* 11956 02.509450618E+0031.571528462E+002+ * 1.430737695E+003 0 0 GRID* 11957 02.511725472E+0031.316694151E+002+ * 1.432848509E+003 0 0 The results should be like this: Column A Column B Column C Column D 11948 2.547415380E+003 5.913292032E+002 1.392468901E+003 11949 2.568299386E+003 5.823013553E+002 1.393538123E+003 etc... Can anyone help? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Importing ASCII text into worksheet
Thanks it works for the given sample data.
The first column is fixed with 8 characters as shown by counting the total of number 1. The rest of columns are 16 characters long. But I guess I did not show you the rows that comes after GRID* of the file. Its like this: 11111111222222222222222233333333333333334444444444 44444455etc GRID* 13500 02.321230505E+003 111111112222222222222222333333etc * 1.252982525E+003 The file continues with the data shown below $ LIBREF=302 MAT1* 17.170000000E+007 2.300000000E-001+ * 2.500000000E-0068.900000000E-006 PSHELL 1 13.000000 1 10.833333 11111111222222223333333334444444445555555556666666 6 CTRIA3 20327 1 11948 11949 11950 CTRIA3 20328 1 11951 11950 11949 CORD2R 2 0.0000 SPC 1 13485 123456 0.0000 END DATA What is required for the information in rows CTRIA be written in sheet number 2. Sample Results for sheet 2: Column A ColumnB ColumnC ColumnD 20327 11948 11949 11950 20328 11951 11950 11949 Note that the rows that start with CTRIA are separtated with columns of 8 characters. The numbers in series were added for clarity( to show the length of columns), those numbers are not in the text file. Thanks in Advance "Rick Rothstein (MVP - VB)" wrote: I had to make some assumptions about your data. You said the first field was 9 characters wide, but that is not what you posted for those starting with *, so I assumed what you posted is what is actually in the file (if not, the code should still work). Also, I am assuming that, as shown in your example, **each** line starting with GRID* is followed by **one, and only one** line starting with * (if this is not the case, then the code will **not** work correctly for you). Change the filename in the Open statement to your text file's path and name... Sub LoadDateValues() Dim X As Long Dim FileNum As Long Dim RowNum As Long Dim TotalFile As String Dim Lines() As String FileNum = FreeFile Open "C:\TEMP\TestData.txt" For Binary As #FileNum TotalFile = Space(LOF(FileNum)) Get #FileNum, , TotalFile Close #FileNum TotalFile = Mid$(TotalFile, InStr(TotalFile, "GRID*")) Lines = Split(TotalFile, vbNewLine) RowNum = 1 With Worksheets("Sheet1") For X = 0 To UBound(Lines) If Left$(Lines(X), 4) = "GRID" Then .Cells(RowNum, "A").Value = Trim$(Mid$(Lines(X), 17, 16)) .Cells(RowNum, "B").Value = Trim$(Mid$(Lines(X), 41, 16)) .Cells(RowNum, "C").Value = Trim$(Mid$(Lines(X), 57, 16)) Else .Cells(RowNum, "D").Value = Trim$(Left$(Trim$(Mid$(Lines(X), 2)), 16)) RowNum = RowNum + 1 End If Next End With End Sub Rick "nutrition" wrote in message ... I need a program that will read information from an ASCII file, which data is organized in fixed width. The width of the first column is 8 characters long and the columns that follow are in width of 16 characters long. For rows that start with GRID*, the 2nd column needs to be imported into a worksheet in Excel in column A. For the same row, the data in the 4th column needs to be imported into the same worksheet into column B and the data in 5th column needs to be imported into the same worksheet in column C. For rows that start with *, the 2nd column needs to be imported into the same row above in column D. The following is some sample data: $ $ --------------- begin exec section --------------------------- $ CEND $ $ $ ---------------- end exec section --------------------------- $ --------------- begin case control --------------------------- $ TITLE = ECHO = NONE OUTPUT METHOD = 10 SPC = 1 $ $ $ ---------------- end case control --------------------------- $ --------------- begin bulk data --------------------------- $ BEGIN BULK PARAM, PARAM, E GRID* 11948 02.547415380E+0035.913292032E+002+ * 1.392468901E+003 0 0 GRID* 11949 02.568299386E+0035.823013553E+002+ * 1.404233550E+003 0 0 GRID* 11950 02.560183765E+0036.110349623E+002+ * 1.393538123E+003 0 0 GRID* 11951 02.585286928E+0036.047367958E+002+ * 1.406622591E+003 0 0 GRID* 11952 02.604819484E+0034.976212049E+002+ * 1.437870452E+003 0 0 GRID* 11953 02.587617792E+0035.171256767E+002+ * 1.426662596E+003 0 0 GRID* 11954 02.579635566E+0034.921914698E+002+ * 1.427609606E+003 0 0 GRID* 11955 02.608029258E+0035.197200252E+002+ * 1.435260482E+003 0 0 GRID* 11956 02.509450618E+0031.571528462E+002+ * 1.430737695E+003 0 0 GRID* 11957 02.511725472E+0031.316694151E+002+ * 1.432848509E+003 0 0 The results should be like this: Column A Column B Column C Column D 11948 2.547415380E+003 5.913292032E+002 1.392468901E+003 11949 2.568299386E+003 5.823013553E+002 1.393538123E+003 etc... Can anyone help? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Importing ASCII text into worksheet
I think this macro will handle your data (at least the data you have told us
about so far<g)... Sub LoadDateValues() Dim X As Long Dim FileNum As Long Dim RowNum As Long Dim TotalFile As String Dim Lines() As String Dim PrevGridLine As Boolean FileNum = FreeFile Open "C:\TEMP\TestData.txt" For Binary As #FileNum TotalFile = Space(LOF(FileNum)) Get #FileNum, , TotalFile Close #FileNum TotalFile = Mid$(TotalFile, InStr(TotalFile, "GRID*")) Lines = Split(TotalFile, vbNewLine) RowNum = 1 With Worksheets("Sheet3") For X = 0 To UBound(Lines) If Left$(Lines(X), 4) = "GRID" Then .Cells(RowNum, "A").Value = Trim$(Mid$(Lines(X), 17, 16)) .Cells(RowNum, "B").Value = Trim$(Mid$(Lines(X), 41, 16)) .Cells(RowNum, "C").Value = Trim$(Mid$(Lines(X), 57, 16)) PrevGridLine = True ElseIf Left$(Lines(X), 1) = "*" And PrevGridLine Then .Cells(RowNum, "D").Value = Trim$(Left$(Trim$(Mid$(Lines(X), 2)), 16)) PrevGridLine = False RowNum = RowNum + 1 ElseIf Left$(Lines(X), 6) = "CTRIA3" Then .Cells(RowNum, "A").Value = Trim$(Mid$(Lines(X), 9, 8)) .Cells(RowNum, "B").Value = Trim$(Mid$(Lines(X), 25, 8)) .Cells(RowNum, "C").Value = Trim$(Mid$(Lines(X), 33, 8)) .Cells(RowNum, "D").Value = Trim$(Mid$(Lines(X), 41, 8)) RowNum = RowNum + 1 End If Next End With End Sub Rick "nutrition" wrote in message ... Thanks it works for the given sample data. The first column is fixed with 8 characters as shown by counting the total of number 1. The rest of columns are 16 characters long. But I guess I did not show you the rows that comes after GRID* of the file. Its like this: 11111111222222222222222233333333333333334444444444 44444455etc GRID* 13500 02.321230505E+003 111111112222222222222222333333etc * 1.252982525E+003 The file continues with the data shown below $ LIBREF=302 MAT1* 17.170000000E+007 2.300000000E-001+ * 2.500000000E-0068.900000000E-006 PSHELL 1 13.000000 1 10.833333 11111111222222223333333334444444445555555556666666 6 CTRIA3 20327 1 11948 11949 11950 CTRIA3 20328 1 11951 11950 11949 CORD2R 2 0.0000 SPC 1 13485 123456 0.0000 END DATA What is required for the information in rows CTRIA be written in sheet number 2. Sample Results for sheet 2: Column A ColumnB ColumnC ColumnD 20327 11948 11949 11950 20328 11951 11950 11949 Note that the rows that start with CTRIA are separtated with columns of 8 characters. The numbers in series were added for clarity( to show the length of columns), those numbers are not in the text file. Thanks in Advance "Rick Rothstein (MVP - VB)" wrote: I had to make some assumptions about your data. You said the first field was 9 characters wide, but that is not what you posted for those starting with *, so I assumed what you posted is what is actually in the file (if not, the code should still work). Also, I am assuming that, as shown in your example, **each** line starting with GRID* is followed by **one, and only one** line starting with * (if this is not the case, then the code will **not** work correctly for you). Change the filename in the Open statement to your text file's path and name... Sub LoadDateValues() Dim X As Long Dim FileNum As Long Dim RowNum As Long Dim TotalFile As String Dim Lines() As String FileNum = FreeFile Open "C:\TEMP\TestData.txt" For Binary As #FileNum TotalFile = Space(LOF(FileNum)) Get #FileNum, , TotalFile Close #FileNum TotalFile = Mid$(TotalFile, InStr(TotalFile, "GRID*")) Lines = Split(TotalFile, vbNewLine) RowNum = 1 With Worksheets("Sheet1") For X = 0 To UBound(Lines) If Left$(Lines(X), 4) = "GRID" Then .Cells(RowNum, "A").Value = Trim$(Mid$(Lines(X), 17, 16)) .Cells(RowNum, "B").Value = Trim$(Mid$(Lines(X), 41, 16)) .Cells(RowNum, "C").Value = Trim$(Mid$(Lines(X), 57, 16)) Else .Cells(RowNum, "D").Value = Trim$(Left$(Trim$(Mid$(Lines(X), 2)), 16)) RowNum = RowNum + 1 End If Next End With End Sub Rick "nutrition" wrote in message ... I need a program that will read information from an ASCII file, which data is organized in fixed width. The width of the first column is 8 characters long and the columns that follow are in width of 16 characters long. For rows that start with GRID*, the 2nd column needs to be imported into a worksheet in Excel in column A. For the same row, the data in the 4th column needs to be imported into the same worksheet into column B and the data in 5th column needs to be imported into the same worksheet in column C. For rows that start with *, the 2nd column needs to be imported into the same row above in column D. The following is some sample data: $ $ --------------- begin exec section --------------------------- $ CEND $ $ $ ---------------- end exec section --------------------------- $ --------------- begin case control --------------------------- $ TITLE = ECHO = NONE OUTPUT METHOD = 10 SPC = 1 $ $ $ ---------------- end case control --------------------------- $ --------------- begin bulk data --------------------------- $ BEGIN BULK PARAM, PARAM, E GRID* 11948 02.547415380E+0035.913292032E+002+ * 1.392468901E+003 0 0 GRID* 11949 02.568299386E+0035.823013553E+002+ * 1.404233550E+003 0 0 GRID* 11950 02.560183765E+0036.110349623E+002+ * 1.393538123E+003 0 0 GRID* 11951 02.585286928E+0036.047367958E+002+ * 1.406622591E+003 0 0 GRID* 11952 02.604819484E+0034.976212049E+002+ * 1.437870452E+003 0 0 GRID* 11953 02.587617792E+0035.171256767E+002+ * 1.426662596E+003 0 0 GRID* 11954 02.579635566E+0034.921914698E+002+ * 1.427609606E+003 0 0 GRID* 11955 02.608029258E+0035.197200252E+002+ * 1.435260482E+003 0 0 GRID* 11956 02.509450618E+0031.571528462E+002+ * 1.430737695E+003 0 0 GRID* 11957 02.511725472E+0031.316694151E+002+ * 1.432848509E+003 0 0 The results should be like this: Column A Column B Column C Column D 11948 2.547415380E+003 5.913292032E+002 1.392468901E+003 11949 2.568299386E+003 5.823013553E+002 1.393538123E+003 etc... Can anyone help? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Importing ASCII text into worksheet
Exactly what I was looking for. Although I added another WITH statement to
wright CTRIa# into another worksheet "Rick Rothstein (MVP - VB)" wrote: I think this macro will handle your data (at least the data you have told us about so far<g)... Sub LoadDateValues() Dim X As Long Dim FileNum As Long Dim RowNum As Long Dim TotalFile As String Dim Lines() As String Dim PrevGridLine As Boolean FileNum = FreeFile Open "C:\TEMP\TestData.txt" For Binary As #FileNum TotalFile = Space(LOF(FileNum)) Get #FileNum, , TotalFile Close #FileNum TotalFile = Mid$(TotalFile, InStr(TotalFile, "GRID*")) Lines = Split(TotalFile, vbNewLine) RowNum = 1 With Worksheets("Sheet3") For X = 0 To UBound(Lines) If Left$(Lines(X), 4) = "GRID" Then .Cells(RowNum, "A").Value = Trim$(Mid$(Lines(X), 17, 16)) .Cells(RowNum, "B").Value = Trim$(Mid$(Lines(X), 41, 16)) .Cells(RowNum, "C").Value = Trim$(Mid$(Lines(X), 57, 16)) PrevGridLine = True ElseIf Left$(Lines(X), 1) = "*" And PrevGridLine Then .Cells(RowNum, "D").Value = Trim$(Left$(Trim$(Mid$(Lines(X), 2)), 16)) PrevGridLine = False RowNum = RowNum + 1 ElseIf Left$(Lines(X), 6) = "CTRIA3" Then .Cells(RowNum, "A").Value = Trim$(Mid$(Lines(X), 9, 8)) .Cells(RowNum, "B").Value = Trim$(Mid$(Lines(X), 25, 8)) .Cells(RowNum, "C").Value = Trim$(Mid$(Lines(X), 33, 8)) .Cells(RowNum, "D").Value = Trim$(Mid$(Lines(X), 41, 8)) RowNum = RowNum + 1 End If Next End With End Sub Rick "nutrition" wrote in message ... Thanks it works for the given sample data. The first column is fixed with 8 characters as shown by counting the total of number 1. The rest of columns are 16 characters long. But I guess I did not show you the rows that comes after GRID* of the file. Its like this: 11111111222222222222222233333333333333334444444444 44444455etc GRID* 13500 02.321230505E+003 111111112222222222222222333333etc * 1.252982525E+003 The file continues with the data shown below $ LIBREF=302 MAT1* 17.170000000E+007 2.300000000E-001+ * 2.500000000E-0068.900000000E-006 PSHELL 1 13.000000 1 10.833333 11111111222222223333333334444444445555555556666666 6 CTRIA3 20327 1 11948 11949 11950 CTRIA3 20328 1 11951 11950 11949 CORD2R 2 0.0000 SPC 1 13485 123456 0.0000 END DATA What is required for the information in rows CTRIA be written in sheet number 2. Sample Results for sheet 2: Column A ColumnB ColumnC ColumnD 20327 11948 11949 11950 20328 11951 11950 11949 Note that the rows that start with CTRIA are separtated with columns of 8 characters. The numbers in series were added for clarity( to show the length of columns), those numbers are not in the text file. Thanks in Advance "Rick Rothstein (MVP - VB)" wrote: I had to make some assumptions about your data. You said the first field was 9 characters wide, but that is not what you posted for those starting with *, so I assumed what you posted is what is actually in the file (if not, the code should still work). Also, I am assuming that, as shown in your example, **each** line starting with GRID* is followed by **one, and only one** line starting with * (if this is not the case, then the code will **not** work correctly for you). Change the filename in the Open statement to your text file's path and name... Sub LoadDateValues() Dim X As Long Dim FileNum As Long Dim RowNum As Long Dim TotalFile As String Dim Lines() As String FileNum = FreeFile Open "C:\TEMP\TestData.txt" For Binary As #FileNum TotalFile = Space(LOF(FileNum)) Get #FileNum, , TotalFile Close #FileNum TotalFile = Mid$(TotalFile, InStr(TotalFile, "GRID*")) Lines = Split(TotalFile, vbNewLine) RowNum = 1 With Worksheets("Sheet1") For X = 0 To UBound(Lines) If Left$(Lines(X), 4) = "GRID" Then .Cells(RowNum, "A").Value = Trim$(Mid$(Lines(X), 17, 16)) .Cells(RowNum, "B").Value = Trim$(Mid$(Lines(X), 41, 16)) .Cells(RowNum, "C").Value = Trim$(Mid$(Lines(X), 57, 16)) Else .Cells(RowNum, "D").Value = Trim$(Left$(Trim$(Mid$(Lines(X), 2)), 16)) RowNum = RowNum + 1 End If Next End With End Sub Rick "nutrition" wrote in message ... I need a program that will read information from an ASCII file, which data is organized in fixed width. The width of the first column is 8 characters long and the columns that follow are in width of 16 characters long. For rows that start with GRID*, the 2nd column needs to be imported into a worksheet in Excel in column A. For the same row, the data in the 4th column needs to be imported into the same worksheet into column B and the data in 5th column needs to be imported into the same worksheet in column C. For rows that start with *, the 2nd column needs to be imported into the same row above in column D. The following is some sample data: $ $ --------------- begin exec section --------------------------- $ CEND $ $ $ ---------------- end exec section --------------------------- $ --------------- begin case control --------------------------- $ TITLE = ECHO = NONE OUTPUT METHOD = 10 SPC = 1 $ $ $ ---------------- end case control --------------------------- $ --------------- begin bulk data --------------------------- $ BEGIN BULK PARAM, PARAM, E GRID* 11948 02.547415380E+0035.913292032E+002+ * 1.392468901E+003 0 0 GRID* 11949 02.568299386E+0035.823013553E+002+ * 1.404233550E+003 0 0 GRID* 11950 02.560183765E+0036.110349623E+002+ * 1.393538123E+003 0 0 GRID* 11951 02.585286928E+0036.047367958E+002+ * 1.406622591E+003 0 0 GRID* 11952 02.604819484E+0034.976212049E+002+ * 1.437870452E+003 0 0 GRID* 11953 02.587617792E+0035.171256767E+002+ * 1.426662596E+003 0 0 GRID* 11954 02.579635566E+0034.921914698E+002+ * 1.427609606E+003 0 0 GRID* 11955 02.608029258E+0035.197200252E+002+ * 1.435260482E+003 0 0 GRID* 11956 02.509450618E+0031.571528462E+002+ * 1.430737695E+003 0 0 GRID* 11957 02.511725472E+0031.316694151E+002+ * 1.432848509E+003 0 0 The results should be like this: Column A Column B Column C Column D 11948 2.547415380E+003 5.913292032E+002 1.392468901E+003 11949 2.568299386E+003 5.823013553E+002 1.393538123E+003 etc... Can anyone help? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel2007 - More columns when importing ascii file? | Excel Discussion (Misc queries) | |||
| Importing Ascii file to Excell won't include Null Characters | Excel Discussion (Misc queries) | |||
| importing ASCII | Excel Discussion (Misc queries) | |||
| Importing to Excel from ASCII | Excel Discussion (Misc queries) | |||
| Excel 2000 importing huge ASCII files into different sheets | Excel Programming | |||
Linear Mode
