Export customers data

Ask the community for help and support.
Post Reply
14Steve14
VIP Member
VIP Member
Posts: 549
Joined: Fri Oct 25, 2019 7:01 pm
Has thanked: 8 times
Been thanked: 49 times

Export customers data

Post by 14Steve14 »

I need to export a list of customer data that can be imported into my new accounting software. As I am unable to find any addons that will do this, is there a way to achieve this using sql as its only going to be a one off thing that needs doing. The confusing bit for me is that the data needed is in different tables and I am not confident with sql stuff.

I need the following information:-
Customers name (first and last name combined), company, phone number, fax number, email address, street, suburb, town, county, postcode country.

The rest I can add once I have it as an excel table as the other data needed is things like customer type, opening balance and that sort of stuff that in basically an empty column or can be added easily.

Any help would be greatly received.
ecartz
Lead Developer
Lead Developer
Posts: 2637
Joined: Tue Nov 05, 2019 6:02 pm
Has thanked: 4 times
Been thanked: 181 times

Re: Export customers data

Post by ecartz »

Code: Select all

SELECT
    CONCAT(c.customers_firstname, ' ', c.customers_lastname) AS name,
    ab.entry_company, c.customers_telephone, c.customers_fax, c.customers_email_address, c.customers_id,
    ab.entry_street_address, ab.entry_suburb, ab.entry_city, ab.entry_state, ab.entry_postcode,
    co.countries_name
  FROM customers c INNER JOIN address_book ab ON c.customers_id = ab.customers_id
                                             AND c.customers_default_address_id = ab.address_book_id
                   LEFT JOIN countries co ON ab.entry_country_id = co.countries_id
14Steve14
VIP Member
VIP Member
Posts: 549
Joined: Fri Oct 25, 2019 7:01 pm
Has thanked: 8 times
Been thanked: 49 times

Re: Export customers data

Post by 14Steve14 »

@ecartz Thanks very much for the prompt reply. Unfortunately I get an error when running that in phpmyadmin. The error is
#1054 - Unknown column 'c.telephone' in 'field list'
heatherbell
VIP Member
VIP Member
Posts: 1996
Joined: Mon Oct 07, 2019 4:39 am
Has thanked: 26 times
Been thanked: 175 times

Re: Export customers data

Post by heatherbell »

I guess you are going to import all customers from your website DB into your accounting DB and then keep that updated with every new customer.
We all do things differently, we only use one 'customer' in our accounting for each of our websites i.e. the 'customer' is 'my website' (although we have other customers there e.g. those buying items not on websites or are buying on account).
Each website sale (or batch of sales) is recorded for 'my website' as the customer and with the website order/invoice number(s) as reference and the payment then entered with the website order/invoice pdf(s) attached.
ecartz
Lead Developer
Lead Developer
Posts: 2637
Joined: Tue Nov 05, 2019 6:02 pm
Has thanked: 4 times
Been thanked: 181 times

Re: Export customers data

Post by ecartz »

I edited the query. It should be correct now.
14Steve14
VIP Member
VIP Member
Posts: 549
Joined: Fri Oct 25, 2019 7:01 pm
Has thanked: 8 times
Been thanked: 49 times

Re: Export customers data

Post by 14Steve14 »

heatherbell wrote: Wed Apr 07, 2021 9:57 am I guess you are going to import all customers from your website DB into your accounting DB and then keep that updated with every new customer.
We all do things differently, we only use one 'customer' in our accounting for each of our websites i.e. the 'customer' is 'my website' (although we have other customers there e.g. those buying items not on websites or are buying on account).
Each website sale (or batch of sales) is recorded for 'my website' as the customer and with the website order/invoice number(s) as reference and the payment then entered with the website order/invoice pdf(s) attached.
The big plan is to export all the customers and their customer number which I forgot to add into the sql bit, but I should be able to work that bit out. New customers will be added when they buy something. Eventually after a while it should be possible to remove any old inactive customers from the accounts database, or that's the plan at the moment. The theory being that for now it will save time having them all in there. Its also the plan to send invoice receipts by email from the accounting package.

In my old accounting package I have three customers, the website, ebay and etsy, so it sounds as if we do the same sort of thing.
14Steve14
VIP Member
VIP Member
Posts: 549
Joined: Fri Oct 25, 2019 7:01 pm
Has thanked: 8 times
Been thanked: 49 times

Re: Export customers data

Post by 14Steve14 »

ecartz wrote: Wed Apr 07, 2021 2:51 pm I edited the query. It should be correct now.
@ecartz Thank you very much. I did have to make one further change but I worked it out for myself after getting an error. There was a address bit missing from the street but apart from that worked like a charm and gives me something to add the extra added columns into to match the accounting packages details.
Post Reply