Large exports to Excel

amariussi · 21 April 2017 17:14

In oder to export everything correctly you must have knowledge of the database schema.

The following is an example query to export contacts including custom fields to a csv file.

select * from contacts inner join contacts_cstm on contacts.id = contacts_cstm.id_c INTO OUTFILE 'c:/contacts.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

This method has some drawbacks:
. the query doesn’t add the headers so you have to do it manaully or through another query
. if the output file (in my example it is: contacts.csv) already exists you will get an error so make sure you delete it before executing the query
. my example query only considers the table contacts and contacts_cstm (custom fields) but it doesn’t take into account other information such as email addresses, which are stored in a different table and other information related into other tables
. to make it complete you will have to work out a more complex query, given the complexity of the database I don’t think it will be easy in one single query

Additional info
In order to be able to run this query I have had to comment out by prepending a semi-colon the line with secure_file_priv= in my.ini

Comment:
It will be probably easier if you write a php script that loops through the database and collects all information from the necessary tables and then appends to a csv file. It will be quite long to execute but you can do it in different runs

Integrityteks · 21 April 2017 17:43

I ran the query (copy & pasted the code) and got the following error:
MySQL returned an empty result set (i.e. zero rows). (Query took 0.0013 seconds.)

Any idea what went wrong?

Thanks again

pgr · 21 April 2017 17:47

You can start with just this part of the query to make it simpler and show results inside phpMyAdmin:

select * from contacts inner join contacts_cstm on contacts.id = contacts_cstm.id_c

But 0 rows… did you start by clicking the suitecrm database, then clicking “SQL” and entering the query?

You do have Contacts on your database, right? And you do have a contacts_cstm table?

Integrityteks · 21 April 2017 17:58

I ran the query after selecting suitecrm then selecting accounts

actually it’s the Accounts database that I want to export not the contacts - so I’ve updated the query to:
SELECT * FROM accounts inner join accounts_cstm.id_c into OUTFILE ‘c:/accounts.csv’ fields terminated by ‘,’ enclosed by ‘"’ lines TERMINATED by ‘\n’;

now I get error:
#1146 - Table ‘accounts_cstm.id_c’ doesn’t exist

pgr · 21 April 2017 21:01

You didn’t update the query, you broke it and you dropped entire blocks of it.

Just pay attention to what you’re doing, and replace “contacts” with “accounts” and keep the rest!

amariussi · 21 April 2017 21:57

try first this:

select * from accounts inner join accounts_cstm on accounts.id = accounts_cstm.id_c;

If that works then try this:

select * from accounts inner join accounts_cstm on accounts.id = accounts_cstm.id_c INTO OUTFILE 'c:/accounts.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

just copy and paste this code withouth modifying it

Before you run it make sure that the file accounts.csv is not in c:\

If you see 0 records it may be due to the fact that the result is redirected to a file.

Once done check the c:\ folder for the file accounts.csv

amariussi · 22 April 2017 13:03

I have elaborated further the query in order to include all email addresses in one semi-colon separated field.

This is the query:

select accounts.*, accounts_cstm.*, GROUP_CONCAT(email_addresses.email_address SEPARATOR ';') AS all_email_addresses from accounts
 inner join accounts_cstm on accounts.id = accounts_cstm.id_c
 inner join email_addr_bean_rel on email_addr_bean_rel.bean_id = accounts.id
 inner join email_addresses on email_addr_bean_rel.email_address_id = email_addresses.id
GROUP BY accounts.id

So, to get your csv file:

select accounts.*, accounts_cstm.*, GROUP_CONCAT(email_addresses.email_address SEPARATOR ';') AS all_email_addresses from accounts
 inner join accounts_cstm on accounts.id = accounts_cstm.id_c
 inner join email_addr_bean_rel on email_addr_bean_rel.bean_id = accounts.id
 inner join email_addresses on email_addr_bean_rel.email_address_id = email_addresses.id
GROUP BY accounts.id
INTO OUTFILE 'c:/accounts_with_emails.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

NOTE:
phpMyAdmin will say that 0 lines have been processed but you will get the full result in the file c:/accounts_with_emails.csv

If you elaborate further the query you can include more information on the email addresses or, going even further, more information from other relationships.

PS: I haven’t tested thoroughly these queries, but they seemed to work fine. I recommend that you test them before relying on them!

amariussi · 22 April 2017 13:16

I have elaborated further the query in order to include all email addresses in one semi-colon separated field.

This is the query:

select accounts.*, accounts_cstm.*, GROUP_CONCAT(email_addresses.email_address SEPARATOR ';') AS all_email_addresses from accounts
 inner join accounts_cstm on accounts.id = accounts_cstm.id_c
 inner join email_addr_bean_rel on email_addr_bean_rel.bean_id = accounts.id
 inner join email_addresses on email_addr_bean_rel.email_address_id = email_addresses.id
GROUP BY accounts.id

So, to get your csv file:

select accounts.*, accounts_cstm.*, GROUP_CONCAT(email_addresses.email_address SEPARATOR ';') AS all_email_addresses from accounts
 inner join accounts_cstm on accounts.id = accounts_cstm.id_c
 inner join email_addr_bean_rel on email_addr_bean_rel.bean_id = accounts.id
 inner join email_addresses on email_addr_bean_rel.email_address_id = email_addresses.id
GROUP BY accounts.id
INTO OUTFILE 'c:/accounts_with_emails.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

With the following query, in addition to all emails you also get opt_out and invalid_email in:

select accounts.*, accounts_cstm.*, GROUP_CONCAT(CONCAT(email_addresses.email_address, ':', email_addresses.invalid_email, ':', email_addresses.opt_out) SEPARATOR ';') AS all_email_addresses from accounts
 inner join accounts_cstm on accounts.id = accounts_cstm.id_c
 inner join email_addr_bean_rel on email_addr_bean_rel.bean_id = accounts.id
 inner join email_addresses on email_addr_bean_rel.email_address_id = email_addresses.id
GROUP BY accounts.id

(just replace the part before INTO to have the results in the file)

NOTE:
phpMyAdmin will say that 0 lines have been processed but you will get the full result in the file c:/accounts_with_emails.csv

If you elaborate further the query you can include more information on the email addresses (eg primary vs secondary) or, going even further, more information from other relationships.

PS: I haven’t tested thoroughly these queries, but they seemed to work fine. I recommend that you test them before relying on them!

amariussi · 22 April 2017 14:24

With the following query you also get primary and reply_to:

SELECT accounts.*, accounts_cstm.*, GROUP_CONCAT(CONCAT(email_addresses.email_address, ':', email_addr_bean_rel.primary_address, ':', email_addr_bean_rel.reply_to_address, ':', email_addresses.invalid_email, ':', email_addresses.opt_out) SEPARATOR ';') AS all_email_addresses
FROM accounts
 INNER JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c
 INNER JOIN email_addr_bean_rel ON email_addr_bean_rel.bean_id = accounts.id
 INNER JOIN email_addresses ON email_addr_bean_rel.email_address_id = email_addresses.id
GROUP BY accounts.id
INTO OUTFILE 'c:/accounts_full.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'  ESCAPED BY '\\'
LINES TERMINATED BY '\n'

Integrityteks · 22 April 2017 14:37

Thank you so much for writing thuis code for me - I’ll try it out and let you know the results.

Thanks again

David

Integrityteks · 22 April 2017 14:49

SQL error again
tried removing the email addresses from the query and still getting errors with no output

all I need is the Accounts database to include custom fields - don’t have email addresses to query on - hopefully with first line being the headers

Is there no easier way to extract this data at all??? there has to be a better way, really

I tried the code below to keep it as simple as possible but get an error to check the docs for my MARIADB server version for correct syntax to use near “INNER JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c”

Code used:
SELECT accounts., accounts_cstm.,
INNER JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c
INTO OUTFILE ‘c:/accounts_full.csv’
FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’ ESCAPED BY ‘\’
LINES TERMINATED BY ‘\n’

amariussi · 22 April 2017 14:51

What error do you get?

Please paste it here

pgr · 22 April 2017 14:57

You removed the FROM clause, put it back in…
You have an extra comma before “INNER JOIN” that you need to remove…

amariussi · 22 April 2017 15:05

To get the list of fields:

SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ',')
    FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME in ('accounts' , 'accounts_cstm')
INTO OUTFILE 'c:/accounts_headers.csv'

Integrityteks · 22 April 2017 15:07

I got th eheaders awesome!! Thanks

Now I’m lost with regards to the code to get the data - I have the following code:

SELECT from accounts., accounts_cstm.
INNER JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c
INTO OUTFILE ‘c:/accountsfull.csv’
FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’ ESCAPED BY ‘\’
LINES TERMINATED BY ‘\n’

error is syntax to use near ‘from accounts., accounts_cstm. LIMIT 0, 25’ at lkine 1

amariussi · 22 April 2017 15:13

I have modified my post with the headers. With the new query they all go in one line

With respect to your query you have put the FROM in front of the fields and you have omitted the table name.

You can paste the following:

SELECT * FROM accounts
INNER JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c
INTO OUTFILE 'c:/accountsfull.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'

amariussi · 22 April 2017 15:25

Sorry I did a mistake copying and pasting. Now I have corrected the two queries but, here they are again:

For the headers:

SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ',')
    FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME in ('accounts' , 'accounts_cstm')
INTO OUTFILE 'c:/accounts_headers.csv'

For the records:

select *
from accounts
inner join accounts_cstm
on accounts.id = accounts_cstm.id_c
INTO OUTFILE 'c:/accounts_full.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'

amariussi · 22 April 2017 15:54

Have you managed?

Integrityteks · 22 April 2017 16:15

yes thanks so much - I finally got it to work with getting headers and the data - just need to work out the field names for the database but that’s all good - thanks again!

amariussi · 22 April 2017 18:09

Just for completeness:
you may have to increase the value of the MySQL environment variable group_concat_max_len (the default is only 1024 and may not be sufficient for this query)

To do so there are two options:

do it in pjpMyAdmin just before the sql query:

SET group_concat_max_len = 4096;

(or to higher number if necessary)

or in the file my.ini:

group_concat_max_len = 4096