Exporting my customer database to CSV.

[bi11i]

I'm referencing to an archived thread which shows I can export my customer database to CSV via phpAdmin by running the query below, clicking export in the lower right-hand corner, tick the box which states to put the fieldset in the first row, and click download. The query I'm using is:

SELECT
zen_customers.customers_firstname,
zen_customers.customers_lastname,
zen_customers.customers_email_address,
zen_address_book.entry_company,
zen_address_book.entry_street_address,
zen_address_book.entry_suburb,
zen_address_book.entry_postcode,
zen_address_book.entry_city,
zen_address_book.entry_state,
zen_customers.customers_telephone
FROM
`zen_address_book`,`zen_customers`
WHERE
zen_address_book.address_book_id=zen_customers.customers_id

But for some reason this doesn't pull in the entry_state, or the field is blank. I can see this from phpadmin (and not just from the generated file.) I'm using phpadmin v2.6.4pl4 with zen cart v1.2.7 and am trying to export my database so I can import to stamps.com (which works quite well, less the state field and the fact that the stamps.com import doesn't have a field for country....

Any ideas? (and thanks....)

[DrByte]

entry_state is only non-blank if no related zone is found.

you can do a join on the zones table, with entry_zone_id = zone_id, and bring back zone_code or zone_name to fill in the state for you.

[bi11i]

Sounds like a working plan, but way over my head in terms of the SQL work that needs to be done....

Any chance of exchanging the query for my gratitude?

thx....

entry_state is only non-blank if no related zone is found.

you can do a join on the zones table, with entry_zone_id = zone_id, and bring back zone_code or zone_name to fill in the state for you.

[DrByte]

how's this:

SELECT 
c.customers_firstname as FirstName,
c.customers_lastname as LastName,
c.customers_email_address as Email,
ab.entry_company as Company,
ab.entry_street_address as Address1,
ab.entry_suburb as Address2,
ab.entry_postcode as ZipPostalCode,
ab.entry_city as City,
z.zone_name as State,
ab.entry_state as Province,
co.countries_name as Country,
c.customers_telephone as Telephone
FROM
customers c,
address_book ab
LEFT JOIN zones z ON (ab.entry_zone_id = z.zone_id
and ab.entry_country_id = z.zone_country_id)
left join countries co ON ab.entry_country_id = co.countries_id
WHERE
ab.customers_id=c.customers_id
and ab.address_book_id = c.customers_default_address_id;

[bi11i]

Looks, great, although I have no idea in how to tailor this to my database. When I run it, I get an error that says

#1146 - Table 'phytocart_com.customers' doesn't exist

am i needing to insert my database prefix in here somewhere?

[DrByte]

....
FROM
INSERT_PREFIX_HERE_customers c,
INSERT_PREFIX_HERE_address_book ab
LEFT JOIN INSERT_PREFIX_HERE_zones z ON (ab.entry_zone_id = z.zone_id
and ab.entry_country_id = z.zone_country_id)
left join INSERT_PREFIX_HERE_countries co ON ab.entry_country_id = co.countries_id
.....

[bi11i]

Thanks! That worked out perfectly.....

A little strange that Stamps.com doesn't have a field for country, but now that I look over the service a little more closely, I see they don't offer international shipping, either.

Either way, thanks so much - highly appreciated. I've been copying/pasting from my zen address book into the click-n-ship fields, line by line. I'll need to continue doing this for international orders, but for domestic, I can now just copy the whole address, name, etc - saves loads of time and thanks again....

[DrByte]

You could also alter the query above slightly:

- remove the ; from the last line
- add the following, so that it's sorted more logically:

ORDER BY co.countries_name, z.zone_name, ab.entry_state, ab.entry_postcode;

[krizzle]

does anyone know how to export customer shipping addresses to an excel file for easy shipping label printing?

[libracorn]

The export info worked great! I have another question though. What would the sql code be if I wanted to only pull the customer addresses who placed orders in the last year?