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
-
Internal Links Data on OSE and poor ranking despite excellent metrics
Hi folks, I use OSE a lot for comparing where we stand against our competitors and I'm always surprised to see most of our competitors have loads more internal links than we do. For example, comparing 'www.kensyard.co.uk' with 'www.sterlingbuild.co.uk', we are better on every Root Domain Metric except for the Total Links metric and this is down to a much smaller number of internal links. They are ranking better than us in Google so I'm wondering if there is something wrong with the way our site is coded / structured that means Google and OSE are not picking up our internal link network properly? We have over nine times as many external root domains linking to our site than theirs, which is surely a far more valuable metric than internal links right? Or is there an issue with our internal link structure that is confusing Google and causing us to rank lower? Many thanks for any advice, Luke
Moz Pro | | LukeyB300 -
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 -
HTTPS on OSE
Just wondering if any of the Moz team can give us a heads up when open site explorer will support HTTPS?
Moz Pro | | Martin_Harris0 -
Export & Email Keyword Report for Selected Tags
Is there a way to get a keyword ranking summary report emailed to me for specific tags?
Moz Pro | | CPollock0 -
Setting to import into gDocs an Opensitexplorer CSV report.
That's pretty much it. Been trying to import an inbound links report generated with OpenSiteExplorer with no avail. All I get is a messy spreadsheet. Does any of you happen to know the right settings for a nifty import? Thanks a lot 🙂
Moz Pro | | Ersatz0 -
Scrolling in OSE
Hi Guys, Is anyone else having issues when scrolling in OSE remembering what the column headers are? Just throwing out a suggestion for the next OSE update, could the column headers remain at the top of the links table when scrolling? This would be very useful especially on the top pages table which now has many columns! Thanks guys! Danni
Moz Pro | | MB070 -
Press Release - using moz bar/OSE is reading domain not page? How? Why?
A question posed by Christopher Glaeser from early today:low PA high DA, had a follow up response from him providing 2 urls from PR WEB for separate press releases: http://www.prweb.com/releases/2011/11/prweb8923419.htm (HP White) On moz bar Page Analysis/Link Data = PA - 47 DA - 36 http://www.prweb.com/releases/2011/12/prweb9051351.htm (Golfer's Advice) On moz bar Page Analysis/Link Data = PA - 1 DA - 96 I kept scratching my head as to how a press release of 6 weeks ago had garnered such attention from a company that would not seem to have a huge traffic due to more obscure product offering and scientific subject (Analyses of Armor Industry versus Golf Advice).
Moz Pro | | RobertFisher
Then I realized that for HP White, Link Data was not about the PR. The url from mozbar was HPWhite.com not the above, I did not notice until I used OSE where same thing was happening. When I cut and pasted the above press release url for HP White and placed it in OSE this changed: PA - 49 DA - 96 (2 links 2 linking root domains) For Golfers advice (0 links from 0 linking domains) Note to all: the links to the PR WEB release for the HP included a low end directory type link and a link from PR WEB (. For Golfer's Advice there was not a link back to the release from PR WEB: Note that Golfer's Advice is a newer release (6 weeks). So, any link from HPWhite release would equal more juice to HP White and PR Web and Vocus. Any link to Golfer's Advice from release offers......???? to Golfer's Advice and who cares to Vocus and PR Web. So, I guess this begs a couple of questions: Why the mozbar link analysis difference for one versus the other? Does PR Web treat some differently than others? Who benefits most from me paying a PR Web to do press releases for a client, PR Web and Vocus or my client and I???? I have tried to order the images to make sense: L to R top, then bottom is last. [](<a href=)" target="_blank">a> [
](<a href=)" target="_blank">a> [
](<a href=)" target="_blank">a> [
](<a href=)" target="_blank">a> [
](<a href=)" target="_blank">a> [
](<a href=)" target="_blank">a>
0 -
Problems with OSE downloads
Ordered 5 reports last 24 hours, none received. Anyone else with this problem ? I do expect better from an expensive subscription. C'mon Moz, fix this new OSE report system please.
Moz Pro | | blocker04082