Create a KML (XML) File from a text file with HTML TAGS

**gucci** · Sep 6th, 2008, 03:55 PM

Hi Guys,

I need a little help - i will try and explain this as best i can!!

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

**MarkT** · Sep 7th, 2008, 07:37 AM

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?

**gucci** · Sep 7th, 2008, 03:45 PM

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

**MarkT** · Sep 7th, 2008, 05:18 PM

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.

**gucci** · Sep 9th, 2008, 10:08 AM

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

**MarkT** · Sep 9th, 2008, 01:10 PM

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

**gucci** · Sep 9th, 2008, 01:23 PM

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 !!

**gucci** · Sep 9th, 2008, 01:30 PM

Another problem it thats it writes it all on one line, rather than the normal xml stlye

Cheers,

Ian

**MarkT** · Sep 9th, 2008, 01:31 PM

just change this line in the GetWindSpeed function from
sngSpeed = sngSpeed * 2.24

to
sngSpeed = Round(sngSpeed * 2.24, 1)

**MarkT** · Sep 9th, 2008, 01:34 PM

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.

**gucci** · Sep 9th, 2008, 01:39 PM

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

**gucci** · Sep 9th, 2008, 01:45 PM

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

**MarkT** · Sep 9th, 2008, 01:56 PM

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.

**gucci** · Sep 9th, 2008, 02:42 PM

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

**MarkT** · Sep 9th, 2008, 02:51 PM

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?

**gucci** · Sep 9th, 2008, 02:51 PM

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>

**gucci** · Sep 9th, 2008, 02:52 PM

I modify the file by hand

**MarkT** · Sep 9th, 2008, 03:01 PM

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")

**MarkT** · Sep 9th, 2008, 03:03 PM

Also remove these lines

'Format the document with proper indentation
objDoc.loadXML FormatXML(objDoc.xml)

**gucci** · Sep 9th, 2008, 03:38 PM

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

Thread: Create a KML (XML) File from a text file with HTML TAGS

Thread Tools

Display

Create a KML (XML) File from a text file with HTML TAGS

Re: Create a KML (XML) File from a text file with HTML TAGS

Re: Create a KML (XML) File from a text file with HTML TAGS

Re: Create a KML (XML) File from a text file with HTML TAGS

Re: Create a KML (XML) File from a text file with HTML TAGS

Re: Create a KML (XML) File from a text file with HTML TAGS

Re: Create a KML (XML) File from a text file with HTML TAGS

Re: Create a KML (XML) File from a text file with HTML TAGS

Re: Create a KML (XML) File from a text file with HTML TAGS

Re: Create a KML (XML) File from a text file with HTML TAGS

Re: Create a KML (XML) File from a text file with HTML TAGS

Re: Create a KML (XML) File from a text file with HTML TAGS

Re: Create a KML (XML) File from a text file with HTML TAGS

Re: Create a KML (XML) File from a text file with HTML TAGS

Re: Create a KML (XML) File from a text file with HTML TAGS

Re: Create a KML (XML) File from a text file with HTML TAGS

Re: Create a KML (XML) File from a text file with HTML TAGS

Re: Create a KML (XML) File from a text file with HTML TAGS

Re: Create a KML (XML) File from a text file with HTML TAGS

Re: Create a KML (XML) File from a text file with HTML TAGS

Posting Permissions