[/B] Problem: multiple DATA SERIES [/B]

[NoahMercy]

I use some scientific instruments to collect data on a computer (man
different ones).

They all give data in a long TAB of CSV stream that looks like this i
Excel:
-
random info about the machine
may be several rows
or column in different cells*

X Y1
x y1
x y1
x y1
...*

more random info cells
gap (# of rows) between data sets is
usually constant/fixed.

X Y2
X Y2
X Y2
X Y2
...[/B]-


ALL THE X COLUMNS ARE THE SAME DATA. I REALLY NEED A WAY TO COPY EAC
SERIES OF Y DATA TO ADJACENT COLUMN SO IT WILL LOOK LIKE THIS

X Y1 Y2 Y3 ...
X Y1 Y2 Y3...
X Y1 Y2 Y3...
X Y1 Y2 Y3...
..

So that I can graph the data. There may be 50-100 series i
one file.

I've attached a very small example data file for furthe
clarification/convience.

This is a very common problem but I can't find any pointers eve
searching here. I tried it with macros, but I don't think I can do i
that way. Don't have a clue where to start, but I'll try anything.

Please give me some help if you can.

Thanks.

~K

Attachment filename: ps-ppv-2.txt
Download attachment: http://www.excelforum.com/attachment.php?postid=62710

 

[Tim Williams]

From my brief testing this seems to work

Add a reference in the Excel VBE to the microsoft scripting runtime library.

Tim.



Sub ReadInfo()

Const RESULT_SHEET As String = "Sheet1"
Const FILE_PATH As String = "C:\local files\input_test.txt"
Const SEP As String = vbTab
Dim w As Worksheet
Dim sInfoStart As String, sInfoStop As String
Dim oFSO As New Scripting.FileSystemObject
Dim f As TextStream
Dim s As String
Dim iCol As Integer, iRow As Integer
Dim bInfo As Boolean
Dim a As Variant

sInfoStart = "VA" & vbTab & "IA*"
sInfoStop = ""
iCol = 1

Set w = ThisWorkbook.Sheets(RESULT_SHEET)
Set f = oFSO.OpenTextFile(FILE_PATH)

Do While Not f.AtEndOfStream
s = f.ReadLine

If s Like sInfoStart Then
iRow = 2
iCol = iCol + 1
bInfo = True
s = f.ReadLine
End If

If bInfo = True And s = sInfoStop Then bInfo = False

If bInfo = True Then
a = Split(Trim(s), vbTab)
If iCol = 2 Then w.Cells(iRow, 1).Value = a(0)
w.Cells(iRow, iCol).Value = a(1)
iRow = iRow + 1
End If

Loop

End Sub