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.
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
-
Open Site Explorer and Escaped Fragments
Does OSE have the ability to crawl AJAX pages utilizing Google's escaped fragment directive? I ask because I'm seeing all our AJAX built pages returning HTTP status codes of 404 when I run OSE reports. See for yourself
Moz Pro | | RyanOD0 -
How does OSE select top 10,000 links
Quick question about OSE, if you are extracting with excel all inbound links from a big site like IMDB.com, OSE only exports 10,000. However IMDB has alot more links then that. I was wondering what criteria does OSE use to select those 10,000 links from the 100,000+ which IMDB has. Is it a random selection of those 10,000 links or does it select those 10,000 links based on metrics like DA or PA? For the settings i have selected: followed + 301s, only external, pages on this root domain, group by domain. Cheers, Chris
Moz Pro | | MBASydney0 -
Can anybody point me to an OSE tutorial blog on Competitive Analysis?
I came across a few blogs which got me started here on an SEOmoz Pro account and now I am working on building a competitive link analysis spreadsheet in Excel in order to identify how are competitors are beating us in Google rankings for specific keywords. One article I came across seemed to do exactly what I wanted, but the images are all broken and the downloads require a membership to some service I am not interested in: http://www.seerinteractive.com/blog/how-are-they-beating-you-quick-seo-analysis-tools-part-1/ Anybody have any resources like this they can share? Thank you.
Moz Pro | | jesse-landry0 -
OSE http or htps
Hello, Is OSE making any difference when crawling the web between http or https links? Will OSE show all links that point to my website regardless of them being http or https? Thank you Cornel Note: The Label for the search bar is http.
Moz Pro | | Cornel_Ilea0 -
SEO Yoast data export
Just thought I would give something back. (Is this the right place!) I use Wordpress with the excellent SEO Yoast plugin. I needed a way of extracting the focus keywords that I have entered onto my pages along with the url for use on the SEOmoz On-page Optimisation tool. So I created GetYoastData which outputs to the browser the required data (and a bit more) that can be saved into an csv (Excel) file. Hope you find it useful - Yes it's not polished and yes it might output a blank line now and again but it's fairly useful. http://deanandrews.uk/get-yoast-seo-data/
Moz Pro | | DeanAndrews0 -
OSE Report Shows HTTP Status "No Data"
Open Site Explorer shows HTTP Status "No Data" on 1,575 urls of the 3,335 urls listed in the "Top Pages" report for my site. What does this mean?
Moz Pro | | debc0 -
Discrepancy in OSE data
When I look up a domain in OSE, it shows that there are 3K+ links from 349 domains. However, when I download the CSV, I only see around 1,500 links. Why the discrepancy?
Moz Pro | | inhouseseo0 -
OSE shows 118 FB shares but we show more, whats wrong here?
I ran www.lauriloewenberg.com/ in OSE and it shows 118 Facebook shares while we see more than that. Maybe we are doing something wrong or a setting isn't correct, I'm not sure. The site was created in WordPress. Here are 3 posts that add up to 224 shares: Post 1 114 shares Post 2 64 shares Post 3 46 shares I'm sure there are some people that are sharing multiple posts, Will that make a difference? Even if that was true the number still seems very low. Thanks 🙂
Moz Pro | | Mike-Dream0