Escape commas in OSE csv export
-
Hi
When I import an OSE Site Crawl .csv to Excel, the lines get messed up. This is due to commas within the crawled site: For instance, when there is a comma in the Meta Description field, it gets separated into two fields. Is there any way to escape this so that only the correct fields get separated?
Thanks!
-
Phillip,
Thanks for writing in! Just so I could see the problem that you are looking at, could you let me know the reports that you are looking at that you are seeing this issue If you could let me know which report you downloaded, I could see if I could replicate this issue!
Looking forward in hearing from you.
Peter SEOmoz Help Team.
-
Hi Tom
Thanks for your tip. But my problem is the exact opposite. It's not that I have additional commas. Instead, a comma which appears in the site's content (such as the Meta Desc) and therefore shows up in the Site Crawl .csv, is interpreted as a csv delimiter.
What happens on importing the .csv is that a sentence containing a comma is split up into two cells.
IMO this is actually a problem with OSE's export which should make sure that commas are escaped in a .csv!
-
Hi Philipp
I think you can remove the comma separation in excel for your worksheet. Try this guide out (lifted from here)
Open the worksheet that contains the data from which you want to remove trailing commas.
Right-click the header of the column directly to the right of the data column that you want to clean. Click "Insert" in the menu to insert a new function column.
Type the following in the cell in the formula column adjacent to the first data cell:
=IF(RIGHT(A1,1)=",",LEFT(A1,LEN(A1)-1),A1)
Substitute the cell address of your first data cell in place of all instances of "A1" in the above example.
Press "Enter." Excel first determines whether the rightmost value in the data cell is a comma. If so, it determines the number of characters in the cell using the "Len" function and then returns only the leftmost N minus 1 characters, thus omitting the comma. If no comma is detected at the end of the string, then Excel returns the original cell value.
Right-click the formula cell and click "Copy." Paste the formula into the cell directly to the right of all cells from which you want to clean the commas. Excel will perform the comma-trimming function on all cells and return the update value in the formula column.
Highlight all formula cells, then right-click the array and choose "Copy."
Highlight the original data cells, then right-click the array and choose "Paste Special." Click the radio button next to "Values," then click the "OK" button. Excel will copy the output strings from the comma-less formula cells into your original data cells as static character strings.
Highlight the formula column, then right-click the array and click "Delete" from the menu. This will delete the formula column now that a permanent copy of the formula output has been saved in the original data column.
Not sure if this will help you, but here's hoping.
Got a burning SEO question?
Subscribe to Moz Pro to gain full access to Q&A, answer questions, and ask your own.
Browse Questions
Explore more categories
-
Moz Tools
Chat with the community about the Moz tools.
-
SEO Tactics
Discuss the SEO process with fellow marketers
-
Community
Discuss industry events, jobs, and news!
-
Digital Marketing
Chat about tactics outside of SEO
-
Research & Trends
Dive into research and trends in the search industry.
-
Support
Connect on product support and feature requests.
Related Questions
-
OSE for Facebook
Hi, I recall being able to use OSE for Facebook. Take https://www.facebook.com/VICE/ which we know as a URL would have many backlinks. It's need registering any. Has this always been the case?
Moz Pro | | wearehappymedia0 -
Ranking report orderng when exporting to pdf
When I am in my rankings report I like to view the results in order of ranking, so that the keywords with higher position in google for example appear at the top, which is great. However when I export it to PDF it seems to default back to appearing in alabetical order... is there a way around this? Thanks!
Moz Pro | | stardigital0 -
Csv download from open site explorer
After I run a report in Open Site explorer and download the csv, the bar says it processing # of 10,000 links, when the report is done and i open it there are only 450 links
Moz Pro | | thesea0 -
CSV not properly shown
When i download a csv i get just one colum with every information in it... which makes it unreadable.. any suggestions for this unlogical problem?
Moz Pro | | JarnoNijzing0 -
Did the SERP Overlay CSV export disappear?
I'm using Google Chrome with SERP Overlay and it use to say Export to CSV... now it says Get Keyword Difficulty Report. Was this purposely removed? This is one feature that I really liked (SEOQuake does this)... basically where I can export the search results with relevant metrics.
Moz Pro | | WrightIMC0 -
OSE Advanced Reports best practices
I'm curious how people use the OpenSiteExplorer Advanced Reports tab. It seems very powerful. What do you use it for? In particular, I see that it has choices for 'same C block' and 'different C block'. Those seem useful to find C blocks that my competitors have links from that I do not, but I'm not totally clear on how to construct the query. Any help or best practices would be appreciated. Thanks!
Moz Pro | | scanlin0 -
Trying to boost Backlinks but OSE still shows same figures for past month
I often check Open site explorer to see if I increased in linking root domains to my site after getting some backlinks on it. I have a concern about the number it is displaying: How does this statistic get updated? If I spread backlinks on some sites do i have to do anything else like ping it so google can crawl it so OSE sees it and counts it towards my linking root domain statistic? Is this current figure OSE is reading from cache and giving me an outdated number? NNzAn.jpg
Moz Pro | | lafurniturestore0 -
Any SEO moz users notice a HUGE change in OSE (Open Site Explorer) link data numbers?
Hi All, I am having some serious concern with OSE data recently for numerous clients, one client I want to talk about today has the following data from OSE for the month of August 2011 compared with July 2011: Total links to the domain: (decrease of around 100,000+)
Moz Pro | | ColumbusAustralia
External Followed links: (decrease by around 5,000)
**Linking Root domains: (decrease of over 60) ** The crazy thing is that the domain authority has actually gone up by around 5 points for this client even though every thing has suddenly gone down? Also funny thing is we have been link building quite strong for this client over the last 12 months using only high quality sources from out niche. I am worried that their is serious issues with the data, I realise we saw some updates to OSE recently yet I am suprised it can be this drastic. Kind Regards. PSV1