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.
Export customers data
-
- Lead Developer
- Posts: 2656
- Joined: Tue Nov 05, 2019 6:02 pm
- Has thanked: 4 times
- Been thanked: 182 times
Re: Export customers data
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
-
- VIP Member
- Posts: 578
- Joined: Fri Oct 25, 2019 7:01 pm
- Has thanked: 9 times
- Been thanked: 49 times
Re: Export customers data
@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'
-
- VIP Member
- Posts: 2025
- Joined: Mon Oct 07, 2019 4:39 am
- : Buy Me A Beverage
- Has thanked: 28 times
- Been thanked: 187 times
Re: Export customers data
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.
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.
-
- VIP Member
- Posts: 578
- Joined: Fri Oct 25, 2019 7:01 pm
- Has thanked: 9 times
- Been thanked: 49 times
Re: Export customers data
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.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.
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.
-
- VIP Member
- Posts: 578
- Joined: Fri Oct 25, 2019 7:01 pm
- Has thanked: 9 times
- Been thanked: 49 times
Re: Export customers data
@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.