Help with Email Address Exporter

[DrByte]

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;
            $exporter_output .=  $FIELDSEPARATOR;
            $exporter_output .=  $FIELDSTART . "customers_telephone".$FIELDEND;
            $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";
            $exporter_output .=  "    <telephone>" .$audience->fields['customers_telephone'] . "</telephone>\n";
            $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;
            $exporter_output .=  $FIELDSEPARATOR;
            $exporter_output .=  $FIELDSTART . $audience->fields['customers_telephone'].$FIELDEND;
            $exporter_output .=  $LINEBREAK;
          }

[Chuckl]

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.

[Woody619]

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.

[Chuckl]

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

[Woody619]

Excellent got it on each line now, but the '0' gave back strange results.
After adding "0". I get this as telephone number
[FONT=Calibri]0"01733******" [/FONT]
[FONT=Calibri]lol now I got an extra 0 and two " , tried removing the 0 leaving "". lol that went back the same as before.[/FONT]
[FONT=Calibri][/FONT]
[FONT=Calibri]How many times can one say thanks.[/FONT]

[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.

[TheOracle]

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 ?

[Woody619]

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

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 ?

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

[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.

[Woody619]

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.