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
-
How can i export a historical ranking report which contains keywords with special characters.
How can i export a historical ranking report which contains keywords with special characters? Previously it only turned the keywords into jumbled format (forgot the tech term) in excel i.e. онлайн чат which are in this case russian characters. The way i got around this was to import it into google docs but that also is now converting it into this format. Due to this i 1 do not know what the keywords are and 2 all my formulas do not work.
Moz Pro | | ColumK0 -
MozBar & OSE
I'm doing some research for an e-commerce store, and I'm getting very different link counts from the Mozbar & Opensite explorer. Any idea why? Which is more accurate? wasqL&DGCBV wasqL&DGCBV#1
Moz Pro | | chris.kent0 -
How can I export from Followerwonk into a Twitter List?
I use Hootsuite but can't see anywhere in there or find any other tools to import a text file of Twitter usernames into a Twitter list. Does anyone know how?
Moz Pro | | benners0 -
Using Keyword Difficulty Report along with OSE to calculate SEO Pricing?
Has anyone thought of or created a algorithm or Excel function that uses the results from the Keyword Difficulty Report percentage of difficulty and SERP ranking analysis set against potential client's own OSE rankings to come up with an estimated SEO pricing tool?
Moz Pro | | easystreetint0 -
Over 90% of anchor text tends to be brand-name on OSE link profiles. Why?
I reported this as a bug in OSE, because often I explore these links and find that the pages include both a brand-name link AND a regular keyword link, but for some reason OSE was only reporting the brand-name link... This led me to wonder how many links this occurred for, and therefore whether or not to trust the fact that the majority of the sites I ran OSE on returned at least (in most cases, more) than 90% brand-name links. I understand that brand-name links are amongst the most important to obtain, but that it's also important to get anchor text for keywords to build a varied profile. Given this apparent flaw in OSE, is it wrong - in the case of very successful sites - to take this ~90% as being anywhere near the correct percentage of brand-name links that I should be aiming for as a proportion of the total profile? Extra Credit :)... And this may help potentially help resolve the issue: does "Inbound Links" tab in OSE just report links to the Root Domain, or to that and every other page on the site?
Moz Pro | | ZakGottlieb710 -
Impact of 301-redirected domains on OSE Metrics
When looking at the OSE metrics (DA, PA, Number Linking RDs etc.) is it purely based on OSEs evaluation of the specific domain or will it take into account links that have been 301-redirected to the domain?
Moz Pro | | bjalc20110 -
Exporting .csv
I love all the data Roger gives me if I ask him politely. It's awesome to turn that data into a nice looking Excel file for analysis. There is however one situation that gets me into trouble. When I export CSV, open it in Excel and convert text to columns (seperated by comma) and e.g. a Page Title contains a comma (which often happens); my file seperation is messed up. Anyone got some tips to handle that? Thanks in advance mozzers
Moz Pro | | Partouter0 -
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