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
-
AM I the only one getting misleading titles in OSE?
I am trying to locate directories in my competitor's links using OSE. Here is the workflow I am using: Filter all results to external sites only, group by site, linking to any page on the domain. Export results to csv. My competitor is in the web design industry. So I try to filter the titles of the pages linking to the competitor to look for titles containing directory. But when I click on the link for "Windsor Internet Web Design Hosting Ontario Canada Directory" I get a page with the title "Kitchen & Bathroom Showroom | London Ontario | Bathroom Vanity Showroom" Are the results really this misleading?? or am I doing something wrong here? Any insight or help would be greatly appreciated.
Moz Pro | | tdlabs0 -
OSE stats for 2 site: searoundus.org and www.seaaroundus.org.
Why are the numbers so different for the two site, one with and one without the www.? Which one is most accurate for external linking domains, for instance?
Moz Pro | | GaryDC0 -
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 -
Y!SE and OSE
Hi So now when Y!SE is closed im trying to find alternative, so im trying OSE basically i like the data that it showed to me but the numbers is unbelievable different. Im used Y!SE about 1 year so i have statistic for a year. Previous month for my client homepage i have 2625 total external links and now OSE reports me only 1558, for the main category page was 16 now 68\. Most strange for me is total external links for root domain on Y!SE its always been around 6-7k but now OSE reports me 10,053. So my question why numbers so different can it be that the Y!SE don`t update for several month there data or some others reason? Thanks, Sergey
Moz Pro | | 77Agency0 -
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 -
CSV export of Open Site Explorer is incomplete.
I exported my back links report from the Open Site Explorer toolbar as a CSV but the file it showed was only about 400 urls. The tool bar is listing over 1,200 links, so at first I thought maybe it was only exporting one link for each unique domain, but it only lists 200 or so unique domains linking to my site. I know it will only export 10,000 urls, but obviously I'm significantly below this level. Here is a link to a competitors site which is having the same issue.
Moz Pro | | bbelgard
http://www.opensiteexplorer.org/links?site=www.zoobooks.comListing about 900 links and only generating about 500 in the CSV report. Any help would be much appreciated.0 -
Incorrect domain authority result on SEO tool bar and OSE
The SEO tool bar is returning what I believe to be an incorrect domain authority of 71 and showing 24,356,141 lins from 153,051 domains. The OSE is also returning 71 as domain authority. Anyone know what could be doing this? Thanks. Jason
Moz Pro | | jayderby0