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
-
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 -
Why am I not getting my allowance of 10,000 inbound links in csv download file? 370 out of 4700??
Hi, I'm desparately trying to audit my backlinks to remove a penguin penalty on my site livefit.co.uk When I do the inbound link report i'm not getting all the links in the download. I know there is a limit of 25 links from each linking site so we get the full picture of links bu: I have 4700 links so why does it need to limit it when we are supposed to see up to 10,000? When you check the link profile on the report it doesn't seem there are many sites with anything close to 25, so surely that rule is invalid as an explanation here? Should I just work off OSE? But there is less useful info than on the csv.. I'd be very grateful for your thoughts. Thanks! James
Moz Pro | | LiveFit0 -
Why Even Publish Dates for the Next OSE Data Set?
I just noticed on the Linkscape calendar the update that was supposed to happen yesterday is pushed back to the 6th. It would be better not knowing. Is this a possibility? Or use the calendar internally, I don't care. Credibility in any announcement or claim looses muster once you do not do what you say you are going to do over and over again. Just a thought..
Moz Pro | | Mr.Rangen1 -
Can someone explain to me why OSE shows two different numbers for Linking Root Domains in the header and in the results area?
See Photo Below. 271 in the header, 500 plus in the results section. I just want an accurate number to chart my progress from here on out. Majestic numbers seem inflated too much. Google Webmaster Tools too. g58HX.png
Moz Pro | | DRPower0 -
OSE Advanced for specific directory only?
Pls how do I set up Open Site Explorer Advanced to look for backlinks to files in ONLY a certain directory? so if the domain is 1234.com, I don't care about links to 1234.com. OSE can find those very neatly! What I do care about is ONLY links to 1234.com/profiles and I can't seem to figure out a way to do this. the /profiles directory has thousands of profiles in it - and we think we have hundreds of thousands of backlinks - so ideally, I'd like to use regex or DOS like filtering to look at only those which start with 9 or 8 or whatever.
Moz Pro | | seo_plus0 -
OSE Latest Update?
As of yesterday's OSE update alot of sites that I'm tracking saw their PA & DA drop significantly. This was for multiple sites, whereas last month's index saw those stats increase significantly. Has anybody else seen this happen to them and perhaps drawn any conclusions?
Moz Pro | | MichaelWeisbaum1 -
Historical Linkscape/OSE data available?
Hey Mozzerati, I'm curious if there's a way to expose and analyze historical data from each previous Linkscape/OpenSiteExplorer (OSE) update. Essentially, I'm looking for something similar to MajesticSEO's Backlink History tool that can show you both daily links over a short time as well as cumulative links over a long period. I think the utility of such a tool is to see which competitors have gained many links over a short period, signalling that they're engaging in some form of link-building/-buying/-baiting activity. Is there any way to yield this data (other than to record it manually after each Linkscape/OSE update) from SEOmoz PRO tools?
Moz Pro | | jcolman0