Parsing a Single Line of comma delimited data, descending a row per value in a single column, and starting a new Column after X counts.

[Bluenose616]

OK I know long winded title. Here's my issue, I have a file that has some header info then begins comma delimited data. The data are all integers pos and neg values that could go out to the 6th decimal point i.e. -3.705078. There are 819200 total values (until the next header - discussed below). I would like to read this file, start the first column with a header of titled "0" and incrementing each subsequent column title by 1 until we have 256 columns. Starting with the first row under Column "0" would be the parsed value from the comma delimited data and the next row under the same column would have the next value and this would be repeated for a total of 3200 time per column, until all 256 column have reached 3200 rows of data.

Unfortunately, this file half way down has another header like the beginning of the file. I would like to perform the same function as noted above but in a seperate worksheet for the rest of the data that follows that header in the exact same manner as performed on the 1st worksheet.

Here's an example of the layout

Column 1	Column 2	thru	Column256
Value 1	Value 3201		Value 81700
thru	thru		thru
Value 3200	Value 6400		Value 819200

<TBODY>
</TBODY>

Here's an example of the file:<FIELD name="Rng(nmi)" />

Header line 1
<VALUES name="IF Gain(dB)">Header line 2
Header line 3
Header line 4
Header line 5 <VALUES name="IF Gain(dB)">[0..360)/256, " [0..200)/3200"{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
*Need to start with the first 0 after that braces. Also the data continues like this with no break until the beginning of the next header in the file.

I hope I explained it thorough enough, I'm just not sure where to begin! Thanks in advance for anyone reading this and wanting to help.

 

[Rick Rothstein]

Header line 1
<VALUES name="IF Gain(dB)">Header line 2
Header line 3
Header line 4
Header line 5 <VALUES name="IF Gain(dB)">[0..360)/256, " [0..200)/3200"{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
*Need to start with the first 0 after that braces. Also the data continues like this with no break until the beginning of the next header in the file.

Does the run of 819200 numbers end with a closing brace (})?

 

[Rick Rothstein]

Does the run of 819200 numbers end with a closing brace (})?

If your answer to the above question is "Yes", then give this macro a try...

Sub SplitDataFileIntoColumns()
  Dim X As Long, Z As Long, Rw As Long, Col As Long, FileNum As Long, TotalFile As String
  Dim Result As Variant, Groups() As String, Fields() As String
  Const NumOfRows As Long = 3200
  Const NumOfCols As Long = 256
  Const PathAndFileName = "C:\Users\Frederick\Desktop\New Text Document.txt"
  FileNum = FreeFile
  Open PathAndFileName For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  ReDim Result(1 To NumOfRows, 1 To NumOfCols)
  Groups = Split(TotalFile, "{")
  For X = 1 To UBound(Groups)
    Fields = Split(Left(Groups(X), InStr(Groups(X), "}") - 1), ",")
    Col = 0
    For Z = 0 To UBound(Fields)
      If Z Mod NumOfRows = 0 Then
        Col = Col + 1
        Rw = 0
      End If
      Rw = Rw + 1
      Result(Rw, Col) = Fields(Z)
    Next
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = "Group" & X
    Sheets("Group" & X).Range("A1").Resize(UBound(Result), UBound(Result, 2)) = Result
  Next
End Sub