Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

QBI Quickbooks Import


adam5532

Recommended Posts

Brent,

 

I'm not sure why this is happening, but the free version 2.10c is a very old version of QBI and in occasional cases the automatic installation does not work correctly -- the installation routine was completely rewritten for 3.x and does not have any problems. But to defeat the automatic routine in 2.10:

 

File: /catalog/includes/qbi_version.php

Lines: 34 - 40

Action: Comment out (add // to the beginning of each line)

 

Also, make sure that in the table qbi_config the field qbi_config_active is set to 1.

 

This will defeat QBI's check that the tables are installed and configured, and assuming that they really are installed and the check is just failing to know that, then it should work fine. If, however, your tables (specifically the table qbi_config) were not installed, or the database_tables.php file was not properly updated (to contain the constant QBI_TABLE_CONFIG among other QBI tables), then the check was failing like it is supposed to when something is wrong.

 

Thanks,

Adam

 

I did as you state above. Now when it loads I am on the down load page with the tabs Create iff, Set up, Match, Configure, About

at the top. I selected "Configure"

and the URL changes to this "...cat2/admin/cat2/admin/qbi_config.php" so I deleted the second set of "cat2/admin/" and the configuration page loads. I enetered all the settings and hit update which just resets the form. Nothing is saved. Am I missing some thing else?

 

Thanks for the help :)

Link to comment
Share on other sites

Adam,

 

I had your program working but screwed up on myside and had to do a complete reinstall of everything and I was just working on adding back in QBI and I get this error

 

Quickbooks Import QBI  

1060 - Duplicate column name 'qbi_imported'

ALTER TABLE `products` ADD `qbi_imported` TINYINT(2) UNSIGNED DEFAULT '2' NOT NULL;

[TEP STOP]

Link to comment
Share on other sites

I did as you state above. Now when it loads I am on the down load page with the tabs Create iff, Set up, Match, Configure, About

at the top. I selected "Configure"

and the URL changes to this "...cat2/admin/cat2/admin/qbi_config.php" so I deleted the second set of "cat2/admin/" and the configuration page loads. I enetered all the settings and hit update which just resets the form. Nothing is saved. Am I missing some thing else?

 

Thanks for the help :)

Undo the comment marks // that I put above, because the problem isn't with the installation routine. You have a problem with one or both below:

 

Check your osC directory configuration. QBI simply reads it from osC, so if it is wrong like above it is not a QBI issue. Check your directory settings in /catalog/admin/includes/configure.php. You apparently have cat2/admin in twice.

 

Check that you have properly added the QBI database table definitions to the file /catalog/admin/includes/database_tables.php

Check that there are no small typos, like the ";" left out from the end of a line that would make it look OK but prevent it all from working.

 

Check that you have QBI 2.1b or 2.1c.

 

Thanks,

Adam

Link to comment
Share on other sites

Adam,

 

I had your program working but screwed up on myside and had to do a complete reinstall of everything and I was just working on adding back in QBI and I get this error

 

Quickbooks Import QBI  

1060 - Duplicate column name 'qbi_imported'

ALTER TABLE `products` ADD `qbi_imported` TINYINT(2) UNSIGNED DEFAULT '2' NOT NULL;

[TEP STOP]

Before doing a full reinstall of QBI 2.x, you need to remove the qbi_imported fields from your orders and products tables. This is explained in the installation manual (section 5 for version 2.10c, section 4 for 2.10b).

 

However, check if it is working OK, despite the error message, before doing anything else. You might be alright, as long as everything got installed correctly before the error message.

 

If you just messed up the php files, then you should leave the database alone, and just replace the files (and make sure the table definition and menu item steps are done also). That way QBI will find the database and not have to reinstall it.

 

Thanks,

Adam

Link to comment
Share on other sites

Before doing a full reinstall of QBI 2.x, you need to remove the qbi_imported fields from your orders and products tables. This is explained in the installation manual (section 5 for version 2.10c, section 4 for 2.10b).

 

However, check if it is working OK, despite the error message, before doing anything else. You might be alright, as long as everything got installed correctly before the error message.

 

If you just messed up the php files, then you should leave the database alone, and just replace the files (and make sure the table definition and menu item steps are done also). That way QBI will find the database and not have to reinstall it.

 

Thanks,

Adam

 

Adam,

 

Not to sure what to do at this point, but when I goto the admin/catalog/QBI import, it now says this:

 

Upgrade QBI database from version 1.00 to 2.10?

 

I have not put any 1.00 files in there, I only have 2.10, what should I do now. Oh and by the way, when I click upgrade, it does to the message I have before in the above post I wrote. So I am clueless at the moment.

Link to comment
Share on other sites

Adam,

 

Not to sure what to do at this point, but when I goto the admin/catalog/QBI import, it now says this:

 

Upgrade QBI database from version 1.00 to 2.10?

 

I have not put any 1.00 files in there, I only have 2.10, what should I do now. Oh and by the way, when I click upgrade, it does to the message I have before in the above post I wrote. So I am clueless at the moment.

 

I got it adam, just incase you get the same problem in the future, what I did was, went to phpmyadmin and delete the table for qbi_config then went back and reinstalled, so not problems now, thanks for the help and I love this contribution. In the furture you plan to be able to import that products too??? I will definately pay for that!@!

Link to comment
Share on other sites

I got it adam, just incase you get the same problem in the future, what I did was, went to phpmyadmin and delete the table for qbi_config then went back and reinstalled, so not problems now

Yup, that's what it says to do in the installation instructions under "Reinstallation." :rolleyes:

, thanks for the help and I love this contribution. In the furture you plan to be able to import that products too??? I will definately pay for that!@!

QBI can load in the QB items for the purpose of matching (mapping), but can't populate your shopping cart with QB items. That may be added in QBI 4.0. For now, you may be able to do that with Easy Populate.

 

Thanks,

Adam

Link to comment
Share on other sites

Yup, that's what it says to do in the installation instructions under "Reinstallation." :rolleyes:

 

QBI can load in the QB items for the purpose of matching (mapping), but can't populate your shopping cart with QB items. That may be added in QBI 4.0. For now, you may be able to do that with Easy Populate.

 

Thanks,

Adam

 

 

Adam, I made a test order last night and when I went in to check the order out and dowload the iif, I got this message, well messages

 

 

Warning: fopen(qbi_output/qbi_orders.iif) [function.fopen]: failed to open stream: Permission denied in /home/icecream/public_html/catalog/admin/includes/qbi_engine_orders.php on line 58

Warning: fwrite(): supplied argument is not a valid stream resource in /home/icecream/public_html/catalog/admin/includes/qbi_engine_orders.php on line 357

Warning: fwrite(): supplied argument is not a valid stream resource in /home/icecream/public_html/catalog/admin/includes/qbi_engine_orders.php on line 358

Warning: fwrite(): supplied argument is not a valid stream resource in /home/icecream/public_html/catalog/admin/includes/qbi_engine_orders.php on line 359

Warning: fclose(): supplied argument is not a valid stream resource in /home/icecream/public_html/catalog/admin/includes/qbi_engine_orders.php on line 390

Warning: filesize() [function.filesize]: Stat failed for qbi_output/qbi_orders.iif (errno=2 - No such file or directory) in /home/icecream/public_html/catalog/admin/includes/qbi_engine_orders.php on line 398

Warning: Cannot modify header information - headers already sent by (output started at /home/icecream/public_html/catalog/admin/includes/qbi_engine_orders.php:58) in /home/icecream/public_html/catalog/admin/includes/qbi_engine_orders.php on line 399

Warning: Cannot modify header information - headers already sent by (output started at /home/icecream/public_html/catalog/admin/includes/qbi_engine_orders.php:58) in /home/icecream/public_html/catalog/admin/includes/qbi_engine_orders.php on line 400

Warning: Cannot modify header information - headers already sent by (output started at /home/icecream/public_html/catalog/admin/includes/qbi_engine_orders.php:58) in /home/icecream/public_html/catalog/admin/includes/qbi_engine_orders.php on line 401

Warning: Cannot modify header information - headers already sent by (output started at /home/icecream/public_html/catalog/admin/includes/qbi_engine_orders.php:58) in /home/icecream/public_html/catalog/admin/includes/qbi_engine_orders.php on line 402

Warning: Cannot modify header information - headers already sent by (output started at /home/icecream/public_html/catalog/admin/includes/qbi_engine_orders.php:58) in /home/icecream/public_html/catalog/admin/includes/qbi_engine_orders.php on line 403

Warning: readfile(qbi_output/qbi_orders.iif) [function.readfile]: failed to open stream: No such file or directory in /home/icecream/public_html/catalog/admin/includes/qbi_engine_orders.php on line 

Link to comment
Share on other sites

Adam, I made a test order last night and when I went in to check the order out and dowload the iif, I got this message, well messages

Warning: fopen(qbi_output/qbi_orders.iif) [function.fopen]: failed to open stream: Permission denied in /home/icecream/public_html/catalog/admin/includes/qbi_engine_orders.php on line 58
...

You have not set the qbi_input and qbi_output directories to CHMOD 777. QBI was denied permission to write the file.

 

Thanks,

Adam

Edited by adam5532
Link to comment
Share on other sites

I installed 2.10c into my osCommerce setup, however, when I try to run the install/configuration setup by clicking on the QBI module link for the first time, I get stuck in what appears to be an endless loop.

...the server is redirecting the request for this address in a way that will never complete.

* This problem can sometimes be caused by disabling or refusing to accept cookies.

I am using a shared host, so my ssl address is not the same as the http address - is this a factor? or is there some other configuration step I'm missing from the installation manual?

I'm using osC 2.2 M2, with php 4.4.6 and mysql 4.1.21, apache 1.3.37.

Link to comment
Share on other sites

I installed 2.10c into my osCommerce setup, however, when I try to run the install/configuration setup by clicking on the QBI module link for the first time, I get stuck in what appears to be an endless loop.

...the server is redirecting the request for this address in a way that will never complete.

* This problem can sometimes be caused by disabling or refusing to accept cookies.

I am using a shared host, so my ssl address is not the same as the http address - is this a factor?

It could be. Try temporarily changing the osC config so that your admin runs non-ssl, and see if you can successfully run the install.

 

Thanks,

Adam

Link to comment
Share on other sites

It could be. Try temporarily changing the osC config so that your admin runs non-ssl, and see if you can successfully run the install.

 

Adam,

This seems to solve the problem - I updated config file in admin/ to not use https:// server, and that allowed me to proceed! Thanks for your help!

Link to comment
Share on other sites

Yikes! New issue - I went through all of the configuration, export a lists.iif file (QB 06 for Mac) and everything but products imported successfully. There, I get "SETUP_FAIL

Quickbooks Items

Name Description Account Action (twice)"

 

So, I'm wondering if it's becuase of one of several things:

(1) Items and accounts in QB use the & and / characters (like S&H) - does this cause any problems?

(2) In looking at the exported .iif file, I notice one item has a comment or description enclosed in double-quotes, and I seem to recall that this should be single-quotes - or was this an issue fixed with 3.12?

(3) IN OsC, under one category, I have several products with the same "Products Name" but are differentiated by "Products Model" . This was to accomodate versions of the same product with different retail prices.

Any suggestions?

Link to comment
Share on other sites

Yikes! New issue - I went through all of the configuration, export a lists.iif file (QB 06 for Mac) and everything but products imported successfully. There, I get "SETUP_FAIL

Quickbooks Items

Name Description Account Action (twice)"

 

So, I'm wondering if it's becuase of one of several things:

(1) Items and accounts in QB use the & and / characters (like S&H) - does this cause any problems?

(2) In looking at the exported .iif file, I notice one item has a comment or description enclosed in double-quotes, and I seem to recall that this should be single-quotes - or was this an issue fixed with 3.12?

(3) IN OsC, under one category, I have several products with the same "Products Name" but are differentiated by "Products Model" . This was to accomodate versions of the same product with different retail prices.

Any suggestions?

Are your products Inventory or Non-inventory, and do you have the corresponding type enabled in the QBI config under Match Types?

 

Thanks,

Adam

Link to comment
Share on other sites

Are your products Inventory or Non-inventory, and do you have the corresponding type enabled in the QBI config under Match Types?

 

Thanks,

Adam

Products are Inventory - but I can't match to the items because the products never loaded - that was were the error arose. Payments and shipping loaded and I could match those fine.

Link to comment
Share on other sites

Products are Inventory - but I can't match to the items because the products never loaded - that was were the error arose. Payments and shipping loaded and I could match those fine.

Open the iif file in Excel and make sure that you selected "item list" and that all the products are in the file. Also, delete all other files from your qbi_input directory, to make sure that QBI is reading the correct file.

 

Thanks,

Adam

Link to comment
Share on other sites

Open the iif file in Excel and make sure that you selected "item list" and that all the products are in the file. Also, delete all other files from your qbi_input directory, to make sure that QBI is reading the correct file.

 

Adam,

 

I opened the file in Excel to verify its contents - and they are my items. I tried the item list separately as items.iif as well as part of the lists.iff and neither worked. I also kept only one file in the folder at a time, and deleted or renamed older files.

 

I have two versions of the products in OsC, but only one in QB - I had intended to match them both the to same product. Could that be a source of the problem??

Link to comment
Share on other sites

Hi Adam,

 

I am installing QBI 2.10C and I have followed all of the setup instructions and I have also double checked all of the files to make sure they are correct. When I go into the admin area of OsCommerce I can see the Quickbooks Import QBI link under the catalog section. When I click it brings up the page "Quickbooks Import QBI

 

Database table upgrades successfull. Now you must go to the configuration page.

1) Recheck your configuration options and configure new options.

2) Click "Update" at the bottom of the page.

Note: You must click update to finish the upgrade, even if you did not change any settings!

 

Now you must go to the configuration page."

 

When I click the link to go to the configuration page it loops back to the same page.

 

I was reading some of your earlier posts and tried commenting out (//) lines 34-40 of the qbi_version.php file and then I get this screen:

http://farm1.static.flickr.com/218/4887751...e84e592.jpg?v=0

 

It also did not create any db tables.

 

Any suggestions?

 

Thanks,

Pat

Link to comment
Share on other sites

Hi Adam,

 

I am installing QBI 2.10C and I have followed all of the setup instructions and I have also double checked all of the files to make sure they are correct. When I go into the admin area of OsCommerce I can see the Quickbooks Import QBI link under the catalog section. When I click it brings up the page "Quickbooks Import QBI

 

Database table upgrades successfull. Now you must go to the configuration page.

1) Recheck your configuration options and configure new options.

2) Click "Update" at the bottom of the page.

Note: You must click update to finish the upgrade, even if you did not change any settings!

 

Now you must go to the configuration page."

 

When I click the link to go to the configuration page it loops back to the same page.

 

I was reading some of your earlier posts and tried commenting out (//) lines 34-40 of the qbi_version.php file and then I get this screen:

http://farm1.static.flickr.com/218/4887751...e84e592.jpg?v=0

 

It also did not create any db tables.

 

Any suggestions?

 

Thanks,

Pat

Pat,

 

Check that your settings in the osC file /catalog/admin/configure.php are correct. Specifically, DIR_FS_DOCUMENT_ROOT, DIR_WS_ADMIN, DIR_FS_ADMIN, HTTP_SERVER, and HTTPS_SERVER. QBI reads the settings from osC, so in almost all cases where QBI can not find things, it is an osC setup issue.

 

Also, if your secure server address is not the same as your non-secure address, then try disabling SSL in the Admin configuration while you install QBI. I'm not sure how you would get directed to the flickr.com site, so something is strange about your osC setup.

 

Thanks,

Adam

Link to comment
Share on other sites

Adam,

 

I am getting the following errors when I goto import the iif file into quick books (program)

 

1. "Quickbooks has detected that one of your items not have an account associated with it. Go to the Item List to mkae sure all items are associated with accounts"

2. "Error on line 10: Can't record transaction"

 

Now I double checked the item number in quick books and online and both item numbers are correct. I am putting in the code I received from excel when I opened the iif files, obviously I deleted the name and address I was using for security issues:-p

 

!CUST	NAME	BADDR1	BADDR2	BADDR3	BADDR4	BADDR5	SADDR1	SADDR2	SADDR3	SADDR4	SADDR5	PHONE1	PHONE2	FAXNUM	EMAIL	NOTE	CONT1	CONT2	CTYPE	TERMS	TAXABLE	TAXITEM	LIMIT	RESALENUM	REP	COMPANYNAME	SALUTATION	FIRSTNAME	LASTNAME
CUST	IceCreamWa-2		Ice Cream Warehouse					Ice Cream Warehouse																		Ice Cream Warehouse			
!TRNS	TRNSID	TRNSTYPE	DATE	ACCNT	NAME	CLASS	AMOUNT	DOCNUM	MEMO	CLEAR	TOPRINT	ADDR1	ADDR2	ADDR3	ADDR4	ADDR5	SADDR1	SADDR2	SADDR3	SADDR4	SADDR5	NAMEISTAXABLE	TERMS	SHIPVIA	REP	FOB	INVMEMO	PAYMETH	PONUM
!SPL	SPLID	TRNSTYPE	DATE	ACCNT	NAME	CLASS	AMOUNT	DOCNUM	MEMO	CLEAR	QNTY	PRICE	INVITEM	TAXABLE	PAYMETHOD	EXTRA													
!ENDTRNS																													
TRNS		INVOICE	5/9/2007	Accounts Receivable	IceCreamWa-2		12.13	7	Online Order	N	Y													Method not set			Thank you for your order!!		7
SPL		INVOICE	5/9/2007				-6.04		I. Rice - Syrup - Banana	N	-1	6.04	IR01-SRTBAN00-E	N															
SPL		INVOICE	5/9/2007							N																			
SPL		INVOICE	5/9/2007				-6.09			N	-1	6.09	Ice Cream Warehouse	N															
ENDTRNS																													

 

 

Now I just triple checked the item number IR01-SRTBAN00-E and it is correct on shopping cart and quick books, I used the export from Quick books orginally to set-up my products anyways using easy populate. Can you help me figure out the problem??

 

Thanks again for this awesome contrib

Link to comment
Share on other sites

Adam,

 

I am getting the following errors when I goto import the iif file into quick books (program)

 

1. "Quickbooks has detected that one of your items not have an account associated with it. Go to the Item List to mkae sure all items are associated with accounts"

2. "Error on line 10: Can't record transaction"

You have not filled in all your accounts in the QBI Configuration page. Specifically, COGS account:, Item income account:, and Item asset account:. They must match your accounts in Quickbooks -- see the QBI manual for more details.

 

Thanks,

Adam

Link to comment
Share on other sites

You have not filled in all your accounts in the QBI Configuration page. Specifically, COGS account:, Item income account:, and Item asset account:. They must match your accounts in Quickbooks -- see the QBI manual for more details.

 

Thanks,

Adam

 

Adam, thanks for the quick reply. I tried every possible thing I could try in the configurationg, can could not come up with the correct accounts to not get the errors, below in the codebox in the account list I exported from QuickBooks and then after codebox, is how te QBI is set up in the configuration, where did I do wrong?

 

Account	Type
Pioneer Bank	Bank
Accounts Receivable	Account Receivable
Inventory Asset	Other Current Asset
Prepaid Inventory	Other Current Asset
Undeposited Funds	Other Current Asset
Petty Cash	                Other Asset
Accounts Payable	Accounts Payable
MasterCard	Credit Card
Visa	                Credit Card
Payroll liabilities	Other Current Liability
Sales Tax Payable	Other Current Liability
SBA Loan Payable	Long Term Liability
Sales Income	Income
Cost of Goods Sold	Cost of Good Sold

 

 

Invoice Account=Accounts Receivable

Sales Receipt= Income

Item Asset Account=Other Current Asset

 

Thanks for the help too!

Link to comment
Share on other sites

Adam, thanks for the quick reply. I tried every possible thing I could try in the configurationg, can could not come up with the correct accounts to not get the errors, below in the codebox in the account list I exported from QuickBooks and then after codebox, is how te QBI is set up in the configuration, where did I do wrong?

 

Account	Type
Pioneer Bank	Bank
Accounts Receivable	Account Receivable
Inventory Asset	Other Current Asset
Prepaid Inventory	Other Current Asset
Undeposited Funds	Other Current Asset
Petty Cash	                Other Asset
Accounts Payable	Accounts Payable
MasterCard	Credit Card
Visa	                Credit Card
Payroll liabilities	Other Current Liability
Sales Tax Payable	Other Current Liability
SBA Loan Payable	Long Term Liability
Sales Income	Income
Cost of Goods Sold	Cost of Good Sold

Invoice Account=Accounts Receivable

Sales Receipt= Income

Item Asset Account=Other Current Asset

 

Thanks for the help too!

From you list, it looks like your sales income account is called "Sales Income", not "Income". This is what you need to put in the QBI configuration field "Item income account:". If you open any item in Quickbooks, it is what is in the box "Income Account".

 

Thanks,

Adam

Link to comment
Share on other sites

Pat,

 

Check that your settings in the osC file /catalog/admin/configure.php are correct. Specifically, DIR_FS_DOCUMENT_ROOT, DIR_WS_ADMIN, DIR_FS_ADMIN, HTTP_SERVER, and HTTPS_SERVER. QBI reads the settings from osC, so in almost all cases where QBI can not find things, it is an osC setup issue.

 

Also, if your secure server address is not the same as your non-secure address, then try disabling SSL in the Admin configuration while you install QBI. I'm not sure how you would get directed to the flickr.com site, so something is strange about your osC setup.

 

Thanks,

Adam

 

First of all I'd like to thank you for helping me out before. I ended up just opening up the sql file and manually importing it via phpMyAdmin. I'm going through the setup right now and I'm trying to import the iff file (it says "Found iif import file qbi_input/lists.iif. Import now? "), but when I click the button "Import iff File" it just brings me back to the same page. I double checked and the /catalog/admin/qbi_input is set to 755 permission. Any pointers?

 

Thanks again for your help,

Patrick

Link to comment
Share on other sites

First of all I'd like to thank you for helping me out before. I ended up just opening up the sql file and manually importing it via phpMyAdmin. I'm going through the setup right now and I'm trying to import the iff file (it says "Found iif import file qbi_input/lists.iif. Import now? "), but when I click the button "Import iff File" it just brings me back to the same page. I double checked and the /catalog/admin/qbi_input is set to 755 permission. Any pointers?

 

Thanks again for your help,

Patrick

Patrick,

It needs to be set to 775 or 777.

 

I'm going to answer by private message, because I don't think I can help much more without actually seeing your installation.

 

Thanks,

Adam

Edited by adam5532
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...