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
-
Unsolved Export to csv does not work
HI friends. For long time I cannot download csv reports for links. I need to export new spam links sort by spam score and compare to existing but export to csv button spins for ever and nothing happens
Moz Pro | | netcomsia0 -
Why doesn't OSE show results from sites like Wikipedia, YouTube, Twitter, etc.?
I know OSE used to provide link data from these domains. But I have been doing link profile lookups on sites that I know have links from these domains - and they don't show up in my results. Just to make sure, they don't even show up when I sort the sites by domain authority.
Moz Pro | | ProspectMX0 -
Are the CSV downloads malformatted, when a comma appears in a URL?
Howdy folks, we've been a PRO member for about 24 hours now and I have to say we're loving it! One problem I am having with however is a CSV exported from our crawl diagnostics summary that I've downloaded. The CSV contains all the data fine, however I am having problems with it when a URL contains a comma. I am making a little tool to work with the CSVs we download and I can't parse it properly because there sometimes URLs contain commas and aren't quoted the same as other fields, such as meta_description_tag, are. Is there something simple I'm missing or is it something that can be fixed? Looking forward to learn more about the various tools. Thanks for the help.
Moz Pro | | Safelincs0 -
Stats from Moz bar and OSE not changed over last three months - new update?
I have noticed for a few of my websites that the SEOMoz stats have not changed over the last three months; PR, MR and even total external links. Is both the tool bar and OSE going through further updates and not yet pulling through accurate results? Any advice on this would be much appreciated.
Moz Pro | | Adido-1053990 -
Open Site Explorer CSV reports
Hello, Small question about the Open Site Explorer. on August 24th I've been trying to create several CSV reports which I knew would take a few days. But now, almost a week later when I check Open Site Explorer > Recent CVS reports it tells me it's still saving data. It's been saving data for 4 days now. Am I missing something, is this a bug, do I have to do something or should I wait a little longer? Thanks in advance, Dennis Tappij Gielen Retail&Clicks JFwMt.jpg
Moz Pro | | RetailClicks0 -
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 -
Has anyone noticed slight drops in the Domain mozRank of their sites since the OSE update?
There's been no significant changes to their link profiles, so I'm thinking how mozRank is measured may have changed. Anyone else have this issue?
Moz Pro | | RiseSEO0