Jump to content
Sign in to follow this  
mpiscopo

Quickbooks Customer Import v1.0

Recommended Posts

I have added a contribution for importing customer address/account information into QuickBooks. Please feel free to comment and contribute. I would like to eventually have it create an iif for sales, but that is an ongoing project. It does save time from having to copy and past customer information from osCommerce into Quickbooks.

 

============================================

This is a very simple contribution which will put a button on your orders page and within the details page for all orders. It exports customer details from osCommerce one customer at a time, creating a file for each customer you choose to export. The file is named according to the customers name.

 

This contribution will get the customer account information from your SQL database.

Format the phone number according to US format (999-999-9999).

Change all first letters of name and address to capital letters.

 

This contribution does not collect/import sales/invoice information as of yet. That feature will be available in the next release. Working on the problem where Quickbooks appends subcategory part numbers with the category name to create their own unique part number.

Nor does this contribution deal with customers who write everything in CAPS, maybe this will be changed in a future release.

============================================

Share this post


Link to post
Share on other sites

Thank you for the contribution. It will be a lifesaver when I get it to work. I installed it as mentioned (even removed and reinstalled to be sure) I get a 404 message saying it cannot find .../catalog/admin/FILENAME_CUSTOMER_EXPORT?id=XXX. Is there a place I need to define this?

 

John

Share this post


Link to post
Share on other sites

Opps, sorry about that.

 

Add to your /catalog/admin/includes/filenames.php the following line:

define('FILENAME_CUSTOMER_EXPORT', 'customer_export.php');

 

Let me know if you have any further problems.

Share this post


Link to post
Share on other sites

That did it almost. I had to go in and give myself permission as well. But now it is working well!

Share this post


Link to post
Share on other sites

Hello Mike,

Your contribution is a lifesaver.

How do I download and install your addon?

Thank you

Zali

Share this post


Link to post
Share on other sites

John,

What did you have to give yourself permissions to? It shouldn't require any additional permission settings on the server since it doesn't save anything there. Also let me know any comments you might have. I have tested it with a bunch of variations of customers (different shipping addresses, company names, etc).

 

Zali,

The link is in the second post, click on it and you should get to the contribution.

 

-Mark

Share this post


Link to post
Share on other sites

first i have to thank you for this contribution. very helpful!!

 

my ask: is it possible to import the customers_id too?

i?ve tried to put "NAMENUMBER " to the customer_export.php but it doesn?t run.

 

can you help me with this?

 

regards from germany

 

Ron


WAR is not the answer!

Share this post


Link to post
Share on other sites

its me again,

 

i have made it!

 

to generate and import the customers_id i?ve made the following changes:

 

old code

 

// Create Customer File Headers

  $cust_header ="!CUST\tNAME\tBADDR1\tBADDR2\tBADDR3\tBADDR4\tBADDR5\t";

  $cust_header.="SADDR1\tSADDR2\tSADDR3\tSADDR4\tSADDR5\tPHONE1\tFAXNUM\t";

  $cust_header.="EMAIL\tTAXITEM\tCONT1\tTERMS\tTAXABLE\tCOMPANYNAME\tFIRSTNAME\tLASTNAME\t\n";

 

// Create the IIF line containing the users information. If you changed the header line, you

// will also need to change these lines to match - read the QB IIF docs for more info

  $customer ="CUST\t$customers_name\t$BADDR[0]\t$BADDR[1]\t$BADDR[2]\t$BADDR[3]\t$BADDR[4]\t";

  $customer.="$SADDR[0]\t$SADDR[1]\t$SADDR[2]\t$SADDR[3]\t$SADDR[4]\t$customers_telephone\t$customers_fax\t";

  $customer.="$customers_email_address\t$taxitem\t$customers_name\t$payment_method\t$taxable\t$customers_company\t$firstname\t";

  $customer.="$lastname\t\n";

 

 

my new code:

 

// Create Customer File Headers

  $cust_header ="!CUST\tNAMENUMBER\tNAME\tBADDR1\tBADDR2\tBADDR3\tBADDR4\tBADDR5\t";

  $cust_header.="SADDR1\tSADDR2\tSADDR3\tSADDR4\tSADDR5\tPHONE1\tFAXNUM\t";

  $cust_header.="EMAIL\tTAXITEM\tCONT1\tTERMS\tTAXABLE\tCOMPANYNAME\tFIRSTNAME\tLASTNAME\t\n";

 

// Create the IIF line containing the users information. If you changed the header line, you

// will also need to change these lines to match - read the QB IIF docs for more info

  $customer ="CUST\t$customers_id\t$customers_name\t$BADDR[0]\t$BADDR[1]\t$BADDR[2]\t$BADDR[3]\t$BADDR[4]\t";

  $customer.="$SADDR[0]\t$SADDR[1]\t$SADDR[2]\t$SADDR[3]\t$SADDR[4]\t$customers_telephone\t$customers_fax\t";

  $customer.="$customers_email_address\t$taxitem\t$customers_name\t$payment_method\t$taxable\t$customers_company\t$firstname\t";

  $customer.="$lastname\t\n";

 

maybe this isn?t interesting only for me?!


WAR is not the answer!

Share this post


Link to post
Share on other sites

it seems that i spam this thread ;)

 

another important change for shop-owners using "purchase without account" -contribution:

 

old code

$filename = "$firstname-$lastname";

 

new code

 

$filename = "$customers_name";

 

the contrib pwa does not write any informations to the customers table because they do not register. so the file has no name while create import-file...

 

my change fix this and so every file gets automaticly the name from the orders table!

 

what do the author think? :)


WAR is not the answer!

Share this post


Link to post
Share on other sites

Ron,

 

These are good changes you made. Please re-zip the contribution with your changes, note what you have changed (enhancements to the contrib), and re-upload it.

 

Thanks for taking the time to contribute.

 

Eventually I want to be able to do two things;

1) Be able to have check boxes to select multiple pending sales to be exported, rather than a single file for each person.

2) Export the invoice; however, since I'm using subcategories in Quickbooks I need to work on a way to change the osC part numbers to the Quickbooks modified part numbers (they append the category to the beginning of the part number). My approach is going to be to create an export from Quickbooks to use as a lookup table, then have the contrib use this table to replace the part numbers with the appropriate QB part number. (or if I wait long enough maybe Quickbooks will fix this problem themselves - I'd like to be able to use my barcode cheat sheets and barcode scanner to enter items into a sale once again).

 

Thanks again,

Mark

Share this post


Link to post
Share on other sites

Ron,

 

One other question; what version of Quickbooks are you using? I don't see the NAMENUMBER field in the QB manual. I don't have access to my QB until later today to see if it exists in my version (I'm on version 2003).

 

I also wonder what would happen if the account number conflicted with an existing account number, will it overwrite it? May be a problem for some people. Maybe if a variable was set in the first portion of the contrib which gives the person installing it the option to use the osCommerce or the Quickbooks customer number.

 

Would be something like:

 

//Set here whether you want to import the osCommerce customer account number into Quickbooks, set to true of false.

$customer_number_import = "true";

 

 

Then later down use an if statement for the two formats:

 

if ($customer_number_import == 'true')

{

$cust_header ="!CUST\tNAMENUMBER\tNAME\tBADDR1\tBADDR2\tBADDR3\tBADDR4\tBADDR5\t";

etc...

Share this post


Link to post
Share on other sites

hi author ;)

 

i?m using quickbooks 2004 plus.

 

the software is new and i?ve done nothing until today with it.

but in the future i?ll use it for my business.

 

 

your idea with the checkboxes would be very helpful because its circumstantial to export every single customer...

 

i?ll re-upload my changes this weekend..

 

greetings from germany :)


WAR is not the answer!

Share this post


Link to post
Share on other sites

if you have a customer with the id "x" and you import a new one with the same id both will co-existing!

so nothing get lost, but its not a perfect solution.

 

maybe it would be better to create 2 different contributions: one for shops with registered customers and another one for users using shops without registration.

 

so we can use the customers table for the registered version and otherwise the orders table for shops without registration...

 

what would you say?


WAR is not the answer!

Share this post


Link to post
Share on other sites

we have a client using clearly bookkeeping pro which uses quickbooks i believe.

i have not used your contribution yet but i wonder if you could improve my knowlede by answering my query?

 

i need to export order details etc from oscommerce and import into clearly bookkeeping pro for use with the accounts etc. i have created a new admin area for my clients oscommerce site which incorporates the day to day stuff that he needs. i have also added an export function amongst other things to export the orders data and other things located in admin/tools of the original admin area.

 

These are exported into excel for now and i was hoping to be able to import these excel files into clearly bookkeeping pro for use in there. i have looked around the web and cannot find much explanation on how to do this or even whether it is possible and seeing as i am a php coder i do not know anything about clearly bookkeeping pro whcih makes things difficult!

 

all i have managed to import are items for sale.

 

do you have any advice on this issue?

 

thanks

 

Gareth


always here to offer some useless advice....

Share this post


Link to post
Share on other sites

If their software is based on QuickBooks you should be able to go into the help section and search on iif or import and look at the matches. QuckBooks has several topics which will show you how to name the fields and what the fields are. It is actually quite easy, it sounds like you have the hard part done. The QuickBooks iif files are mearly tab delimited files. QuickBooks can also import Excel files. Another thing to try is to do an export from their software, then look at the format of the file (column headings, names, values, etc).

 

Hope this helps.

Share this post


Link to post
Share on other sites

Does this version of Quickbooks Customer Import v1.0, handle the accounting information? such as the total sales, etc?


Most likely your question has been answered, please do a search first.

Share this post


Link to post
Share on other sites

Hi,

 

I have just installed this contribution.

 

It is installed great... the buttons are there and all looks well accept for one thing!

 

I am getting this error message when I click on the button.

 

Access Denied

No Right Permission Access

Please contact your Webmaster to request more access or if you found any problem.

 

I saw above that John said he had this problem and had to "give himself permissions"

 

Does anyone know which files or directories you have to CHMOD 777 ???

 

Thanks :)


I'm having a great Day - hope you are too!

 

Leon

Share this post


Link to post
Share on other sites

Thanks John

 

For answering me by email. You really have a great site: John's Great Site

Problem solved! :rolleyes:

Edited by bibleman

I'm having a great Day - hope you are too!

 

Leon

Share this post


Link to post
Share on other sites

Hi Mark,

 

Well I did a lot.... John was a great help to me. But maybe I can help you a bit.

 

The main thing I had to do was to set the permissions right. This was not a Chmod like I thought but an operation within the admin of the store.

 

Go to catalog/admin/admin_files.php and make sure you add the file "customer_export.php" by clicking on store file "customersOLD" directory.

 

When I did this I gave myself permissions as administrator to access the file. Then it worked perfectly.

 

If this helps then great.... if not let me know and be specific what your problem is, maybe I can assist further. If not maybe someone else can. I am still pretty new to PHP and oscommerce.


I'm having a great Day - hope you are too!

 

Leon

Share this post


Link to post
Share on other sites
Does this version of Quickbooks Customer Import v1.0, handle the accounting information? such as the total sales, etc?

anyone? i'm sure someone, out there has this installed and knows the answer. thank you for your reply.


Most likely your question has been answered, please do a search first.

Share this post


Link to post
Share on other sites
Does this version of Quickbooks Customer Import v1.0, handle the accounting information?  such as the total sales, etc?

anyone? i'm sure someone, out there has this installed and knows the answer. thank you for your reply.

Hi,

 

No this version of the Quickbooks Mod only downloads the name and address and telephone of the customer. It does not include produsts or merchant card information.

 

The trade off is this mod is very easy to install. Very easy. The mod that does it all as you are talking about (I was told by a php programmer) takes about 4 hours to install.


I'm having a great Day - hope you are too!

 

Leon

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×