How do I export the list of URL alias into an excel or csv?

Question

I am beginner and have recently started to work on Drupal website that has been implemented at my work place.

I would like to export the list of URL alias to an excel or csv file so that we can look at the various url aliases that we have set and eliminate or update those that aren't required.

How can I achieve this?

Answer 1

This is easy with a query:

select source, alias from url_alias INTO OUTFILE '/tmp/url_aliases.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

If you are good with mysql, you can run this directly from mysql with your drupal database selected.

If you are using drush you can run the query this way from the command line when in your site's root directory:

drush sqlq "select source, alias from url_alias INTO OUTFILE '/tmp/url_aliases.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';"

If you have the developer module installed, you can run this with db_query in /devel/php:

db_query("select source, alias from url_alias INTO OUTFILE '/tmp/url_aliases.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';");

You can also run the above code anywhere in the code base that runs on page execution, and then remove it once you have gotten your csv.

Answer 2

A more simpler method would be to use a tool like HeidiSQL or SequelPro sql clients, connect to your database, open the url_alias table and then do File > Export or in SequelPro, there is a gear icon at the bottom of the table. You can click on it and hit "Export to CSV".

• HeidiSQL
• SequelPro

Answer 3

To further expand oknate's answer, yet another solution is to use Forena (disclosure: I'm a co-maintainer). Below are the required steps, which typically takes a few minutes (at max) to complete.

Step 1: Create a data block

• Navigate to admin/structure/forena/data/add.
• Select the desired "Data Source" (eg: Custom Drupal Reports), and enter the machine name of the block to be created (eg path_aliases, no need to specify the .sql suffix).
• Enter your SQL, in this case all that's needed is select source, alias from url_alias, and optionally (within "Advanced"), enter a value for the data security you want to apply to this data block, eg: access administration pages.

• After saving this, an SQL file will be saved in something like files/private/forena/datablocks/path_aliases.sql, with a content that looks like so:

  --ACCESS=access administration pages

  select source, alias from url_alias

Step 2: Create a report

• Create a new report, by navigating to reports/add.
• Enter some values for the report to be created, ie:
  • Report title (required), e.g. "Path aliases".
  • Report name (required), e.g. path_aliases.
  • Report category (optional, to group various reports together, e.g. "Demo").
• After hitting the Create button, the WYSIWYG report editor shows up. For the purpose of this report, IMO it's sufficient to use these options in it:
  • Use the +Data button to select the data block created in Step 1. Note how it already shows a preview of the data contained in that data block. Use the Select button to move on to the next part of adding a data block in a report: select "Simple Table" as the report template (no need for another template such as an SVG Graph or CrossTab table in this case). Specify the "Text format" (within Setting), e.g. use "Full HTML". For this case just accept the defaults for all other facilities on this screen and use the Add button to complete adding this data block to your report.
  • Use the tab Document Types to indicate that you want to enable the CSV option (so that anybody who browses this report, can use the link "CSV" to export the report data to a CSV file).
  • Use the Save button to save the specifications of your report. After doing so, you will be redirected to a path to browse your actual report, and which should include an hyperlink (upper right) to export your report data to a "CSV".

Sample report

After you complete the steps as above, a so called .frx file will be saved somewhere in your files directory, which contains the entire report configuration (= specification). Its actual location depends on how you configured your report specification directory.

Here is a sample of how the path_aliases.frx file for the above example looks like:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE root [
<!ENTITY nbsp "&#160;">
]>
<html xmlns:frx="urn:FrxReports">
<head>
<title>Path aliases</title>
<frx:category>Demo</frx:category>
<frx:options/>
<frx:parameters>
</frx:parameters>
<frx:docgen>
  <frx:doc type="csv"/>
</frx:docgen>
<frx:fields>
</frx:fields>
<frx:menu/>
<frx:cache/>
<style/>
</head>
<body>
<div id="path_aliases_block" class="FrxTable" frx:block="drupal_cust/path_aliases">
    <table>
      <thead>
        <tr>
          <th>source</th>
          <th>alias</th>
        </tr>
      </thead>
      <tbody>
        <tr id="path_aliases" frx:foreach="*">
          <td>{source}</td>
          <td>{alias}</td>
        </tr>
      </tbody>
    </table>
</div>
</body>
</html>

Note that everything above was created using a WYSIWYG report editor (no custom coding, etc). And anybody with the relevant permissions (to design reports, and/or to create data blocks, or both) can use this.

If you want to deploy it to another site, just copy/transfer the content of the above .sql (from Step 1) and .frx (from Step 2) to the appropriate files subfolder in that other site.

Answer 4

A really simple way : Use phpMyAdmin. Select the table url_alias. Click export tab. You can choose format : sql, CSV, CSV for Excel, etc.