How to optimize Sitemaps using your own Editable Excel File

How to optimize Sitemaps using your own Editable Excel File
Sitemaps! You don’t have to be an architect in order to understand what they mean but yes trying to make changes in those XML tables for your videos and images can be an Achilles heel. Trust me I have been in your shoes if you are a newbie or a seasoned campaigner this blog is surely going to make your life a lot easier & the best part is you can share it with your colleagues and be their savior as well.

We learned it the hard way but the key to understand this is simple ‘Just Read Carefully the below Steps’

XML Sitemaps can be generated by a lot of software’s and making edits in them is not very difficult. But when it comes to image and video sitemaps it becomes difficult to save them back in XML data format. I tried this experiment with a number of software’s but A1 Sitemap Generator is my pick amongst the others as it is fast and efficient when you are dealing with large websites.

Let’s try and solve this issue with the simplest of steps but make sure you don’t miss any of them –

Step 1: Acquire an editable Excel-XML export.
There must be many XML sitemaps that one must have already created before, which were edited in Excel and saved back to XML format. Get one of those files, make sure you don’t mess the tables or it will be very difficult to roll back the changes).

Step 2: Clear all text data in them except the header columns
Use the delete button instead of the delete rows option from Excel itself. Please be careful not to clear the header columns as they are an important part of the XML Mapping schema.

Step 3: Get an estimate of how many mapped columns you need for your site-map

E.g.: Video sitemaps usually need 11, after you know this insert those many columns in this sheet in between the already existing columns of the mapping.

Step 4: Get an estimate of how many rows you are going to need for the site-map.
You will know this from the Excel sheet of the edited site-map that you want to make. Again insert those many rows into this sheet in between the already mapped rows.

Step 4: Copy-Paste the Headers of the Video or Image site-map onto the headers row of this sheet as plain-text.

Step 5: Copy-Paste the content of your intended site-map onto the mapped rows of this sheet as plain-text.
Make sure that all the content remains within the blue formatted part of this sheet as they are the rows that contain the mapping.

Step 6: Edit the contents as required for optimization

Step 7: Save As XML Data

Step 8: Open in Notepad++ and remove the additional mapping elements that were saved just as you would do for a normal XML Site-map. E.g.: Replacing ns1: etc.

Step 9: Save & your XML file is ready for upload.

By now if you have followed the above steps as mentioned you should have an XML file which is ready for upload. I hope you have got what you were looking for but in the unlikely event that you are unable to lay your hands on the editable XL-XML editable file mentioned in Step 1 and face the same issue with those files, follow this procedure to create an Excel-XML mapping sheet.

Step 1: Open an existing error free XML sitemap
Step 2: Open an empty excel sheet
Step 3: Manually copy paste each Constant element of the site-map as columns in the new sheet. Copy paste highlighted elements into empty sheet with one an empty columns for insert-able elements

Elements in yellow are empty columns left for inserting the elements that you want to insert.

Step 4: Concatenate all these columns in one column at the end.

Step 5: Copy-Paste the column which has all the concatenated items into a notepad file.
Step 6: Copy paste following lines in the start of the doc

<urlset xmlns=”

*Or whatever latest XML sitemap schema you want to use.

Step 7: Copy Paste following line at the end of the doc

*You can put </urlset> in the beginning also to feel safe but it works none the less

Step 8: Save as an XML File
Step 9: Open the file in Notepad++ to check for proper indentation and mapping.

In order to create an Image or Video Sitemap you will have to head to Step 1 of previous process to achieve your goal of an XML Video or Image Sitemap.

If the above sets of steps have given you an idea on how you can create your own Excel XML file, the hard task is done. Now you can edit all kinds of sitemaps in excel and save it as XML as many times enhancing the bot visibility for your websites benefit.

Leave a reply