Posted by m_brown on October 14, 2014 at 11:01pm
Good afternoon Alaska Drupal users,
Does anyone know how to export URL Aliases & URL redirects to a file? I've gone the long route before, by copy/pasting the results to an excel sheet. But, I found a few solutions via a quick Google search; however, the steps seem really complicated to setup or I just can't understand the lingo. Can someone translate this for me?! Pretty please! =) http://www.reddit.com/r/drupal/comments/1jb2uz/is_there_a_way_to_export_...
Thank you!
Comments
Is this so you can export
Is this so you can export them to another database?
What is the purpose of this file?
drush sql-dump --tables-list=url_alias > output.sql
Will export the url aliases so you can run
drush sqlc < output.sql
and import them else where.
If you just want a list of them you can run, but it wont be comma separated or have any formating.
drush sql-query "SELECT source, alias, language FROM url_alias;" > output
Or are you talking about patterns? Because if you don't have all the existing paths for your url aliases then you might break your site.
https://www.drupal.org/node/982818
Purpose of file
No, these will not be exported to another database.
Our purpose for exporting the files is to compare the URL alias list to the URL redirect list. So ideally an Excel or CSV file. Recently, we're been encountering an infinite loop error message. The only way we could query which pages have a URL redirect to itself was to go through the full URL alias list (60 pages), view each page and see if the error message displayed.
So, if there's a way to export those lists just to view and compare, a few simple filters in Excel could tell us the problem pages. Also, I'm not a developer, only have client admin access. What does "drush sql-query "SELECT source, alias, language FROM url_alias;" > output" mean?
hmm ok... Well that command
hmm ok... Well that command is something that would allow you export all of the url_alias table information from your database. Are you familiar with linux/ssh? If you have drush installed on your server you could run the following
drush sql-query "SELECT source, alias FROM url_alias;" | sed 's/\s+/,/g' > output.csv
And it would create a functional CSV (comma separated) file for you. It does this by calling the drush program then passes the flag "sql-query" with the argument "SELECT source, alias FROM url_alias;" which tells mysql to grab the source and alias from the table url_alias from you drupal install. It then pipes (| character) the results into sed where it removes duplicate spaces with a comma using a regular expression. This then outputs (> character) to the output.csv file
Edit: I just saw that you aren't a developer. If you have any linux or shell experience you should be able to run that command
About SSH
The ssh command is also available on the Mac, from the command line (mac, like linux has unix - like features and commands)
If one were to copy and paste the drush sql-query command I recommend copying it from the browser, as opposed to from an email. The reason is that the greater-than (output) symbol may be received in some emails as
ampersand, letter g, letter t, and a colon -
which would cause an error on the command line.
Drupal Noob (since 2012)
Coder since 1987 - Web Coder since 1996
akwebsoft.com - tj49.com
Exporting URL Redirects in a View
For posterity, here's a View I whipped up that exposes all URL redirects and also their destination as a path and a node. You can then easily copy the table into Excel and save as a CSV.
I couldn't get it to expose the Redirect code (301, 302 etc) so I've hardcoded them all as 301 via a custom text field.
$view = new view();
$view->name = 'redirects';
$view->description = 'Displays a list of redirects on user and admin pages.';
$view->tag = '';
$view->base_table = 'redirect';
$view->human_name = 'Redirects';
$view->core = 0;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially /
/ Display: Defaults /
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['access']['perm'] = 'administer redirects';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['pager']['options']['items_per_page'] = '50';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['pager']['options']['id'] = '0';
$handler->display->display_options['pager']['options']['quantity'] = '9';
$handler->display->display_options['pager']['options']['expose']['items_per_page'] = TRUE;
$handler->display->display_options['pager']['options']['expose']['items_per_page_options'] = '50, 100, 200, 500';
$handler->display->display_options['pager']['options']['expose']['items_per_page_options_all'] = TRUE;
$handler->display->display_options['style_plugin'] = 'table';
$handler->display->display_options['style_options']['columns'] = array(
'rid' => 'rid',
'source' => 'source',
'redirect' => 'redirect',
'redirect_1' => 'redirect_1',
'count' => 'count',
'access' => 'access',
);
$handler->display->display_options['style_options']['default'] = '-1';
$handler->display->display_options['style_options']['info'] = array(
'rid' => array(
'sortable' => 0,
'default_sort_order' => 'asc',
'align' => '',
'separator' => '',
'empty_column' => 0,
),
'source' => array(
'sortable' => 1,
'default_sort_order' => 'asc',
'align' => '',
'separator' => '',
'empty_column' => 0,
),
'redirect' => array(
'sortable' => 1,
'default_sort_order' => 'asc',
'align' => '',
'separator' => '',
'empty_column' => 0,
),
'redirect_1' => array(
'sortable' => 1,
'default_sort_order' => 'asc',
'align' => '',
'separator' => '',
'empty_column' => 0,
),
'count' => array(
'sortable' => 1,
'default_sort_order' => 'asc',
'align' => '',
'separator' => '',
'empty_column' => 0,
),
'access' => array(
'sortable' => 1,
'default_sort_order' => 'asc',
'align' => '',
'separator' => '',
'empty_column' => 0,
),
);
$handler->display->display_options['style_options']['sticky'] = TRUE;
/ Header: Global: Text area /
$handler->display->display_options['header']['area']['id'] = 'area';
$handler->display->display_options['header']['area']['table'] = 'views';
$handler->display->display_options['header']['area']['field'] = 'area';
$handler->display->display_options['header']['area']['label'] = 'To export the URL Redirects...';
$handler->display->display_options['header']['area']['empty'] = TRUE;
$handler->display->display_options['header']['area']['content'] = '<p>While Drupal allows exporting URL redirects, it doesn\'t give control over how the redirect path is shown, hence why this View was created. </p>
<p>This View uses some jQuery magic in the View Footer to change the \'Redirect alias/path\' column from the \'/node/1234\' path to that link\'s \'href\' value, exposing the actual URL destination. The node ID is kept in another column for completeness. </p>
<p>The Redirect Code column is added as custom text in the View, it\'s not actually retrieving the real redirect code. For some reason, that isn\'t available to the View...</p>
<p><strong>To export all this information, copy the entire table into an empty Excel document, and save it as a CSV. </strong></p>';
$handler->display->display_options['header']['area']['format'] = 'rich_text';
/ Header: Global: Result summary /
$handler->display->display_options['header']['result']['id'] = 'result';
$handler->display->display_options['header']['result']['table'] = 'views';
$handler->display->display_options['header']['result']['field'] = 'result';
$handler->display->display_options['header']['result']['content'] = '<hr>
Displaying @start - @end of @total';
/ Footer: Global: Text area /
$handler->display->display_options['footer']['area']['id'] = 'area';
$handler->display->display_options['footer']['area']['table'] = 'views';
$handler->display->display_options['footer']['area']['field'] = 'area';
$handler->display->display_options['footer']['area']['label'] = 'JavaScript to add redirect path';
$handler->display->display_options['footer']['area']['content'] = '<script type="text/javascript">
jQuery(\'tr td.views-field-redirect a\').each(function () {
jQuery(this).text(jQuery(this).attr(\'href\'));
});
</script>';
$handler->display->display_options['footer']['area']['format'] = 'admin_html';
/ No results behavior: Global: Text area /
$handler->display->display_options['empty']['area']['id'] = 'area';
$handler->display->display_options['empty']['area']['table'] = 'views';
$handler->display->display_options['empty']['area']['field'] = 'area';
$handler->display->display_options['empty']['area']['content'] = 'No URL redirects found.';
$handler->display->display_options['empty']['area']['format'] = '1';
/ Field: Redirect: Redirect ID /
$handler->display->display_options['fields']['rid']['id'] = 'rid';
$handler->display->display_options['fields']['rid']['table'] = 'redirect';
$handler->display->display_options['fields']['rid']['field'] = 'rid';
/ Field: Redirect: Source URL /
$handler->display->display_options['fields']['source']['id'] = 'source';
$handler->display->display_options['fields']['source']['table'] = 'redirect';
$handler->display->display_options['fields']['source']['field'] = 'source';
$handler->display->display_options['fields']['source']['absolute'] = 0;
/ Field: Redirect: Redirect URL /
$handler->display->display_options['fields']['redirect']['id'] = 'redirect';
$handler->display->display_options['fields']['redirect']['table'] = 'redirect';
$handler->display->display_options['fields']['redirect']['field'] = 'redirect';
$handler->display->display_options['fields']['redirect']['label'] = 'Redirect alias/path';
$handler->display->display_options['fields']['redirect']['alter']['path'] = 'node';
$handler->display->display_options['fields']['redirect']['alter']['absolute'] = TRUE;
$handler->display->display_options['fields']['redirect']['absolute'] = 0;
/ Field: Redirect: Redirect URL /
$handler->display->display_options['fields']['redirect_1']['id'] = 'redirect_1';
$handler->display->display_options['fields']['redirect_1']['table'] = 'redirect';
$handler->display->display_options['fields']['redirect_1']['field'] = 'redirect';
$handler->display->display_options['fields']['redirect_1']['label'] = 'Redirect URL nodeID';
$handler->display->display_options['fields']['redirect_1']['absolute'] = 0;
/ Field: Redirect: Clicks /
$handler->display->display_options['fields']['count']['id'] = 'count';
$handler->display->display_options['fields']['count']['table'] = 'redirect';
$handler->display->display_options['fields']['count']['field'] = 'count';
/ Field: Redirect: Last accessed date /
$handler->display->display_options['fields']['access']['id'] = 'access';
$handler->display->display_options['fields']['access']['table'] = 'redirect';
$handler->display->display_options['fields']['access']['field'] = 'access';
$handler->display->display_options['fields']['access']['label'] = 'Last accessed';
$handler->display->display_options['fields']['access']['empty'] = 'Never';
$handler->display->display_options['fields']['access']['empty_zero'] = TRUE;
$handler->display->display_options['fields']['access']['date_format'] = 'time ago';
/ Field: Global: Custom text /
$handler->display->display_options['fields']['nothing']['id'] = 'nothing';
$handler->display->display_options['fields']['nothing']['table'] = 'views';
$handler->display->display_options['fields']['nothing']['field'] = 'nothing';
$handler->display->display_options['fields']['nothing']['label'] = 'Redirect code';
$handler->display->display_options['fields']['nothing']['alter']['text'] = '301';
/ Filter criterion: Redirect: Type /
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'redirect';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
'redirect' => 'redirect',
);
/ Display: Page: User redirects /
$handler = $view->new_display('page', 'Page: User redirects', 'page_user');
$handler->display->display_options['defaults']['hide_admin_links'] = FALSE;
$handler->display->display_options['defaults']['arguments'] = FALSE;
/ Contextual filter: Redirect: User ID /
$handler->display->display_options['arguments']['uid']['id'] = 'uid';
$handler->display->display_options['arguments']['uid']['table'] = 'redirect';
$handler->display->display_options['arguments']['uid']['field'] = 'uid';
$handler->display->display_options['arguments']['uid']['default_action'] = 'default';
$handler->display->display_options['arguments']['uid']['default_argument_type'] = 'user';
$handler->display->display_options['arguments']['uid']['default_argument_options']['user'] = FALSE;
$handler->display->display_options['arguments']['uid']['summary']['format'] = 'default_summary';
$handler->display->display_options['path'] = 'user/%/redirects';
$handler->display->display_options['menu']['type'] = 'tab';
$handler->display->display_options['menu']['title'] = 'Redirects';
$handler->display->display_options['menu']['weight'] = '0';
/ Display: Page: Admin redirects */
$handler = $view->new_display('page', 'Page: Admin redirects', 'page_admin');
$handler->display->display_options['defaults']['hide_admin_links'] = FALSE;
$handler->display->display_options['path'] = 'admin/config/search/redirect/list';
$handler->display->display_options['menu']['type'] = 'default tab';
$handler->display->display_options['menu']['title'] = 'List';
$handler->display->display_options['menu']['weight'] = '0';
$handler->display->display_options['menu']['name'] = 'main-menu';
$handler->display->display_options['tab_options']['weight'] = '0';
@timfletcher Thanks for the
@timfletcher Thanks for the share. Worked perfectly.
Hi, Using drupal 7 with this
Hi,
Using drupal 7 with this view, I got following errors:
Warning: Creating default object from empty value in views_db_object->new_display() (regel 2514 van /sites/all/modules/views/includes/view.inc).Notice: Undefined property: stdClass::$display_plugin in view->init_display() (regel 500 van /sites/all/modules/views/includes/view.inc).
Notice: Undefined property: stdClass::$display_plugin in views_ui_import_validate() (regel 2202 van /sites/all/modules/views/includes/admin.inc).
Weergave-plugin is niet beschikbaar.
Overzicht importeren niet mogelijk.
Not possible to import the view.
greetings, Martijn