Help with Email Address Exporter

Hi,
I would like export all customer information,
name, email, address with postcode and phone number.

I have had a good search around the forums and had play with admin/email_export.php but can't get anything to show up in the file when downloaded using csv apart from just the column headings.

Can anyone point me in the right direction please?

Woody, Does it do this whatever audience you select from the dropdown?

Chuckl:

Woody, Does it do this whatever audience you select from the dropdown?

yeah I did try different audience but was still the same.

Very strange, it's not the most complex of mods...
Only things I can suggest are to check the query builder table in the database. I have had problems in the past with soft carriage returns in the table fields, or try a fresh copy of the files.

Looking in all the other files for Email Address Exporter, admin/email_export.php is the only file I think I need to add anything to.

I have added

            $exporter_output .=  $FIELDSEPARATOR;
            $exporter_output .=  $FIELDSTART . "customers_telephone".$FIELDEND; 

So it looks like

        // add column headers if CSV or HTML format
          if ($format == "CSV" || $format == "HTML") {
            $exporter_output .=  $LINESTART;
            $exporter_output .=  $FIELDSTART . "customers_email_address".$FIELDEND;
            $exporter_output .=  $FIELDSEPARATOR;
            $exporter_output .=  $FIELDSTART . "customers_firstname".$FIELDEND;
            $exporter_output .=  $FIELDSEPARATOR;
            $exporter_output .=  $FIELDSTART . "customers_lastname".$FIELDEND;
            $exporter_output .=  $FIELDSEPARATOR;
            $exporter_output .=  $FIELDSTART . "customers_telephone".$FIELDEND;        
            $exporter_output .=  $LINEBREAK;
          }

Now I think that should work to export the telephone number BUT it don't. It creates a column but no data.

Is there something else I need to add? where? and I can't find customers address field in the database, only customers_default_address_id? where is the postcode?

Thanks for your reply

Woody,
You are correct, what you have added IS the column header. if you look a bit further down the file, you will find the section where the actual data is added, and you need to add

$exporter_output .= $FIELDSEPARATOR;

$exporter_output .= $FIELDSTART.$audience->fields['customers_telephone'].$FIELDEND;

in the non XML section

Chuckl:

Woody,
You are correct, what you have added IS the column header. if you look a bit further down the file, you will find the section where the actual data is added, and you need to add

$exporter_output .= $FIELDSEPARATOR;

$exporter_output .= $FIELDSTART.$audience->fields['customers_telephone'].$FIELDEND;

in the non XML section

Yeah I did try that and the xml above it that never worked.

My apologies Woody, I finally understand the question you were actually asking...

When you select the audience for you export from the dropdown, you are actually selecting a predefined SQL query from the query builder table to select a set of data from the database tables.
In your case you want some data that is not contained in any existing entry, which means you need to insert an entry into the query_builder table that will return that data.
i.e. upload a SQL patch from admin that will create a new query, something like this:

insert into query_builder (query_category, query_name, query_description, query_string, query_keys_list) VALUES ('newsletters','My Address Export Query','Special Query for Customer Data Export','select c.customers_telephone, c.customers_email_address, \nc.customers_lastname, c.customers_firstname, o.entry_street_address as customers_street_address, o.entry_suburb as customers_suburb,o.entry_city as customers_city, o.entry_state as customers_state, o.entry_postcode as customers_postcode from TABLE_ADDRESS_BOOK o, TABLE_CUSTOMERS c where c.customers_newsletter = 1 AND c.customers_id = \no.customers_id GROUP \nBY c.customers_email_address order by c.customers_lastname, \nc.customers_firstname ASC','');

This will then give you a new entry in the dropdown list, which will return the telephone, address and postcode fields you were looking for, and you can then modify the export as necessary.

Rgds,

Chuck

Thanks Chuck I will give that a try, thanks for the help.

I didn't have any luck with that SQL patch, I got the extra dropdown box but it never exported any extra data. I tested all the formats.

1. A slight alteration to Chuckl's new query to show "all" customer records, not just newsletter-subscribers:```
   insert into query_builder (query_category, query_name, query_description, query_string, query_keys_list) VALUES ('newsletters','My Address Export Query - All custs with phone number included','Special Query for Customer Data Export','select c.customers_telephone, c.customers_email_address, \nc.customers_lastname, c.customers_firstname, o.entry_street_address as customers_street_address, o.entry_suburb as customers_suburb,o.entry_city as customers_city, o.entry_state as customers_state, o.entry_postcode as customers_postcode from TABLE_ADDRESS_BOOK o, TABLE_CUSTOMERS c where c.customers_id = \no.customers_id GROUP \nBY c.customers_email_address order by c.customers_lastname, \nc.customers_firstname ASC','');

2. Code edits:
a) add the customers_telephone column-heading here:```
          if ($format == "CSV" || $format == "HTML") {
            $exporter_output .=  $LINESTART;
            $exporter_output .=  $FIELDSTART . "customers_email_address".$FIELDEND;
            $exporter_output .=  $FIELDSEPARATOR;
            $exporter_output .=  $FIELDSTART . "customers_firstname".$FIELDEND;
            $exporter_output .=  $FIELDSEPARATOR;
            $exporter_output .=  $FIELDSTART . "customers_lastname".$FIELDEND;
[B]            $exporter_output .=  $FIELDSEPARATOR;
            $exporter_output .=  $FIELDSTART . "customers_telephone".$FIELDEND;[/B]
            $exporter_output .=  $LINEBREAK;
          }

b) add the customers_telephone data output here:```
if ($format=="XML") {
$exporter_output .= "<address_book>\n";
$exporter_output .= " <contact>\n";
$exporter_output .= " <firstname>" . $audience->fields['customers_firstname'] . "</firstname>\n";
$exporter_output .= " <lastname>" .$audience->fields['customers_lastname'] . "</lastname>\n";
$exporter_output .= " <email_address>" . $audience->fields['customers_email_address'] . "</email_address>\n";
[B] $exporter_output .= " <telephone>" .$audience->fields['customers_telephone'] . "</telephone>\n";[/B]
$exporter_output .= " </contact>\n";
} else { // output non-XML data-format
$exporter_output .= $LINESTART;
$exporter_output .= $FIELDSTART . $audience->fields['customers_email_address'].$FIELDEND;
$exporter_output .= $FIELDSEPARATOR;
$exporter_output .= $FIELDSTART . $audience->fields['customers_firstname'].$FIELDEND;
$exporter_output .= $FIELDSEPARATOR;
$exporter_output .= $FIELDSTART . $audience->fields['customers_lastname'].$FIELDEND;
[B] $exporter_output .= $FIELDSEPARATOR;
$exporter_output .= $FIELDSTART . $audience->fields['customers_telephone'].$FIELDEND;[/B]
$exporter_output .= $LINEBREAK;
}

Beat me to it, Dr. B, I was going to say replace the /admin/email_export.php with the attached, after making a backup copy of the original.

Great work, I have what I need now, with just to problems to sort out.
Dr.B I got just the telephone number, no address.
I did put the SQL query in a clean Database.

Chuck I used the SQL query

insert into query_builder (query_category, query_name, query_description, query_string, query_keys_list) VALUES ('newsletters','My Address Export Query','Special Query for Customer Data Export','select c.customers_telephone, c.customers_email_address, \nc.customers_lastname, c.customers_firstname, o.entry_street_address as customers_street_address, o.entry_suburb as customers_suburb,o.entry_city as customers_city, o.entry_state as customers_state, o.entry_postcode as customers_postcode from TABLE_ADDRESS_BOOK o, TABLE_CUSTOMERS c where c.customers_newsletter = 1 AND c.customers_id = \no.customers_id GROUP \nBY c.customers_email_address order by c.customers_lastname, \nc.customers_firstname ASC','');

And the new file attached, the two things I now need to sort out is all data is on 1 row, Not each customer on a new column. And the telephone number is missing the first '0'. It's 1733 should be 01733.

Customers details from Dr.B input came on each column, but the telephone was still missing the '0'.

I'm sure it's something simple which tells to put each customer on a new on column but it's still beyond me.

Thank you for your help again the support here is great.

Always a pleasure Woody, regarding the 'all in 1 line, in the middle of the file is a line something like

$audience->MoveNext();

with 2 curly brackets above it.

Add a a new line between the 2 brackets, with

$exporter_output .= $LINEBREAK;

The telephone number '0' will require a

"0".

or similar inserted after the equals sign in the customer_telephone entry about 10 lines above that

Rgds,

Chuck

Excellent got it on each line now, but the '0' gave back strange results.
After adding "0". I get this as telephone number
0"01733******"
lol now I got an extra 0 and two " , tried removing the 0 leaving "". lol that went back the same as before.

How many times can one say thanks.

Ouch, of course, field separators in csv are double quotes. I speak PHP as about a 30th language, so mebbe single quotes? As in

'0'.

Otherwise you'll need one of the people who use php a little more than I do.

If you replace:

$exporter_output .=  "    <telephone>" .$audience->fields['customers_telephone'] . "</telephone>\n";

with:

$exporter_output .=  "    <telephone>" .zen_db_prepare_input(strip_tags($audience->fields['customers_telephone'])) . "</telephone>\n";

and:

$exporter_output .=  $FIELDSTART . $audience->fields['customers_telephone'].$FIELDEND;

with:

$exporter_output .=  $FIELDSTART . zen_db_prepare_input(strip_tags($audience->fields['customers_telephone'])).$FIELDEND;

Will the error still show up ?

Chuckl:

Ouch, of course, field separators in csv are double quotes. I speak PHP as about a 30th language, so mebbe single quotes? As in

'0'.

Otherwise you'll need one of the people who use php a little more than I do.

I did try that but it still came out like post 15

TheOracle:

If you replace:

$exporter_output .= " <telephone>" .$audience->fields['customers_telephone'] . "</telephone>\n";

> 
> ```php
$exporter_output .=  "    <telephone>" .zen_db_prepare_input(strip_tags($audience->fields['customers_telephone'])) . "</telephone>\n";
```and:
> 
> ```php
$exporter_output .=  $FIELDSTART . $audience->fields['customers_telephone'].$FIELDEND;
```with:
> 
> ```php
$exporter_output .=  $FIELDSTART . zen_db_prepare_input(strip_tags($audience->fields['customers_telephone'])).$FIELDEND;
```Will the error still show up ?

Thanks for your help, that first 0 still never showed up.

The code I supplied shows 01733 just fine.
Are you looking at the output in a text editor?
If you're opening a .csv file in Excel or some spreadsheet program, it will likely drop the preceeding '0' if there are no non-numeric characters in the phone number, treating it as a numeric value instead of the text string that it is meant to be.

DrByte:

The code I supplied shows 01733 just fine.
Are you looking at the output in a text editor?
If you're opening a .csv file in Excel or some spreadsheet program, it will likely drop the preceeding '0' if there are no non-numeric characters in the phone number, treating it as a numeric value instead of the text string that it is meant to be.

I am opening a .csv file in Excel 2007, and your right it is dropping the '0'.
I just changed one number to 21733 and that showed fine.

Ok now just just tested an import into outlook and everything is working fine.

Great stuff thanks people.