Extract data from text downloaded from Web

[AlexH]

I've a question that is out of my league.

I would like to extract the data from a text file that is updated daily. Then append the data to a column (or row) to build up a data set over time. The information is held here (http://www.swpc.noaa.gov/ftpdir/latest/wwv.txt) and in the format below:


roduct: Geophysical Alert Message wwv.txt

:Issued: 2012 Nov 20 1205 UTC

# Prepared by the US Dept. of Commerce, NOAA, Space Weather Prediction Center

#

# Geophysical Alert Message

#

Solar-terrestrial indices for 19 November follow.

Solar flux 134 and estimated planetary A-index 4.

The estimated planetary K-index at 1200 UTC on 20 November was 2.


No space weather storms were observed for the past 24 hours.


No space weather storms are predicted for the next 24 hours.


I can get the data into Excel (2010) but extracting the Solar flux, A-index and K index and data from the text is causing me grief. Any clues?

 

[dan_l]

If the data is pretty reliable, you could do some vba to extract based on text landmarks. Would you post how you import the data?

 

[bobhc]

God day AlexH


A simple copy paste from the web data presents no problem. So I am guessing I am not reading your problem right, could you upload a section of your spread sheet so that we can get a better understanding of what you want to achieve with the data.

 

[AlexH]

No problem, I think I may have stated on the right way. this is the rough idea so far.


A1:A13 used for web query to get data and paste in into column. Refreshed daily


Text to column done manually - could be automated with a macro - to extract data from text and place into B1, C1, D1 etc.


No idea how to append to next row. Advice would be appreciated


In the end I would like to build up a graph of data and use in to predict radio propagation based on a set of broad criteria. A very crude and simple model. I may not be doing it right thats all.


link to sheet is here (https://www.dropbox.com/s/nelste6qm73xl49/Solar%20data.xlsx)

 

[dan_l]

If that query works, the vba to parse it into rows is pretty simple.


The string functions listed here:

http://www.techonthenet.com/excel/formulas/index_vba.php


Plus a very simple count of the current number of rows, adding 1 to it, and putting the data on the next row should be a snap.

 

[AlexH]

Cheers guy's. Thanks for the help, much appreciated


Alex

 

[Debraj]

Hi Alex,


Thanks for you feed back,


Can you please check the below file..


https://dl.dropbox.com/u/78831150/Excel/Extract%20data%20from%20text%20downloaded%20from%20Web%20%28AlexH%29.xlsm


Not all part completed.. I hope you can manage.. as I have used only EXCEL Formula's not proper VBA.


I am little bit hurry.. So just tried a loosely syntax formula and code.. as not fully sure about the webpage..

Please adjust as per requirement, or someone may help you to make it better. Sorry


Regards,

Deb.

[pre]

Sub OpenHTMLpage_SearchIt()
'put your own URL below, an example is given
Dim OpenFile As String, SourceSh As Worksheet, DestSh As Worksheet
Dim lastrow As Long, issueDate As Date
OpenFile = InputBox("Enter the WebPage Address", "WebPage Search", "http://www.swpc.noaa.gov/ftpdir/latest/wwv.txt")
Application.Workbooks.Open OpenFile
Set SourceSh = ActiveSheet
Set DestSh = ThisWorkbook.Sheets(1)
With DestSh
lastrow = .Range("A" & .Cells.Rows.Count).End(xlUp).Row + 1
issueDate = CDate([=TRIM(MID(A2,SEARCH("???? ??? ??",A2),11))])
.Range("a" & lastrow) = Year(issueDate)
.Range("b" & lastrow) = Format(issueDate, "mmm")
.Range("c" & lastrow) = Day(issueDate)
'    .Range("d" & lastrow) = [=MID(A2,22,4)]
'    .Range("e" & lastrow) = [=MID(A8,12,3)]
.Range("h" & lastrow) = [=A11]
.Range("i" & lastrow) = [=A13]
End With
SourceSh.Parent.Close False
End Sub

[/pre]