«« back to the tools page ««  
  »» Download Microsoft Excel file (xls2kml) »»  
  »» Download OpenOffice Calc file (ods2kml) »»
 

  xls2kml and ods2kml > MANUAL FOR ADVANCED USAGE

 Only English. Please, translate with the Google translation tool on the right. 
COLUMNS « OpenOffice »     « Columns »     « Few Remindings »     « Beginners To Kml »     « The Power Of Excel »     « How To?s »     « General Problems »     « Late Editions »
Column Name  Explanations  Example Data
The Columns column:  It has no effect to the kml. Kept there for any use while working on the dataset.   
The ID column:  Use this column if you want to call the balloon of any of the kml features (objects) in the row from another balloon.
The ID to be written by the script into the kml file will depend on the feature. So will be the href in the calling balloon (see the example on the right). After the word "layer" and before the word you enter in the cell, "P" is inserted for pushpin placemarks, "T" for paths, "O" (letter) for polygons, and "G" for ground overlays.
Learn what it is from the documentation and see washi's example.		  oslo
href link for
pushpin: "#layerPoslo;balloonFlyto"
path: "#layerToslo;balloonFlyto"
polygon: "#layerOoslo;balloonFlyto"
g.overlay: "#layerGoslo;balloonFlyto"
The Name column:(1)  The subject line of the pushpin placemark.  Google
The Latitude column:(1)  The latitude value of the pushpin placemark on earth. If it includes a space in between, or a direction letter at the end (N, S, W, E -no lowercase) the value is accepted as a degree value. You can use semicolumns, the standard notation or "deg" or "degree", as well.  37.4231
The Longitude column:(1)  The longitude value of the pushpin placemark on earth. If it includes a space in between, or a direction letter at the end (N, S, W, E -no lowercase) the value is accepted as a degree value. You can use semicolumns, the standard notation or "deg" or "degree", as well.  -122.081783
The Address column:  Overrides longitude and latitude values.
If you specify an address, other features that depends on lat and lon values like overlays and statistics may not work. To make them work too, the lat and lon values must be provided.		  Reykjavík, Iceland
The Altitude column:  The altitude value of the pushpin placemark on earth. The value is in meters.  100
The Description column:  The description field of the pushpin placemark. You can use html codes, as well.  Google Headquarters is in Mountain View, CA.
The Range column:  The distance between the camera and the place point beneath the pushpin placemark (not the placemark itself). The value is in meters.  1000
The Tilt column:  The angle of the camera. As you increase the value, the camera height is decreased keeping the Range value. The value is in degrees and between zero and 90.  60
The Heading column:  The angle of the latitudes to the camera view. The value is in degrees, counter-clockwise and between zero and 360 (360=0).  90
The Icon column:  The absolute path of pushpin placemark's icon. For no-icon option, leave empty.  http://maps.google.com/mapfiles/kml/pushpin/red-pushpin.png
Any other named column:  Any term used as a name to a column that is other than those employed in the given spreadsheet will go into the description field of the pushpin placemark. Though the Description column can even contain any html tag, the script will not underestimate additional columns because they may be valuable for databases that include fields which may need to be sorted when working.   
Other tags to be worked on:  Style tags, Altitude properties. Please, inform if anything is missed.   
The Folder column:  The great column that turns the kml into a collection. The script sorts the folders alphabetically. If left empty, the placemark(s) defined in the datarow will stay in the root folder of the kml. Nesting folders, which are also alphabetically sorted by the script, can be created with the use of the "/" character.  Continents/Countries/Mountains
Other tags to be worked on:  Folder-specific properties, shared properties of the placemarks in the folder. Please, inform if anything is missed.   
The BalloonStyle column:  The text of the BalloonStyle CDATA tag.  $[name]‹br›‹br›$[description]
The BalloonStyleBGC column:  Background color of the balloon in "aabbggrr" format expressed in hexadecimal notation. "aa" stands for alpha (transparency). Empty values (or the empty row#3) is interpreted as white.  ffe65640
The Timeline columns:  Another great feature Google encourages and one of the reasons for the creation of this script to help members benefit the Timeline. The TimeSpan tag consists of two children represented by the two columns. To utilize the TimeStamp tag instead, keep the two cells equal. Keep one of the cells (or, for the whole database, of row#3 cells) empty to define an infinite time. Or keep both empty for time-free placemark(s). For conventions to define hours, see the documentation.  1997-07-16
The Snippet column:  The description line for the placemark in the Places pane on the left. Can produce only one line and affects only the pushpin placemark. Empty value (or, for the whole dataset, empty row#3 cell) will produce placemark(s) without a description line.  any text
The Visibility column:  If it is not an empty value (or, for the whole dataset, row#3 cell is not empty), it will produce invisible placemark(s). Therefore, any value can be assigned for sorting purposes. Affects only the pushpin placemark.  any text
The GOverlay column:  The GroundOverlay image (ie, image overlay)'s absolute path, that will appear beneath the pushpin placemark of the datarow in GE's Places pane. If coordinates (explained below) are not defined, the center of the image is fit to the pushpin placemark's coordinates. In that case, because the buffer may not allow all probable images, image scale ratio is underestimated and the image is being constraint 1.5 degrees from the center to each four directions. To maintain the ratio, you need to copy the coordinates of the image from GE and paste to the appropriate cells explained below.  http://www.uwgb.edu/dutchs/TECHFOTO/MEDIEVAL/pirireis.jpg
The GO.BoundScale column:  The percentage scale of the GroundOverlay image. Empty value (or, for the whole dataset, empty row#3 cell) or 100 will not scale. Negative values are converted to positives. Therefore, minus and plus signs can be used for sorting purposes.  45
The GO.North column:  The northern edge of the image to be overlayed. As a detail, if the other edges are not defined (or their row#3 cells are empty), their coordinates are assumed to be 1.5 degrees towards the respective directions from the pushpin placemark's coordinates of the datarow. To define a distance from the puspin placemark, use the * character.  52.031 OR *.7
The GO.South column:  The southern edge of the image to be overlayed. As a detail, if the other edges are not defined (or their row#3 cells are empty), their coordinates are assumed to be 1.5 degrees towards the respective directions from the pushpin placemark's coordinates of the datarow. To define a distance from the puspin placemark, use the * character.  -51.6169 OR *.7
The GO.East column:  The eastern edge of the image to be overlayed. As a detail, if the other edges are not defined (or their row#3 cells are empty), their coordinates are assumed to be 1.5 degrees towards the respective directions from the pushpin placemark's coordinates of the datarow. To define a distance from the puspin placemark, use the * character.  4.65681 OR *.7
The GO.West column:  The western edge of the image to be overlayed. As a detail, if the other edges are not defined (or their row#3 cells are empty), their coordinates are assumed to be 1.5 degrees towards the respective directions from the pushpin placemark's coordinates of the datarow. To define a distance from the puspin placemark, use the * character.  -83.969 OR *.7
The GO.Rotation column:  The angle of the image to be overlayed to the latitudes. The value is in degrees, counter-clockwise and between zero and 360 (360=0).  30
Other tags to be worked on:  Style tags, Altitude properties, Draw Order, Refresh parameters. Please, inform if anything is missed.   
The Path column:  The coordinates of GE's path placemark, that will appear beneath the pushpin placemark of the datarow in GE's Places pane. You need to copy the path from GE, paste into NotePad, copy the coordinates there and paste to this column.  -9.07,38.66,0 -9.07,38.66,0 -9.13,38.70,0 -9.13,38.70,0
The P.Width column:  Width of the path. Empty values (or the empty row#3) is interpreted as 1.  2.5
The P.Color column:  Color of the path in "aabbggrr" format expressed in hexadecimal notation. "aa" stands for alpha (transparency). Empty values (or the empty row#3) is interpreted as white.  ff5566ee
Other tags to be worked on:  LookAt tags, Altitude properties. Please, inform if anything is missed.   
The Polygon column:  The coordinates of GE's polygon placemark, that will appear beneath the pushpin placemark of the datarow in GE's Places pane. You need to copy the polygon from GE, paste into NotePad, copy the coordinates there and paste to this column.  22.11,43.54,0 21.33,43.13,0 21.70,41.98,0 23.80,42.13,0 22.11,43.54,0
The Po.Height column:  Height of the polygon creating a box.  150
The Po.innerBoundary column:  The coordinates of the inner polygon.  21.64,43.14,0 21.82,42.55,0 22.30,42.68,0 21.97,43.23,0 21.64,43.14,0
The Po.LineWidth column:  The width of the edge lines of polygon. Empty values (or the empty row#3) is interpreted as 1.  2
The Po.LineColor column:  The color of the edge lines of polygon in "aabbggrr" format expressed in hexadecimal notation. "aa" stands for alpha (transparency). Empty values (or the empty row#3) is interpreted as white.  9956a6ff
The Po.FillColor column:  The color of the polygon in "aabbggrr" format expressed in hexadecimal notation. "aa" stands for alpha (transparency). Empty values (or the empty row#3) is interpreted as white.  ccc0a64c
Other tags to be worked on:  LookAt tags, Altitude properties. Please, inform if anything is missed.   
OTHER ABILITIES:      
The Grid.downleft column:(2)  The coordinate of the lower left corner of the grid's frame.  32.67;39.98;0
The Grid.downright column:(2)  The coordinate of the lower right corner of the grid's frame.  32.67;39.98;0
The Grid.upright column:(2)  The coordinate of the upper right corner of the grid's frame.  32.67;39.98;0
The Grid.upleft column:(2)  The coordinate of the upper left corner of the grid's frame.  32.67;39.98;0
The G.horizontalblend column:(2)  The number of grids in horizontal direction.  15
The G.verticalblend column:(2)  The number of grids in vertical direction.  8
The G.LineWidth column:  The width of the lines of the grid. Empty values (or the empty row#3) is interpreted as 1.  1.3
The G.LineColor column:  The color of the lines of the grid in "aabbggrr" format expressed in hexadecimal notation. "aa" stands for alpha (transparency). Empty values (or the empty row#3) is interpreted as white.  33ffffff
The G.FillColor column:  The color of the grid in "aabbggrr" format expressed in hexadecimal notation. "aa" stands for alpha (transparency). Empty values (or the empty row#3) is interpreted as white.  ee11f64e
Other potentials to be worked on:  LookAt tags, Altitude properties. Please, inform if anything is missed.   
The Stats.Value column:  The value of the statistical bar.  1500
The Stats.Coefficient column:  A coefficient value that is to be multiplied by the data value for visual effect.  150
The Stats.Radius column:  The radius of the imaginary circle from the coordinates that determines the size of the bar. The value is in meters.  150
The Stats.NoOfEdges column:  The number of the edges of the bar. Zero, 1 or empty value is changed to 4. Increase the number to have a circle.  4
The Stats.Rotation column:  The angle of the first corner to the Equator (not Azimuth) counter-clockwise. The default is zero. The value is in degrees.  45
The Stats.LineWidth column:  The width of the lines of the bar. Empty values (or the empty row#3) is interpreted as 1.  1.3
The Stats.LineColor column:  The color of the lines of the bar in "aabbggrr" format expressed in hexadecimal notation. "aa" stands for alpha (transparency). Empty values (or the empty row#3) is interpreted as white.  33ffffff
The Stats.FillColor column:  The color of the bar in "aabbggrr" format expressed in hexadecimal notation. "aa" stands for alpha (transparency). Empty values (or the empty row#3) is interpreted as white.  ee11f64e
Other potentials to be worked on:  LookAt tags, Altitude properties. Please, inform if anything is missed.   
The Shift column:  When, during imagery updates in GE, the images shift or when your calculated coordinates do not match to the images in GE, this column helps for adjusting the placemarks. However, it does not affect the GroundOverlays.  100;50 (means 100 meter in 50°)
MAIN STUFF TO GO ON WITH:      
   Network Links and updating tags, Multi Geometry, Models, Photos, Schemas, shared Styles, Regions, and a kml2xls script.   

1: Row#3 has no effect since this tag of a placemark is a necessity for a kml.
2: Row#3 of all these six columns must be marked to create a grid. And any empty cell in these columns will stop creating the grid.


FEW REMINDINGS « OpenOffice »     « Columns »     « Few Remindings »     « Beginners To Kml »     « The Power Of Excel »     « How To?s »     « General Problems »     « Late Editions »
Unlike it's face, the use of the spreadsheet is easy. One does not have to know anything that is beyond any need (except clearing the cells with "x"s).
With the built-up of this spreadsheet, as a first step goal, all the kml elements are intended to be utilized in a friendly manner to "manage databases" at hand for their use in GE. Any future addition of an element or a feature will not need the database created/organized in the current versions to be edited.
When you download the file, you will receive an Excel spreadsheet that is penetrated by a script to create the GE file format, kml, when you press a button in it.
In every row, you will define not one but several placemarks, the most basic one being a pushpin. A row can contain a pushpin, or a pushpin and a path, or a pushpin and a polygon, etc, or all of them. In GE's Places pane, the pushpin placemark will appear above all. So, you can keep related data together without being affected by the sorting processes (or, alternatively, you can define them separately in different rows within their own unique folders). With this method, all the placemarks will obey to the Timeline defined in the row, as well.
* It includes a sample dataset. You can test the button and open the file in your GE.
* As you can see in the sample dataset, names and descriptions fully support unicode characters. So, from now on, you can use any character of any language, or any combinations of them in your placemarks.
* Another power is the ability to define nesting folders. The "Folder" column is the real factor that makes the script a collection creator.
* The script reads the data downwards starting from row#5 until it finds an empty cell in the "Name" column. Thereby, one can create easy first samples before the huge set by having an empty row in between.
* And also it reads the columns until it finds an empty cell in row#4.
* Most of the columns represent tags (other columns are sort of functions for additional abilities which can increase in number as users demand). All the yet-supported tags are included in the sample set but you can remove them if you don't need. Or, alternatively, you can keep their cells in row#3 empty. Another alternative is to move (explained on The Power Of Excel section) the needed columns to the fore and insert an empty column before those you do not need.
Before starting...
You will realize these few information listed below when you navigate with the TAB key but here are some remindings:
1. Where to save the kml? You can enter the file path and file name in the cell under the "File Path and Name:" heading.
2. Where to name the kml? You can enter the kml's name (which will show up as the root folder name in GE's Places pane) in the cell under the "Kml Name:" heading.
3. Where to insert my legend into the kml? You can enter it's absolute path in the cell under the "Screen Overlay" heading. For the time being, in order to keep it simple, one Screen overlay is allowed. But this may change in updates.
4. Erroneous kmls? The script knows what rules are to be obeyed not to make GE angry. If anything is missing, it will prompt a message and will not create the kml. However, mistaken typings and meaningless inputs (such as entering alphanumerics into numeric fields like the coordinates) will escape. So, if GE does not open the kml, a good job would be investigating the data instead of throwing your monitor. To find the mistake, start by inserting empty rows to minimize your data (the script reads the rows until it finds an empty cell in the "Name" column).
5. How to tell it my empty extra description cells shall / shall not go into the kml? Enter 1 (one) to the cell under the "Keep Empty Descr.s:" heading. Empty or any other value will tell the script that the cells (which belong to columns for extra descriptions) that are empty will not be included to the placemark's description. If you want them, they will, in the placemark's baloon, show up as empty lines to the right of the name you have given to the column in question.
6. IMPORTANT: Do not forget to save any state-of-history before pushing the button. Excel forgets its history when a script is performed.
7. IMPORTANT: What is meant by inserting a column in "this" spreadsheet is actually inserting cells. Select cells from row#3 to the last datarow and insert cells from the Insert menu.


BEGINNERS TO KML « OpenOffice »     « Columns »     « Few Remindings »     « Beginners To Kml »     « The Power Of Excel »     « How To?s »     « General Problems »     « Late Editions »
A kml is an open-code xml file. What this script does is to insert the necessary tags between the columns of each row and, if you define a set of folders, organize them in and sort them according to the folders.
Besides being well-structured, a kml technically needs at least one placemark and a placemark needs at least three things:
1. A name of the placemark
2. A latitude value of the placemark
3. A longitude value of the placemark
With GE Plus, one can create a kml with numerous placemarks (which we call "collection" when opened in GE) with using a simple csv file with three columns named "name", "latitude", and "longitude", respectively. Any other column will go into the placemark's description field (one rule is that there should be no empty cell in the dataset).
However, GE's abilities are more than these and expanding by the time passing. The Reference documents all the features (which we call "tag"s).
What is a placemark?
Any element in GE's "Places" pane is called placemark (maybe, the only exception is a folder). They include:
1. Placemarks (the term confuses and, therefore, the term "pushpin" is instead used on this page)
2. Paths
3. Polygons
4. Ground overlays
5. Screen overlays
6. Network links
7. Models
8. Photos
and a rarely used one
9. Multi geometries
Each has different features as to let us define how the information will show up, in what form, where, when, and under which conditions with the help of some other side elements. Many of these features are not reachable from GE's interface. Even they were, GE's Places pane and the Properties window would be too complex. Therefore, one needs a tool to be able to use these.


THE POWER OF EXCEL « OpenOffice »     « Columns »     « Few Remindings »     « Beginners To Kml »     « The Power Of Excel »     « How To?s »     « General Problems »     « Late Editions »
With the spreadsheet, you can easily sort, filter, find-replace, edit, exclude/include your data/features or part of your data/features. If something among these does not work, remove the protection in the Tools menu.
By duplicating the spreadsheet in the xls file, you will be able to keep many collections or related sub-subjects in one file.
The cells you do not need and should not be edited are protected. So, you can easily navigate with the TAB key on your keyboard. If any sorting or filtering feature does not work, or if you need to move cells or columns (explained below), remove the protection in the Tools menu.
The order of the columns has no effect. Therefore, you can move them (explained on The Power Of Excel section) where ever you want (or even delete them). After removing the protection from the Tools menu, be sure of three things: (1) you have selected the column beginning with row#3, and (2) press the SHIFT key before releasing the dragging mouse (3) while seeing the vertical gray indicator between the target columns.
You can end a kml by inserting an empty row (or by removing the name of a placemark). The data after the row with empty "Name" cell will not go into the kml.
Similarly, you can end a kml's tags by inserting an empty column (or by removing the name of a feature). The data after the column with empty row#4 cell will not go into the kml.


SPECIFICS TO OPENOFFICE « OpenOffice »     « Columns »     « Few Remindings »     « Beginners To Kml »     « The Power Of Excel »     « How To?s »     « General Problems »     « Late Editions »
OpenOffice may load the file in design mode. To run it, turn off the design mode. One way to do so is to turn off the relevant button in the Form Design toolbar.
OpenOffice scripts cannot read the real value in the rounded cells. Since GE uses 6 decimals, you'd better round the lat and lon cells to 6 decimals or more for accurate kmls.
Unlike Excel, the TAB key skips protected cells but does not go to the next row when it reaches at the last columns. So, related behavior mentioned in this manual or elsewhere is not valid for OpenOffice.
OpenOffice runs scripts in background. Therefore, a message box is added to the script that will prompt when the job has been completed. Please, wait for the message box to continue working.
The OpenOffice version does not check new versions available.


HOW TO?S « OpenOffice »     « Columns »     « Few Remindings »     « Beginners To Kml »     « The Power Of Excel »     « How To?s »     « General Problems »     « Late Editions »


GENERAL PROBLEMS « OpenOffice »     « Columns »     « Few Remindings »     « Beginners To Kml »     « The Power Of Excel »     « How To?s »     « General Problems »     « Late Editions »
Pushpin placemark in a datarow where another kind of placemark has also been defined may not always be desired but it is always included to the kml.
A too wide screen is needed. And removal of undesired columns is not very easy though not difficult. Similarly, to revive removed columns is not available.
Although it is supporting unicode, not "all" languages have been tested. Needs feedback.
Until now, one error has been spotted that may trouble the users which is a Windows and/or configuration problem: Run-time error '429'.
There is a need for a reverse way (kml to xls) in order to be able to grab data from a kml (eg, polygons, paths) or to import a complete collection already worked on.


LATE EDITIONS « OpenOffice »     « Columns »     « Few Remindings »     « Beginners To Kml »     « The Power Of Excel »     « How To?s »     « General Problems »     « Late Editions »
Please, check out also the Version History. The edits relating to version updates will not be listed in this section if any use of already existing columns has not been affected by the update or if any new How To trick has not been added.
7. No-icon option available.
6. Use of * character as in the sample data lets the bounds of groundoverlays to be set as distance from the pushpin placemark.
5. Separation of grid coordinate triples are seperated by semicolons not commas to overcome the problem with users using comma as decimal point.
4. Recognized coordinate values...
3. Rules for the grid...
2. Excluding empty cells in extra description columns...
1. What is in this text meant by inserting a column...


please, wishes, reports and feedbacks here
Sorry for any mistake in my English