0

I have code below that extracts data from a text file and sends to a worksheet. Data sends to the worksheet but its a mess. I aim to extract certain columns of data in the text file. Data columns are space delimited and spacing differs between column but ea column has a heading. How can I complete my code to search text file by column header name and send data under that header to the worksheet? A typical header and columns of data underneath is shown below the code. Header blocks and associated data are repeated throughout the text file but are space delimited by rows. Above ea header band is an identifier for that block.

Sub test()
Dim Path As String
Dim textline As String
Dim arr() As String
Dim i As Long, j As Long
Path = whatever...
Open Path For Input As #1
i = 1
While EOF(1) = False
Line Input #1, textline
   arr = Split(CStr(textline), " ")
   For j = 1 To UBound(arr)
   ActiveSheet.Cells(i, j).Value = arr(j - 1)
   Next j
   i = i + 1
Wend
Close #1
End Sub

Identifier 1.1

Heave /

Wave Ampl.

/--------------/

Ampl. Phase

0.123 42

0.131 72

0.433 55

Community
  • 1
  • 1
Nui
  • 39
  • 5
  • 12
  • Nevermind, I see that you are using `split`. xD – Tim Aug 07 '16 at 17:31
  • Hi Tim, the split seems to work as all the text in the text file are sent to my worksheet and every word etc is split out into its own cell. A start but I do not know how to take more control of what data is actually extracted and sent to the spreadsheet, and where in the spreadsheet it will be stored etc – Nui Aug 07 '16 at 20:12
  • Perhaps, I'm not understanding your issue. You have everything you need to completely control what gets read and what gets put on the sheet. Adjusting `.cells(I,j)` will let you control exactly which row (i) or column (j) will get the data. You're looping through the data file line by line so if you wanted to skip all the odd lines for example, just add a counter (we'll call it `x`) and check if its odd: `If x Mod 2 = 1 Then 'It is odd`. Maybe you can paste some sample data exactly as it is formatted with an example of exactly what you want it to look like. – Tim Aug 07 '16 at 20:44
  • Tim there was a typical eg of text from the text file under the VBA code I originally sent. My text file has blocks of no.s arranged by columns. These columns have headers. In the example I gave Heave / Wave Ampl. is the header (before the fwd slash is Heave on one line and next line is Wave Ampl.). Then there are two columns under this heading with their own sub headings, Ampl. and Phase. I just want to send those columns of data to say to start at cell A2 for one column and B2 for the other; with A1 and B1 containing the sub headings (and then loop). I may need to use an If with InStr(Left.. – Nui Aug 08 '16 at 20:22
  • So your example of text under your code is exactly how your data file looks? With `Identifier 1.1` as the very first line and empty lines between every row? If that the case, you will need to add code to skip the first 10 lines (Many examples of that [here](http://stackoverflow.com/questions/938796/read-lines-from-a-text-file-but-skip-the-first-two-lines) ), and in your `For` loop, you will have to check if the line is blank (eg: `If arr(j - 1) <> "" Then 'it is a value`). You've got everything you need to do what you want, just examine that data going to the sheet and adjust accordingly. – Tim Aug 10 '16 at 14:25

0 Answers0