Large exports to Excel

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