Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed column data
Can anyone help me with a program that reads a data file. The data file
reads the following information: $ELEMENT ID = 800000 IDENTIFIED BY TIME 8 0.000000E+00 -2.310368E-01 -4.983161E-01 3.032406E-02 9 -CONT- -3.499301E-01 6.825016E-01 -9.688503E-01 10 1.000000E-03 -1.482825E+01 3.571170E+00 5.367560E+01 11 .... .... 5.000000E-02 -2.275676E+00 4.481227E+00 2.024647E+01 109 -CONT- -2.396308E+02 -3.661783E+01 -4.990652E+01 110 ... (data repeats with element ID increasing) ... $ELEMENT ID = 800058 IDENTIFIED BY TIME 6388 0.000000E+00 1.008979E-01 -2.118058E-01 8.690994E-02 6389 -CONT- -8.416289E-01 -1.098713E-01 8.560615E-01 6390 1.000000E-03 8.365435E-01 -3.808490E+00 -2.597483E-01 6391 .... .... 5.000000E-02 -2.112889E+00 -1.135966E+01 3.044809E-01 6489 -CONT- 5.165210E+01 -9.663095E+00 -1.797665E+01 6490 ... ... $ELEMENT ID = 800059 IDENTIFIED BY TIME 6398 etc The output in Excel reads: ELEMENT ID = 800000 0.000000E+00 -2.310368E-01 -4.983161E-01 3.032406E-02 9 1.000000E-03 -1.482825E+01 3.571170E+00 5.367560E+01 11 ..etc 5.000000E-02 -2.275676E+00 4.481227E+00 2.024647E+01 109 (skip 2 rows once before the next ELEMENT ID = ??) ELEMENT ID = 800058 0.000000E+00 1.008979E-01 -2.118058E-01 8.690994E-02 6389 1.000000E-03 8.365435E-01 -3.808490E+00 -2.597483E-01 6391 ..etc 5.000000E-02 -2.112889E+00 -1.135966E+01 3.044809E-01 6489 (skip 2 rows once before the next ELEMENT ID = ??) ELEMENT ID = 800059 ..etc Thanks to everyone in advanced. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed column data
It this what you wnat? Sub ReadData() Const ForReading = 1 filetoopen = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If filetoopen = False Then MsgBox ("Cannot open file - Exiting Macro") Exit Sub End If Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.OpenTextFile(filetoopen, ForReading, False) RowCount = 0 Do While f.AtEndOfStream < True InputLine = f.ReadLine If InStr(InputLine, "$ELEMENT ID") 0 Then 'remove ending of line InputLine = _ Left(InputLine, InStr(InputLine, "IDENTIFIED BY TIME") - 1) InputLine = Trim(InputLine) If RowCount = 0 Then RowCount = 1 Else RowCount = RowCount + 3 End If Range("A" & RowCount) = InputLine Else RowCount = RowCount + 1 'put data into array around spaces Set MyArray = Nothing MyArray = Split(InputLine, " ") 'test if it is a continue line If MyArray(0) = "-CONT-" Then StartCount = 1 Else StartCount = 0 End If ColCount = 1 For Index = StartCount To UBound(MyArray) Cells(RowCount, ColCount) = MyArray(Index) ColCount = ColCount + 1 Next Index End If Loop f.Close End Sub -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=158475 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed column data
Hi Joel,
I ran the file with the program you provided and it does not work. I think the error is with counter (For Index....etc). "joel" wrote: It this what you wnat? Sub ReadData() Const ForReading = 1 filetoopen = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If filetoopen = False Then MsgBox ("Cannot open file - Exiting Macro") Exit Sub End If Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.OpenTextFile(filetoopen, ForReading, False) RowCount = 0 Do While f.AtEndOfStream < True InputLine = f.ReadLine If InStr(InputLine, "$ELEMENT ID") 0 Then 'remove ending of line InputLine = _ Left(InputLine, InStr(InputLine, "IDENTIFIED BY TIME") - 1) InputLine = Trim(InputLine) If RowCount = 0 Then RowCount = 1 Else RowCount = RowCount + 3 End If Range("A" & RowCount) = InputLine Else RowCount = RowCount + 1 'put data into array around spaces Set MyArray = Nothing MyArray = Split(InputLine, " ") 'test if it is a continue line If MyArray(0) = "-CONT-" Then StartCount = 1 Else StartCount = 0 End If ColCount = 1 For Index = StartCount To UBound(MyArray) Cells(RowCount, ColCount) = MyArray(Index) ColCount = ColCount + 1 Next Index End If Loop f.Close End Sub -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=158475 Microsoft Office Help . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed column data
What results are you getting? You should be getting some data, probably not exactly what you are expecting. Can you tell me what is wrong? -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=158475 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose Data with fixed first column | Excel Discussion (Misc queries) | |||
How do I format the A/ B Column to be fixed like numeric column | Excel Worksheet Functions | |||
How do I format the A/ B Column to be fixed like numeric column | Excel Worksheet Functions | |||
Offset from a variable column to a fixed column | Excel Programming | |||
Help: How do I create a column of fixed width data? | Excel Programming |