How do I export all of my customers (not users) from Drupal into a CSV file?

Question

I'm working on integrating a new accounting system and crm (Xero & Capsule) for a client - and they have just under 10,000 clients - only stored in their Drupal website. I need to export these with all their details (e mail, full name, address etc) - into a csv or excel file.

Currently under Store Administration I can "View Customers" - but this gives me no option to export.

But under Reports > Customer Reports it gives me the option to export each page to a csv - however the information is not what we need - it only gives me usernames.

Any suggestions?

Answer 1

With the https://www.drupal.org/project/views_data_export module you can create views and export to CSV, Excel, XML, etc.

It also supports exporting in batches (default activated if the views has 1 000 or more rows). So 10 000 clients shouldn't be a problem.

Answer 2

Another module to consider for addressing these requirements is Forena.

You would use it's report writing features (such as its WYSIWYG report writer and/or it's query builder) to first create a report that fits your needs (e.g. to remedy your '... the information is not what we need - it only gives me usernames ...'). The result of it could be a tabular output and/or graph. After the report content matches the specifications,you could save it as (e.g.) a CSV file.

Here is a quote from one of the forena community docu pages, i.e. Reasons for considering Forena:

Reports created by Forena (combined with SVGGraph and mPDF) can be saved (exported) as PDF documents with embedded graphs. Other supported formats for saving a report are CSV, XLS, DOC, HTML, etc.

The Forena Video demos and tutorials contain quite some links to live demos.

Any report created with Forena can be saved (exported) in various file formats, such as CSV or XLS. To do so, just add .csv as an extention to the report (output) URL. That contains the CSV equivalent of that report. As a sample, consider the sample report located at /reports/sample.states (in your own site, one of the samples shipped with Forena). Change the URL to /reports/sample.states.csv (= just add .csv to it) to get the same report in .csv format. If you'd add such .csv to the URL of the Simple Table of States in the demo site, the result is like so ...

Same technique applies for other supported extensions (export formats), such as adding .html, .xls (MS Excell), .pdf or .doc (MS Word).

By the way, all data blocks are accessible directly via their url also, provided you have permission to access that block. Here are a few samples of that, for the sample report located at /reports/sample.states:

• in XML format (= demo with anonymous access).
• in JSON format (= demo with anonymous access).
• in XML format (= demo without anonymous access).
• in JSON format (= demo without anonymous access).

For short, to answer this specific answer I'd write the various (custom) SQLs I need, format it as reports that fit my needs, and then just save it as an XLS (or CSV?).

Disclosure: I'm a co-maintainer of Forena.