Download to Quickbooks?

Hi

I am in the process of putting together a mod that will allow you to download your customers name, address, phone and email address to a csv file, which can be used to insert those details into quickbooks pro.

This is my first contribution so I would appreciate any comments good or bad on the mod. I will upload the mod shortly, but I expect it won't be available straight away.

This has been made for users of quickbooks UK, I have not tested it in other countries.

Thanks,

Phil

Hi Everyone

I have just had confirmation that the mod is available to download.

http://www.zen-cart.com/index.php?main_page=product_contrib_info&cPath=40_41&products_id=1147

Please take a minute to have a look if you can, especially if you are a Quickbooks user.

Any comments or suggestions would be appreciated.

Thanks,

Phil

Hi

I am putting together an interface for people with large databases, the current Quickbooks Download retrieves all of the database records making this a large file to download if you have a large database. In the new version users will be able to select a number (entry) in the database and then download entries from that number (entry).

It should be ready soon,

Phil

Hi

The improved Quickbooks Download is now complete and ready for downloading. The new module allows people to see their orders table and select from which person in the database to download. This was for people with large databases who could only download the enitre file before.

As always, if you could take the time to post your views or questions.

Thanks,

Phil

Hi

At the time of writing this 62 people have downloaded my script. I am not one for glory or acknowledgements, I don't even want any donations!
But I thought one or two people might have had the decency to write a few lines to say something!

It only took me a few weeks nailed to the computer to do this, is everyone out there that rude not to say at least thanks!

Phil

dude i just registered right now for the sole purpose of sending a thank you.

:cool:

Hi Cornhustlah

Thank you very much for your reply.

I would be interested in where you are in the world and what version of Quickbooks you are using, and obviously what do you think of my module?

Thanks,

Phil

Thanks for the contribution. Overall, it works pretty good. I have some problems and suggestions for you. I'm not trying to be critical ... just trying to help make your module even better and easier to use.

Problem:
State/County is missing from exported .CSV
Country is missing

Suggestion:
Not sure about the way you decided to handle missing Suburbs. In the USA, we have Suburbs, but it's rarely part of our postal address. Our address would be incorrectly labeled if we included it in most cases. If we have an usually long street address, we will break it into 2 lines (Address Line 1 & Address Line 2). We know we can use the Suburb line in this case. Seems like Address 2 (Suburb) should export as (blank) if it's blank in ZenCart, unless it gives QuickBooks Import problems, in which case you might just make it a . (period) or something.

Suggestion:
You might want to modify the contribution's description to include the word "export". Right now, it's a little hard to find with the site's search engine. Also, I know you intended it to work with QuickBooks, I likely plan to use it as a basic CSV exporter to take the data to other desktop apps. (not just QB).

Observations:
When you first run it, if you get:
Fatal error: SQL in .../admin/quickbooks_download_view.php on line 45
... that just means your table prefixes are wrong. A quick Search and Replace for zen_ and you are back in business.

I'm running QuickBooks 2007. Like I said, I wasn't really planning on using it in this way. However, once I get a proper export, I wouldn't mind importing them into a test company file to see if it's compatible.

I also wanted to add that although the State is shown on the export screen (right before you download) Billing Address4 gets exported empty or blank.

The Country is NOT shown on the export screen yet, but it would be nice if it was included (as Billing Address6 I suppose).

Can you give an example of a long address and also the state problem?

I think I might make an ammerican version in addition to the current version so people can choose.

Phil

An American version would be great and greatly appreciated if you can do this for all us.

Thank you for your good work!

Tezla:

I also wanted to add that although the State is shown on the export screen (right before you download) Billing Address4 gets exported empty or blank.

The Country is NOT shown on the export screen yet, but it would be nice if it was included (as Billing Address6 I suppose).

That's odd, because if it is showing before you download, then it is in the database and therefore it should download. It works ok for me.

You can have the country showing in the preview and have it download too, let me know if you want me to show you.

Phil

Tezla:

Thanks for the contribution. Overall, it works pretty good. I have some problems and suggestions for you. I'm not trying to be critical ... just trying to help make your module even better and easier to use.

Problem:
State/County is missing from exported .CSV
Country is missing

Suggestion:
Not sure about the way you decided to handle missing Suburbs. In the USA, we have Suburbs, but it's rarely part of our postal address. Our address would be incorrectly labeled if we included it in most cases. If we have an usually long street address, we will break it into 2 lines (Address Line 1 & Address Line 2). We know we can use the Suburb line in this case. Seems like Address 2 (Suburb) should export as (blank) if it's blank in ZenCart, unless it gives QuickBooks Import problems, in which case you might just make it a . (period) or something.

Suggestion:
You might want to modify the contribution's description to include the word "export". Right now, it's a little hard to find with the site's search engine. Also, I know you intended it to work with QuickBooks, I likely plan to use it as a basic CSV exporter to take the data to other desktop apps. (not just QB).

Observations:
When you first run it, if you get:
Fatal error: SQL in .../admin/quickbooks_download_view.php on line 45
... that just means your table prefixes are wrong. A quick Search and Replace for zen_ and you are back in business.

I'm running QuickBooks 2007. Like I said, I wasn't really planning on using it in this way. However, once I get a proper export, I wouldn't mind importing them into a test company file to see if it's compatible.

All fields have to be filled in the excel file otherwise, if you have a missing field then every field after that will move up 1 when you import into QB. So for example instead of email being imported into the email box, you would have the telephone number!

If you replaced the suburb with a period, then the period would be inserted into that box where all of the suburb boxes were filled in with the other entries.

confused_aswell:

Can you give an example of a long address and also the state problem?

I think I might make an ammerican version in addition to the current version so people can choose.

Phil

As far as the addresses go this should help:
http://pe.usps.gov/cpim/ftp/pubs/Pub28/pub28.pdf

I don't really see a difference between our 2 countries. Our QB can have International customers and so can yours, and so can ZenCart. One universal format should work for everyone. It's all in the QB Import Mappings.

But basically, you either have Address Line 2/Suburb or you don't, but you don't duplicate the city. Same with Country.

This mail merge should work for everyone (most countries or at least US and Canada):

Company Name
FirstName LastName
Street Address
Address Line 2 or Suburb (or blank line)
City, State/Province, Zip/PostalCode
Country

Because of the QuickBooks Mapping capabiliity on import, one version should work for everyone, as long as all the fields are there to choose from.

confused_aswell:

That's odd, because if it is showing before you download, then it is in the database and therefore it should download. It works ok for me.

You can have the country showing in the preview and have it download too, let me know if you want me to show you.

Phil

The state shows on the export screen, but is not contained in the CSV download.

I can't really show you the Export screen because it's got real addresses, but I assure you, they are there.

Here is one line from the CSV:
"Customer Job","First Name","Last Name","Billing Address1","Billing Address2","Billing Address3","Billing Address4","Billing Address5","Phone","Email",
"Bill Smith","Bill","Smith","123 Any Avenue","Houston","Houston","","45345","123-455-5684 ","[email protected]",

Whether you call Billing Address4 "State" or call it "Province" I don't see how it works for you and not me. We are both using the same ZenCart (I'm using 1.3.8a). Quickbooks and/or Excel are not even in the equation yet.

There might be a country setting in ZenCart the effects this. I noticed that my State field in the DB is blank, and it really derived from a LEFT JOIN against zones.

Likewise, I've seen some code where address_book.entry_state returns the Province, but that field is NULL for me.

As you know, there are 2 different sets of SQL statements running (one for the screen, one for the CSV output), so that should explain why the output differently.

Yes, I think I know how the modify both sets of SQL to add the Country. Just thought you might want to include it on next release.

confused_aswell:

All fields have to be filled in the excel file otherwise, if you have a missing field then every field after that will move up 1 when you import into QB. So for example instead of email being imported into the email box, you would have the telephone number!

If you replaced the suburb with a period, then the period would be inserted into that box where all of the suburb boxes were filled in with the other entries.

Yes, I see what you mean. That Customer Import inside QB (even with proper mappings) is pretty weak. The fix is to make sure there is a least a space in the export field so it will actually count as a valid field like this ," ",

My previous suggestion about Suburbs was premature. However, I was speaking of the IF THEN loop in the routine and ONLY when empty. Now that I've actually tested it it seems the right thing to do might be as stated above. Export Suburb if it is filled in ... Export "a space" if it is empty. I would think this should apply to all "optional" fields that might be empty.

When I use the QB-SDK (qbFC) to write VB programs to import customers I do something similar. I first use IF-THEN statements to check that the variable is not empty before trying to work with it.

Hi Tezla

Thanks for your comments, I can see where you are coming from. It seems I have a lot more work to do for the next release, if you can provide some of the sql for the queries and also the IF THEN statements then maybe I can get an updated version out there quicker.

Thanks,

Phil

I have updated your module with some general fixes and updated for USA. Everything works fine now. Import to QuickBooks also works.

The download and view SQL queries match now.

The Suburb / QB fix is done.

The State and Country export now.

Commented out the "Space in UK phone number after 5 digits" routine.

It should work for pretty much all the countries. The only real difference is the space in phone number thing, and that can easily be activated or disabled by comments as needed or other ways.

After I added the Billing Address 6/Country export, I realized that for some reason there is no option to import the Country in QB (I guess they forgot it). It's allowed in the QB-SDK though.

I left it in ... in case they add it to the Excel Import later or people want to use that CSV file to import to something else that needs a full address. It ignores it fine.

Oh, by the way ... it seems you only need the SQL Left JOINS against tables like zones (for States) when you are working with the address_book to get certain Customer info. Order addresses are stored somewhere else.

Hi

I am glad you have sorted your problems. Have you uploaded an updated version to my mod, if not can you let me have a copy of your doings so I can upload it for other people to use?

Thanks,

Phil

Phil,

I thought I would upload it to you, let you test it on your system there in the UK, approve it, and then you can release the new version.

:smile: