Create a KML (XML) File from a text file with HTML TAGS
Hi Guys,
I need a little help - i will try and explain this as best i can!! :eek:
I need to create a xml file the as the same template below
Code:
<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Document>
<Placemark>
<description>
<![CDATA[
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#0000FF" width="100%" id="AutoNumber1" height="86">
<tr>
<td width="100%" colspan="2" height="18"><b><font face="Tahoma" size="2">
Metar Report for statloc</font></b></td>
</tr>
<tr>
<td width="50%" height="16"><font face="Tahoma" size="2">Temp</font></td>
<td width="50%" height="16"><font face="Tahoma" size="2">TMP c</font></td>
</tr>
<tr>
<td width="50%" height="16"><font face="Tahoma" size="2">RH</font></td>
<td width="50%" height="16"><font face="Tahoma" size="2">RH %</font></td>
</tr>
<tr>
<td width="50%" height="16"><font face="Tahoma" size="2">Dew Point</font></td>
<td width="50%" height="16"><font face="Tahoma" size="2">DP c</font></td>
</tr>
<tr>
<td width="50%" height="16"><font face="Tahoma" size="2">Sea Level Pressure</font></td>
<td width="50%" height="16"><font face="Tahoma" size="2">SLP c</font></td>
</tr>
<tr>
<td width="50%" height="16"><font face="Tahoma" size="2">Wind Speed</font></td>
<td width="50%" height="16"><font face="Tahoma" size="2">WSPD mph</font></td>
</tr>
<tr>
<td width="50%" height="4"><font face="Tahoma" size="2">Wind Gust</font></td>
<td width="50%" height="4"><font face="Tahoma" size="2">WGUST mph</font></td>
</tr>
<tr>
<td width="50%" height="4"><font face="Tahoma" size="2">Wind Direction</font></td>
<td width="50%" height="4"><font face="Tahoma" size="2">WDIR</font></td>
</tr>
<tr>
<td width="50%" height="4"><font face="Tahoma" size="2">Last Updated @</font></td>
<td width="50%" height="4"><font face="Tahoma" size="2">DATE_TIME GMT</font></td>
</tr>
</table>
]]>
</description>
<Point>
<coordinates>-1,52</coordinates>
</Point>
</Placemark>
</Document>
</kml>
The code which is coloured in as RED above, is the data i need from my text file. AS Below:
Code:
EIDW,53.4,-6.25,68.0,20080906,1930,13.1,9.1,76.6,7.2,,320.0,1004,,
It is a csv.
The data follows this order, as the data i need to fill in the red parts of the KML(xml) file.
1. statloc
2. LAT
3. LON
Note that the lat and lon needs to go into the coordinates tags lon,lat and maintaining the comma
4. ELE
5. DATE
6. TIME
Note the date and time needs to go into the Date_Time Tag
7. TMP
8. DP
9. RH
10. WSPD
11. WGUST
12 WDIR
13 SLP
----------------------------------------------
Background.
I am planning to use this to create live weather details across the globe using Google Maps.
Any Questions :)
Much Help is needed please.
Thanks
Ian
P.S
The infile is called TO_FTP.txt
the outfile called google_metar.kml
Re: Create a KML (XML) File from a text file with HTML TAGS
The xml/kml file - Is it going to always be this same format with only the data changing?
The CSV file - is it always going to contain one line.
You have a coordiantes node. I don't see where that corresponds to the data you posted.
Are you looking for a VB or VBScript solution?
Re: Create a KML (XML) File from a text file with HTML TAGS
Hi again Mark,
The KML file will always be in the same format
The CSV is contain more than one line of data (as at present there will be about 80 lines, however i plan to cover all the earth metar stations which is around 7000)
The coordiantes node is the lat and lon, but needs to be displayed as lon,lat maintaining the comma. It the 2nd and 3rd collum in
VBS script will do just fine - thankyou.
BTW
Also while playing around today with a KML file is that the following does not need to be included:
<kml xmlns="http://www.opengis.net/kml/2.2">
</kml>
Many thnaks
Ian
If you wish to see where the data will be added then visit the link below
http://www.metweb.axspace.com/weather/gl_metar.html
Re: Create a KML (XML) File from a text file with HTML TAGS
So is each line of data in the csv file going to be placed inside a placemark node?
In general the XML DOM isn't too bad to work with once you get the hang of it but for something like this I would probably create a template of the placemark node with some place holders for the variables that you want to plug into the node. Then you could read a line of data, use the spilt function to get each field and then use the replace function to replace the place holders in the template with the data. Then you could add it to your document and move onto the next line of data.
If you want to post a sample file with the data I might have time to look into it further.
Re: Create a KML (XML) File from a text file with HTML TAGS
Hi Mark, sorry for the delay in getting back to you.
OK, been having a think about this and need to make some changes to the posts above.
I have also included everything i think ypu will need.
1. Below shows the raw txt file where the data comes from
Code:
EGHH,50.7,-1.83,11.0,20080909,1420,17.1,16.1,93.8,5.6,,190.0,1008,
EGKB,51.3,0.03,183.0,20080909,1420,17.1,16.1,93.8,2.5,,200.0,1010,
EGMC,51.5,0.70,15.0,20080909,1420,20.1,16.1,77.7,5.1,,240.0,1009,
EGMD,50.9,0.93,3.0,20080909,1420,18.1,14.1,77.4,7.7,,240.0,1010,
EGMH,51.3,1.35,55.0,20080909,1420,22.1,14.1,60.4,7.7,,190.0,1009,
EGNM,53.8,-1.65,208.0,20080909,1420,14.1,14.1,100.0,4.1,,150.0,1004,
EGNS,54.0,-4.63,17.0,20080909,1420,16.1,14.1,87.9,10.8,,180.0,999,
EGNT,55.0,-1.70,81.0,20080909,1420,13.1,12.1,93.6,2.5,,140.0,1004,
EGNV,54.5,-1.42,37.0,20080909,1420,16.1,14.1,87.9,5.1,,160.0,1004,
EGNX,52.8,-1.33,94.0,20080909,1420,18.1,15.1,82.6,7.2,,170.0,1005,
EGAC,54.6,-5.88,5.0,20080909,1420,15.1,13.1,87.8,9.2,,160.0,995,
EGAA,54.6,-6.22,81.0,20080909,1420,17.1,15.1,88.0,9.2,,140.0,994,
EGBB,52.4,-1.73,99.0,20080909,1420,17.1,15.1,88.0,6.1,,160.0,1005,
EGCC,53.3,-2.28,69.0,20080909,1420,18.1,16.1,88.0,7.7,,160.0,1003,
EGFF,51.4,-3.35,67.0,20080909,1420,18.1,15.1,82.6,4.6,,200.0,1005,
EGGD,51.3,-2.72,189.0,20080909,1420,16.1,15.1,93.7,7.2,,190.0,1006,
EGGP,53.3,-2.85,26.0,20080909,1420,19.1,14.1,72.7,5.6,,150.0,1002,
EGGW,51.8,0.37,160.0,20080909,1420,16.1,16.1,100.0,5.1,,180.0,1008,
EGHD,50.4,-4.12,25.0,20080909,1420,16.1,15.1,93.7,6.6,,230.0,1007,
EGHI,50.9,-1.40,9.0,20080909,1420,16.1,15.1,93.7,1.5,,130.0,1008,
EGKK,51.1,0.18,62.0,20080909,1420,17.1,15.1,88.0,1.5,,,1009,
EGLL,51.4,-0.45,24.0,20080909,1420,17.1,15.1,88.0,4.6,,140.0,1008,
EGOS,52.8,-2.67,76.0,20080909,1350,18.1,16.1,88.0,7.2,,160.0,1003,
EGOV,53.2,-4.53,1.0,20080909,1350,18.1,15.1,82.6,12.3,,190.0,1000,
EGQK,57.6,-3.57,7.0,20080909,1350,16.1,10.1,67.5,6.6,,110.0,1004,
EGQL,56.3,-2.87,12.0,20080909,1350,12.1,12.1,100.0,7.7,,130.0,1004,
EGQS,57.7,-3.32,13.0,20080909,1350,16.1,10.1,67.5,10.2,,130.0,1004,
EGDG,50.4,-5.00,119.0,20080909,1350,16.1,14.1,87.9,7.7,,240.0,1006,
EGDM,51.1,-1.75,124.0,20080909,1350,16.1,15.1,93.7,7.2,,170.0,1007,
EGDY,51.0,-2.63,23.0,20080909,1350,17.1,16.1,93.8,7.2,,200.0,1006,
EGUB,51.6,-1.08,63.0,20080909,1350,17.1,15.1,88.0,6.6,11.8,150.0,1007,
EGVN,51.7,-1.58,88.0,20080909,1350,16.1,15.1,93.7,4.1,,180.0,1006,
EGWU,51.5,0.42,38.0,20080909,1350,17.1,16.1,93.8,3.6,,200.0,1009,
EGXC,53.0,0.17,7.0,20080909,1350,18.1,15.1,82.6,6.1,,160.0,1007,
EGXT,52.6,0.47,84.0,20080909,1350,17.1,15.1,88.0,4.6,,150.0,1007,
EGPA,58.9,-2.90,21.0,20080909,1420,15.1,12.1,82.2,8.7,,130.0,1007,
The file name is called TO_FTP.txt and the CSV follows this order
1. Station
2. Lat
3. Lon
4. Elev
5. DateDate
6. DateTime
7. Temp
8. Dew
9. RH
10. WindSpeed
11. WindDir
12. PRMSL
I first need to apply some maths first before create the KML (XML) file.
8. WindSpeed *2.24
9. Gust *2.24
10. WindDir (I need to keep this format (degrees), but create vectors names aswell. The maths is as follows:
>=315,"North"
>=270,"NorthWest"
>=225,"West"
>=180,"SouthWest"
>=135,"South"
>=90,"SouthEast"
>=45,"East"
>=0,"NorthEast"
Now the KML file template
Code:
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<Document>
<Placemark>
<description>
<![CDATA[<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#0000FF" width="100%" id="AutoNumber1" height="86"><tr><td width="100%" colspan="2" height="18"><b><font face="Tahoma" size="2">Metar Report for EGHH</font></b></td></tr><tr><td width="50%" height="16"><font face="Tahoma" size="2">Temp</font></td><td width="50%" height="16"><font face="Tahoma" size="2"> 17.1 c</font></td></tr><tr><td width="50%" height="16"><font face="Tahoma" size="2">RH</font></td><td width="50%" height="16"><font face="Tahoma" size="2"> 94 %</font></td></tr><tr><td width="50%" height="16"><font face="Tahoma" size="2">Dew Point</font></td><td width="50%" height="16"><font face="Tahoma" size="2"> 16.1 c</font></td></tr><tr><td width="50%" height="16"><font face="Tahoma" size="2">Sea Level Pressure</font></td><td width="50%" height="16"><font face="Tahoma" size="2"> 1008.0 mb</font></td></tr><tr><td width="50%" height="16"><font face="Tahoma" size="2">Wind Speed</font></td><td width="50%" height="16"><font face="Tahoma" size="2"> 12.5 mph</font></td></tr><tr><td width="50%" height="4"><font face="Tahoma" size="2">Wind Gust</font></td><td width="50%" height="4"><font face="Tahoma" size="2"> 0.0 mph</font></td></tr><tr><td width="50%" height="4"><font face="Tahoma" size="2">Wind Direction</font></td><td width="50%" height="4"><font face="Tahoma" size="2"> SouthWest</font></td></tr><tr><td width="50%" height="4"><font face="Tahoma" size="2">Last Updated @</font></td><td width="50%" height="4"><font face="Tahoma" size="2"> 1420 GMT</font></td></tr></table>]]>
</description>
<Point>
<coordinates>-1.83,50.7</coordinates>
</Point>
</Placemark>
</Document>
Below is the CDATA - HTML Code
Code:
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#0000FF" width="100%" id="AutoNumber1" height="86"><tr><td width="100%" colspan="2" height="18"><b><font face="Tahoma" size="2">Metar Report for Station</font></b></td></tr><tr><td width="50%" height="16"><font face="Tahoma" size="2">Temp</font></td><td width="50%" height="16"><font face="Tahoma" size="2">TEMPc</font></td></tr><tr><td width="50%" height="16"><font face="Tahoma" size="2">RH</font></td><td width="50%" height="16"><font face="Tahoma" size="2"> RH %</font></td></tr><tr><td width="50%" height="16"><font face="Tahoma" size="2">Dew Point</font></td><td width="50%" height="16"><font face="Tahoma" size="2"> DEW c</font></td></tr><tr><td width="50%" height="16"><font face="Tahoma" size="2">Sea Level Pressure</font></td><td width="50%" height="16"><font face="Tahoma" size="2"> PRMSL mb</font></td></tr><tr><td width="50%" height="16"><font face="Tahoma" size="2">Wind Speed</font></td><td width="50%" height="16"><font face="Tahoma" size="2"> WINDSPEED mph</font></td></tr><tr><td width="50%" height="4"><font face="Tahoma" size="2">Wind Gust</font></td><td width="50%" height="4"><font face="Tahoma" size="2"> GUST mph</font></td></tr><tr><td width="50%" height="4"><font face="Tahoma" size="2">Wind Direction</font></td><td width="50%" height="4"><font face="Tahoma" size="2"> WIND VECTOR NAMES AND WIND DIR </font></td></tr><tr><td width="50%" height="4"><font face="Tahoma" size="2">Last Updated @</font></td><td width="50%" height="4"><font face="Tahoma" size="2"> DATEDATE AND DATETIME</font></td></tr></table>]]>
The Output file is called GL_METAR.kml
ALSO THE <coordinates>-1.83,50.7</coordinates> tag should read as below
<coordinates>LON,LAT</coordinates>
Thanks,
Ian
Re: Create a KML (XML) File from a text file with HTML TAGS
This was quickly thrown together so there may be some errors but it should give you a real good start. Just make sure you have the correct path to your source file.
Code:
Option Explicit
Dim fso, ts
Dim objDoc, objProcInst, objRoot, objNode, objChild, objCData
Dim strSource, strOutput
Dim strData, strHTML
'Set these accordingly
strSource = "TO_FTP.txt"
strOutput = "GL_METAR.kml"
'Get the html template
strHTML = GetTemplate
'Open the csv file
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.OpenTextFile("TO_FTP.txt")
'Create your xml document
Set objDoc = CreateObject("Microsoft.XMLDOM")
'add the Proceessing Instructions
Set objProcInst = objDoc.createProcessingInstruction("xml", "version='1.0' encoding='utf-8' standalone='yes'")
objDoc.appendChild objProcInst
Set objProcInst = Nothing
'add the root node
Set objRoot = objDoc.createElement("Document")
objDoc.appendChild objRoot
'Start Reading the Data a line at a time
Do While Not ts.AtEndOfStream
strData = ts.ReadLine
'Make sure you we didn't get a blank line
If Trim(strData) <> "" Then
Set objNode = objDoc.createElement("Placemark")
'objNode.Text = strData
objRoot.appendChild objNode
'Create the Description node
Set objChild = objDoc.createElement("Description")
objNode.appendChild objChild
'Add the CDATA to the Description node
Set objCData = objDoc.createCDATASection("description")
objCData.Text = GetCData(strHTML, strData)
objChild.appendChild objCData
'Add the Point node
Set objChild = objDoc.createElement("Point")
objNode.appendChild objChild
'Add the coordinates to the Point node and assign a value
Set objNode = objDoc.createElement("coordinates")
objNode.Text = GetLonLat(strData)
objChild.appendChild objNode
Set objChild = Nothing
Set objCData = Nothing
Set objNode = Nothing
End If
Loop
ts.Close
objDoc.save strOutput
Set objRoot = Nothing
Set objDoc = Nothing
Set ts = Nothing
Set fso = Nothing
Private Function GetLonLat(ByVal Data)
Dim strData
'Split the line of data on the commas
strData = Split(Data, ",")
GetLonLat = strData(2) & "," & strData(1)
End Function
Private Function GetCData(ByVal HtmlTemplate, byval Data)
Dim strData
'Split the line of data on the commas
strData = Split(Data, ",")
'Replace the placeholders with the data
HtmlTemplate = Replace(HtmlTemplate, "[STATION]", strData(0))
HtmlTemplate = Replace(HtmlTemplate, "[TEMP]", strData(6))
HtmlTemplate = Replace(HtmlTemplate, "[RH]", strData(8))
HtmlTemplate = Replace(HtmlTemplate, "[DEW]", strData(7))
HtmlTemplate = Replace(HtmlTemplate, "[PRMSL]", strData(12))
HtmlTemplate = Replace(HtmlTemplate, "[WINDSPEED]", GetWindSpeed(strData(9)))
HtmlTemplate = Replace(HtmlTemplate, "[GUST]", GetWindSpeed(strData(10)))
HtmlTemplate = Replace(HtmlTemplate, "[VECTOR]", GetDirection(strData(11)))
HtmlTemplate = Replace(HtmlTemplate, "[DATEDATE AND DATETIME]", strData(4) & " " & strData(5))
GetCData = HtmlTemplate
End Function
Private Function GetWindSpeed(ByVal strSpeed)
Dim sngSpeed
'Check if a wind speed is passed in
If strSpeed <> "" Then
'Convert the string to a number
sngSpeed = CSng(strSpeed)
'make the adjustment
sngSpeed = sngSpeed * 2.24
GetWindSpeed = CStr(sngSpeed)
Else
GetWindSpeed = "0.0"
End If
End Function
Private Function GetDirection(ByVal Degrees)
Dim strDirection, intDegree
'Check if there is a value
If Degrees <> "" Then
'convert to a number
intDegree = CInt(Degrees)
'Get the string value
If Degrees >= 315 then
strDirection = "North"
Elseif Degrees >= 270 then
strDirection = "NorthWest"
Elseif Degrees >= 225 Then
strDirection = "West"
Elseif Degrees >= 180 Then
strDirection = "SouthWest"
Elseif Degrees >= 135 Then
strDirection = "South"
Elseif Degrees >= 90 Then
strDirection = "SouthEast"
Elseif Degrees >= 45 Then
strDirection = "East"
Else
strDirection = "NorthEast"
End If
GetDirection = strDirection & " " & Degrees & " degrees"
End If
End Function
Private Function GetTemplate()
Dim strHTML(29)
strHTML(0) = "<table border='1' cellpadding='0' cellspacing='0' "
strHTML(1) = "style='border-collapse: collapse' bordercolor='#0000FF' "
strHTML(2) = "width='100%' id='AutoNumber1' height='86'>"
strHTML(3) = "<tr><td width='100%' colspan='2' height='18'><b>"
strHTML(4) = "<font face='Tahoma' size='2'>Metar Report for [STATION]</font></b>"
strHTML(5) = "</td></tr><tr><td width='50%' height='16'>"
strHTML(6) = "<font face='Tahoma' size='2'>Temp</font></td>"
strHTML(7) = "<td width='50%' height='16'><font face='Tahoma' size='2'>[TEMP]c</font></td></tr>"
strHTML(8) = "<tr><td width='50%' height='16'>"
strHTML(9) = "<font face='Tahoma' size='2'>RH</font></td>"
strHTML(10) = "<td width='50%' height='16'><font face='Tahoma' size='2'> [RH] %</font></td></tr>"
strHTML(11) = "<tr><td width='50%' height='16'>"
strHTML(12) = "<font face='Tahoma' size='2'>Dew Point</font></td>"
strHTML(13) = "<td width='50%' height='16'><font face='Tahoma' size='2'> [DEW] c</font></td></tr>"
strHTML(14) = "<tr><td width='50%' height='16'>"
strHTML(15) = "<font face='Tahoma' size='2'>Sea Level Pressure</font></td>"
strHTML(16) = "<td width='50%' height='16'><font face='Tahoma' size='2'> [PRMSL] mb</font></td></tr>"
strHTML(17) = "<tr><td width='50%' height='16'>"
strHTML(18) = "<font face='Tahoma' size='2'>Wind Speed</font></td>"
strHTML(19) = "<td width='50%' height='16'><font face='Tahoma' size='2'> [WINDSPEED] mph</font></td></tr>"
strHTML(20) = "<tr><td width='50%' height='4'><font face='Tahoma' size='2'>Wind Gust</font></td>"
strHTML(21) = "<td width='50%' height='4'><font face='Tahoma' size='2'> [GUST] mph</font></td></tr>"
strHTML(22) = "<tr><td width='50%' height='4'><font face='Tahoma' size='2'>Wind Direction</font></td>"
strHTML(23) = "<td width='50%' height='4'>"
strHTML(24) = "<font face='Tahoma' size='2'> [VECTOR] </font></td></tr>"
strHTML(25) = "<tr><td width='50%' height='4'>"
strHTML(26) = "<font face='Tahoma' size='2'>Last Updated @</font></td>"
strHTML(27) = "<td width='50%' height='4'>"
strHTML(28) = "<font face='Tahoma' size='2'> [DATEDATE AND DATETIME]</font></td>"
strHTML(29) = "</tr></table>"
GetTemplate = Join(strHTML, "")
End Function
Re: Create a KML (XML) File from a text file with HTML TAGS
Hi Mark,
Works a treat, just more error (although not really a error) it that i need to round up the wind speed and wind gust figuers, if thats possible please.
Currently create this 16.1279995727539
Need something like this 16.1
Thanks - Great work !!
Re: Create a KML (XML) File from a text file with HTML TAGS
Another problem it thats it writes it all on one line, rather than the normal xml stlye
Cheers,
Ian
Re: Create a KML (XML) File from a text file with HTML TAGS
just change this line in the GetWindSpeed function from
sngSpeed = sngSpeed * 2.24
to
sngSpeed = Round(sngSpeed * 2.24, 1)
Re: Create a KML (XML) File from a text file with HTML TAGS
If you change the extension to xml and view it in a browser it will be formatted correctly. It can be formatted when you open it in notepad but that would be a little more work.
Re: Create a KML (XML) File from a text file with HTML TAGS
Changed the speed - works well,
But still can't view the data on the google maps. I can see the markers, but when i click on nothing happens
http://www.metweb.axspace.com/weather/gl_metar.html
Re: Create a KML (XML) File from a text file with HTML TAGS
It might work now - I think there was a error on the first try - it did not end with the placement tags and document, but the 2nd time it did - will let you know. or you can check out the link above.
Cheers,
Ian
Again many thanks for all you help
Re: Create a KML (XML) File from a text file with HTML TAGS
To get the indentation on the xml you can add this to the bottom of the script.
Code:
'#############################################################################################
'#############################################################################################
' every thing below here was taken from and modified from Martin's response #31 in this thread
' http://vbforums.com/showthread.php?t...ght=xml+indent
Private Function FormatXML(ByVal strXML)
Dim oXMLDoc
Dim oXSLT
Set oXMLDoc = CreateObject("MSXML.DOMDocument")
Set oXSLT = CreateObject("MSXML.DOMDocument")
oXMLDoc.async = False
oXSLT.async = False
oXMLDoc.loadXML strXML
oXSLT.loadXML GetXSL
FormatXML = oXMLDoc.transformNode(oXSLT)
End Function
Private Function GetXSL()
Dim strXSL(8)
strXSL(0) = "<?xml version=""1.0"" encoding=""UTF-8""?>"
strXSL(1) = "<xsl:stylesheet version=""1.0"" xmlns:xsl=""http://www.w3.org/1999/XSL/Transform"">"
strXSL(2) = "<xsl:output method=""xml"" version=""1.0"" encoding=""UTF-8"" indent=""yes""/>"
strXSL(3) = "<xsl:template match=""@* | node()"">"
strXSL(4) = "<xsl:copy>"
strXSL(5) = "<xsl:apply-templates select=""@* | node()"" />"
strXSL(6) = "</xsl:copy>"
strXSL(7) = "</xsl:template>"
strXSL(8) = "</xsl:stylesheet>"
GetXSL = Join(strXSL, vbCrLf)
End Function
And when you go to save the file change it so it looks like this
Code:
'Format the document with proper indentation
objDoc.loadXML FormatXML(objDoc.xml)
objDoc.save strOutput
As for nothing coming up, I have no ideas about that.
Re: Create a KML (XML) File from a text file with HTML TAGS
I could not get this to work, added in the parts you said
Code:
Option Explicit
Dim fso, ts
Dim objDoc, objProcInst, objRoot, objNode, objChild, objCData
Dim strSource, strOutput
Dim strData, strHTML
'Set these accordingly
strSource = "C:\METWEB\BATCH\GOOGLE_MADIS\RAW\TO_FTP.txt"
strOutput = "C:\METWEB\BATCH\GOOGLE_MADIS\FTP2METWEB\GL_METAR_UK.kml"
'Get the html template
strHTML = GetTemplate
'Open the csv file
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.OpenTextFile("C:\METWEB\BATCH\GOOGLE_MADIS\RAW\TO_FTP.txt")
'Create your xml document
Set objDoc = CreateObject("Microsoft.XMLDOM")
'add the Proceessing Instructions
Set objProcInst = objDoc.createProcessingInstruction("xml", "version='1.0' encoding='utf-8' standalone='yes'")
objDoc.appendChild objProcInst
Set objProcInst = Nothing
'add the root node
Set objRoot = objDoc.createElement("Document")
objDoc.appendChild objRoot
'Start Reading the Data a line at a time
Do While Not ts.AtEndOfStream
strData = ts.ReadLine
'Make sure you we didn't get a blank line
If Trim(strData) <> "" Then
Set objNode = objDoc.createElement("Placemark")
'objNode.Text = strData
objRoot.appendChild objNode
'Create the Description node
Set objChild = objDoc.createElement("Description")
objNode.appendChild objChild
'Add the CDATA to the Description node
Set objCData = objDoc.createCDATASection("description")
objCData.Text = GetCData(strHTML, strData)
objChild.appendChild objCData
'Add the Point node
Set objChild = objDoc.createElement("Point")
objNode.appendChild objChild
'Add the coordinates to the Point node and assign a value
Set objNode = objDoc.createElement("coordinates")
objNode.Text = GetLonLat(strData)
objChild.appendChild objNode
Set objChild = Nothing
Set objCData = Nothing
Set objNode = Nothing
End If
Loop
ts.Close
'Format the document with proper indentation
objDoc.loadXML FormatXML(objDoc.xml)
objDoc.save strOutput
Set objRoot = Nothing
Set objDoc = Nothing
Set ts = Nothing
Set fso = Nothing
Private Function GetLonLat(ByVal Data)
Dim strData
'Split the line of data on the commas
strData = Split(Data, ",")
GetLonLat = strData(2) & "," & strData(1)
End Function
Private Function GetCData(ByVal HtmlTemplate, byval Data)
Dim strData
'Split the line of data on the commas
strData = Split(Data, ",")
'Replace the placeholders with the data
HtmlTemplate = Replace(HtmlTemplate, "[STATION]", strData(0))
HtmlTemplate = Replace(HtmlTemplate, "[TEMP]", strData(6))
HtmlTemplate = Replace(HtmlTemplate, "[RH]", strData(8))
HtmlTemplate = Replace(HtmlTemplate, "[DEW]", strData(7))
HtmlTemplate = Replace(HtmlTemplate, "[PRMSL]", strData(12))
HtmlTemplate = Replace(HtmlTemplate, "[WINDSPEED]", GetWindSpeed(strData(9)))
HtmlTemplate = Replace(HtmlTemplate, "[GUST]", GetWindSpeed(strData(10)))
HtmlTemplate = Replace(HtmlTemplate, "[VECTOR]", GetDirection(strData(11)))
HtmlTemplate = Replace(HtmlTemplate, "[DATEDATE AND DATETIME]", strData(4) & " " & strData(5))
GetCData = HtmlTemplate
End Function
Private Function GetWindSpeed(ByVal strSpeed)
Dim sngSpeed
'Check if a wind speed is passed in
If strSpeed <> "" Then
'Convert the string to a number
sngSpeed = CSng(strSpeed)
'make the adjustment
sngSpeed = Round(sngSpeed * 2.24, 1)
GetWindSpeed = CStr(sngSpeed)
Else
GetWindSpeed = "0.0"
End If
End Function
Private Function GetDirection(ByVal Degrees)
Dim strDirection, intDegree
'Check if there is a value
If Degrees <> "" Then
'convert to a number
intDegree = CInt(Degrees)
'Get the string value
If Degrees >= 315 then
strDirection = "North"
Elseif Degrees >= 270 then
strDirection = "NorthWest"
Elseif Degrees >= 225 Then
strDirection = "West"
Elseif Degrees >= 180 Then
strDirection = "SouthWest"
Elseif Degrees >= 135 Then
strDirection = "South"
Elseif Degrees >= 90 Then
strDirection = "SouthEast"
Elseif Degrees >= 45 Then
strDirection = "East"
Else
strDirection = "NorthEast"
End If
GetDirection = strDirection & " " & Degrees & " degrees"
End If
End Function
Private Function GetTemplate()
Dim strHTML(29)
strHTML(0) = "<table border='1' cellpadding='0' cellspacing='0' "
strHTML(1) = "style='border-collapse: collapse' bordercolor='#0000FF' "
strHTML(2) = "width='100%' id='AutoNumber1' height='86'>"
strHTML(3) = "<tr><td width='100%' colspan='2' height='18'><b>"
strHTML(4) = "<font face='Tahoma' size='2'>Metar Report for [STATION]</font></b>"
strHTML(5) = "</td></tr><tr><td width='50%' height='16'>"
strHTML(6) = "<font face='Tahoma' size='2'>Temp</font></td>"
strHTML(7) = "<td width='50%' height='16'><font face='Tahoma' size='2'>[TEMP]c</font></td></tr>"
strHTML(8) = "<tr><td width='50%' height='16'>"
strHTML(9) = "<font face='Tahoma' size='2'>RH</font></td>"
strHTML(10) = "<td width='50%' height='16'><font face='Tahoma' size='2'> [RH] %</font></td></tr>"
strHTML(11) = "<tr><td width='50%' height='16'>"
strHTML(12) = "<font face='Tahoma' size='2'>Dew Point</font></td>"
strHTML(13) = "<td width='50%' height='16'><font face='Tahoma' size='2'> [DEW] c</font></td></tr>"
strHTML(14) = "<tr><td width='50%' height='16'>"
strHTML(15) = "<font face='Tahoma' size='2'>Sea Level Pressure</font></td>"
strHTML(16) = "<td width='50%' height='16'><font face='Tahoma' size='2'> [PRMSL] mb</font></td></tr>"
strHTML(17) = "<tr><td width='50%' height='16'>"
strHTML(18) = "<font face='Tahoma' size='2'>Wind Speed</font></td>"
strHTML(19) = "<td width='50%' height='16'><font face='Tahoma' size='2'> [WINDSPEED] mph</font></td></tr>"
strHTML(20) = "<tr><td width='50%' height='4'><font face='Tahoma' size='2'>Wind Gust</font></td>"
strHTML(21) = "<td width='50%' height='4'><font face='Tahoma' size='2'> [GUST] mph</font></td></tr>"
strHTML(22) = "<tr><td width='50%' height='4'><font face='Tahoma' size='2'>Wind Direction</font></td>"
strHTML(23) = "<td width='50%' height='4'>"
strHTML(24) = "<font face='Tahoma' size='2'> [VECTOR] </font></td></tr>"
strHTML(25) = "<tr><td width='50%' height='4'>"
strHTML(26) = "<font face='Tahoma' size='2'>Last Updated @</font></td>"
strHTML(27) = "<td width='50%' height='4'>"
strHTML(28) = "<font face='Tahoma' size='2'> [DATEDATE AND DATETIME]</font></td>"
strHTML(29) = "</tr></table>"
GetTemplate = Join(strHTML, "")
'#############################################################################################
'#############################################################################################
' every thing below here was taken from and modified from Martin's response #31 in this thread
' http://vbforums.com/showthread.php?t...ght=xml+indent
Private Function FormatXML(ByVal strXML)
Dim oXMLDoc
Dim oXSLT
Set oXMLDoc = CreateObject("MSXML.DOMDocument")
Set oXSLT = CreateObject("MSXML.DOMDocument")
oXMLDoc.async = False
oXSLT.async = False
oXMLDoc.loadXML strXML
oXSLT.loadXML GetXSL
FormatXML = oXMLDoc.transformNode(oXSLT)
End Function
Private Function GetXSL()
Dim strXSL(8)
strXSL(0) = "<?xml version=""1.0"" encoding=""UTF-8""?>"
strXSL(1) = "<xsl:stylesheet version=""1.0"" xmlns:xsl=""http://www.w3.org/1999/XSL/Transform"">"
strXSL(2) = "<xsl:output method=""xml"" version=""1.0"" encoding=""UTF-8"" indent=""yes""/>"
strXSL(3) = "<xsl:template match=""@* | node()"">"
strXSL(4) = "<xsl:copy>"
strXSL(5) = "<xsl:apply-templates select=""@* | node()"" />"
strXSL(6) = "</xsl:copy>"
strXSL(7) = "</xsl:template>"
strXSL(8) = "</xsl:stylesheet>"
GetXSL = Join(strXSL, vbCrLf)
End Function
Re: Create a KML (XML) File from a text file with HTML TAGS
I'm not sure why the formatting doesn't work for you but I also discovered that it removes the CDATA part.
I also see you have got things working for a single point. What does the file look like for that?
Re: Create a KML (XML) File from a text file with HTML TAGS
For Google to read the KML file it needs to be in this format as opened in notepad
Code:
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<Document>
<Placemark>
<description>
<![CDATA[<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#0000FF" width="100%" id="AutoNumber1" height="86"><tr><td width="100%" colspan="2" height="18"><b><font face="Tahoma" size="2">Metar Report for EGHH</font></b></td></tr><tr><td width="50%" height="16"><font face="Tahoma" size="2">Temp</font></td><td width="50%" height="16"><font face="Tahoma" size="2"> 17.1 c</font></td></tr><tr><td width="50%" height="16"><font face="Tahoma" size="2">RH</font></td><td width="50%" height="16"><font face="Tahoma" size="2"> 94 %</font></td></tr><tr><td width="50%" height="16"><font face="Tahoma" size="2">Dew Point</font></td><td width="50%" height="16"><font face="Tahoma" size="2"> 16.1 c</font></td></tr><tr><td width="50%" height="16"><font face="Tahoma" size="2">Sea Level Pressure</font></td><td width="50%" height="16"><font face="Tahoma" size="2"> 1008.0 mb</font></td></tr><tr><td width="50%" height="16"><font face="Tahoma" size="2">Wind Speed</font></td><td width="50%" height="16"><font face="Tahoma" size="2"> 12.5 mph</font></td></tr><tr><td width="50%" height="4"><font face="Tahoma" size="2">Wind Gust</font></td><td width="50%" height="4"><font face="Tahoma" size="2"> 0.0 mph</font></td></tr><tr><td width="50%" height="4"><font face="Tahoma" size="2">Wind Direction</font></td><td width="50%" height="4"><font face="Tahoma" size="2"> SouthWest</font></td></tr><tr><td width="50%" height="4"><font face="Tahoma" size="2">Last Updated @</font></td><td width="50%" height="4"><font face="Tahoma" size="2"> 1420 GMT</font></td></tr></table>]]>
</description>
<Point>
<coordinates>-1.83,50.7</coordinates>
</Point>
</Placemark>
</Document>
Re: Create a KML (XML) File from a text file with HTML TAGS
I modify the file by hand
Re: Create a KML (XML) File from a text file with HTML TAGS
I'm not sure if this will do it or not but xml is case sensitive. In the example above the description node is lowercase but the code I posted would make the node start with a capital D. You may want to change this line and see if it helps.
Set objChild = objDoc.createElement("Description")
to
Set objChild = objDoc.createElement("description")
Re: Create a KML (XML) File from a text file with HTML TAGS
Also remove these lines
'Format the document with proper indentation
objDoc.loadXML FormatXML(objDoc.xml)
Re: Create a KML (XML) File from a text file with HTML TAGS
Hi Mate,
All Working - Thanks
Thats the UK - Next it's the WORLD!!
-----------------------------------
Thanks again, might just play around with the dates and times, but apart from that perfect.
I have got a few more ideas for google earth including tracking hurricanes, so might drop you a line for some help if that's OK.
Cheers,
Ian
