Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

QBI Quickbooks Import


adam5532

Recommended Posts

I had this SAME problem. Thanks ADAM for the idea where to check!

 

The fix:

If you have an admin contrib installed, go to the Administrator section in the control panel, then File Access, then Catalogue (click on it 2x just in case), on the right hand side should be a drop down with all the files that you need to grant access to. Once you do that, the quick books importer works!

 

Thanks again adam for the GREAT contrib!

 

 

is it only the _config file we need to add to the list?

Link to comment
Share on other sites

ok, I lied, got excited too soon, when i click on the left side, it asks if i want to install it, i say yes, it tells me this

 

Error: Database upgrade file not found.

Link to comment
Share on other sites

ok, i got it to work, i forgot to upload the .sql files.

 

Quick question, does this export to quickbooks also, or just the quickbooks to osc import?

The purpose of QBI is to export orders from osC and import them into QB. QBI imports various QB data only so that it know what QB wants and can properly prepare the iif file, and so that you can match osC items to QB items.

 

Thanks,

Adam

Link to comment
Share on other sites

The purpose of QBI is to export orders from osC and import them into QB. QBI imports various QB data only so that it know what QB wants and can properly prepare the iif file, and so that you can match osC items to QB items.

 

Thanks,

Adam

 

wonderful, so simply put i can export FROM osc and import INTO QB?

;) :blush:

Link to comment
Share on other sites

I am having an issue where attributes are showing up as products when I try and match. Because of this, QBI wants me match 1200 products/attributes it is pulling from my OSC database..where I only wanna match the 400 products.

 

Is there a way to fix this?

Link to comment
Share on other sites

I am trying to export the products that are in osC to QB through the .iif file. When I export the products .iif file and open it in Excel, there are no entries under NAME. QB says that the .iif file is imported successfully but the products do not show up in QB. You cannot add a product to QB without an Item Name. Since the Excel file does not have any entiries under NAME. I figure that this is the problem. How do I make it so that the export .iif file of the products has entries under NAME.

 

Thanks

Link to comment
Share on other sites

I am trying to export the products that are in osC to QB through the .iif file. When I export the products .iif file and open it in Excel, there are no entries under NAME. QB says that the .iif file is imported successfully but the products do not show up in QB. You cannot add a product to QB without an Item Name. Since the Excel file does not have any entiries under NAME. I figure that this is the problem. How do I make it so that the export .iif file of the products has entries under NAME.

 

Thanks

You must have a "Products Model" for each item in your store. osC does not require that you fill this in, but this is how QBI identifies the product. You need to Edit each product and fill in that form field.

 

Thanks,

Adam

Link to comment
Share on other sites

After the install, I was prompted to go to the config. I get to the config screen, and click Update. However, it brings me back to the same screen. Any ideas what I did wrong?

 

Thanks.

Link to comment
Share on other sites

After the install, I was prompted to go to the config. I get to the config screen, and click Update. However, it brings me back to the same screen. Any ideas what I did wrong?

 

Thanks.

Try going into the qbi_config MySQL database table and change qbi_active to 1.

Thanks,

Adam

Link to comment
Share on other sites

Try going into the qbi_config MySQL database table and change qbi_active to 1.

Thanks,

Adam

Okay, I've now set qbi_config_active to 1 and it appears to work. Was that in the documentation somewhere and I overlooked it?

 

Thanks.

Link to comment
Share on other sites

I am having an issue where attributes are showing up as products when I try and match. Because of this, QBI wants me match 1200 products/attributes it is pulling from my OSC database..where I only wanna match the 400 products.

 

Is there a way to fix this?

Each product / attribute combination is actually a different inventory item (Shirt:Red, Shirt:Green etc), so it is on purpose that each one is listed separately. osC currently can not properly track inventory by attributes, but QB can. You can match them to different QB items, QB item:subitem, or let them flow through without matching to a like item:subitem in QB.

 

I will however put it on the feature request list to add the ability to match tems with attributes as a single item.

 

Thanks,

Adam

Link to comment
Share on other sites

Hi Guys,

 

Fighting my way through getting QBI to function. I have followed the install and all seems to be working. I have tested the IIF imports with QB2006. Customers and product seem to load in fine but I am getting an error during order import " error on line 18: Can't import invalid transaction"

I have read through most of the 50 pages here and cant seem to find anything that relates to this error. I have tried several different configurations, monkied with tax settings ect... Cant seem to find the issue.

If I view the IIF with Excel line 18 is "ENDTRNS", not sure if this is truely line 18? Here is a sample IIF with the error (cust info changed to protect the innocent :D ):

 

!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 Blow-291 Joe Blow 1111 Foxdale Circle Colorado Springs Colorado 80919 Joe Blow 1111 Foxdale Circle Colorado Springs Colorado 80919 (111) 111-1111 [email protected] Joe Blow N Sales Tax Payable Joe Blow

!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/3/2006 Web Sales Blow-291 43.73 1813 N Y Joe Blow 1111 Foxdale Circle Colorado Springs Colorado 80919 Joe Blow 1111 Foxdale Circle Colorado Springs Colorado 80919 N Method not set 1813

SPL INVOICE 5/3/2006 Web Sales -35 Servo Crimp Tool N -1 35 AE-TMC-003 N

SPL INVOICE 5/3/2006 N

SPL INVOICE 5/3/2006 Sales -8.73 Shipping Charge N -1 8.73 shipping N

SPL INVOICE 5/3/2006 N

SPL INVOICE 5/3/2006 Thanks, Todd!

Transaction ID: 20A44961BX5508327

Payment Type: PayPal Direct Payment

Payment Status: Completed

AVS Code: Yes Address and five-digit ZIP

CVV2 Code: Match CVV2 N

SPL INVOICE 5/3/2006 Sales Tax Payable Florida Department of Revenue 0 N 6.50% Sales Tax Payable N AUTOSTAX

ENDTRNS

Any thoughts as to the source of the error??

TIA

Todd

Link to comment
Share on other sites

Support issue:

 

I'm trying to modify a .iff file to import a different set of data. Basically, instead of the entire customer list, I want to be able to import the most recent customers who have not been imported without importing all the new orders. Unfortunately, I am running into walls all over the place.

 

Here's the deal. I first tried exporting the whole customer list, just grabbing the first few lines of the file, making that into a new .iif file, and importing that to see if I can do just a few entries instead of the whole list. That doesn't seem to work. It won't import any customers unless I import the whole list. I thought perhaps it might have something to do with me not importing the orders first, but I realized I am able to import the entire list of customers into a blank company. I really just don't know what's preventing me from grabbing just a few or the most recent customers.

 

I can't find good documentation on the web for structuring my .iif files, and Quickbooks is confusing enough without trying to sync it with osC. Any help would be appreciated, and I'll continue to search this thread and the rest of the net.

 

Thanks,

Ryan

Link to comment
Share on other sites

I got the same error as you did. What I found was if you put anything other than "Accounts Receivable" into the "Invoice Account" option in the QBI config, it gives you this error. I see you changed yours to "Web Sales". I need a way to be able to use a Current Asset account so I don't have to manually receive every payment once I import all my orders. any ideas?

 

 

 

 

 

Hi Guys,

 

Fighting my way through getting QBI to function. I have followed the install and all seems to be working. I have tested the IIF imports with QB2006. Customers and product seem to load in fine but I am getting an error during order import " error on line 18: Can't import invalid transaction"

I have read through most of the 50 pages here and cant seem to find anything that relates to this error. I have tried several different configurations, monkied with tax settings ect... Cant seem to find the issue.

If I view the IIF with Excel line 18 is "ENDTRNS", not sure if this is truely line 18? Here is a sample IIF with the error (cust info changed to protect the innocent :D ):

 

!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 Blow-291 Joe Blow 1111 Foxdale Circle Colorado Springs Colorado 80919 Joe Blow 1111 Foxdale Circle Colorado Springs Colorado 80919 (111) 111-1111 [email protected] Joe Blow N Sales Tax Payable Joe Blow

!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/3/2006 Web Sales Blow-291 43.73 1813 N Y Joe Blow 1111 Foxdale Circle Colorado Springs Colorado 80919 Joe Blow 1111 Foxdale Circle Colorado Springs Colorado 80919 N Method not set 1813

SPL INVOICE 5/3/2006 Web Sales -35 Servo Crimp Tool N -1 35 AE-TMC-003 N

SPL INVOICE 5/3/2006 N

SPL INVOICE 5/3/2006 Sales -8.73 Shipping Charge N -1 8.73 shipping N

SPL INVOICE 5/3/2006 N

SPL INVOICE 5/3/2006 Thanks, Todd!

Transaction ID: 20A44961BX5508327

Payment Type: PayPal Direct Payment

Payment Status: Completed

AVS Code: Yes Address and five-digit ZIP

CVV2 Code: Match CVV2 N

SPL INVOICE 5/3/2006 Sales Tax Payable Florida Department of Revenue 0 N 6.50% Sales Tax Payable N AUTOSTAX

ENDTRNS

Any thoughts as to the source of the error??

TIA

Todd

Link to comment
Share on other sites

Support issue:

 

I'm trying to modify a .iff file to import a different set of data. Basically, instead of the entire customer list, I want to be able to import the most recent customers who have not been imported without importing all the new orders. Unfortunately, I am running into walls all over the place.

 

Here's the deal. I first tried exporting the whole customer list, just grabbing the first few lines of the file, making that into a new .iif file, and importing that to see if I can do just a few entries instead of the whole list. That doesn't seem to work. It won't import any customers unless I import the whole list. I thought perhaps it might have something to do with me not importing the orders first, but I realized I am able to import the entire list of customers into a blank company. I really just don't know what's preventing me from grabbing just a few or the most recent customers.

 

I can't find good documentation on the web for structuring my .iif files, and Quickbooks is confusing enough without trying to sync it with osC. Any help would be appreciated, and I'll continue to search this thread and the rest of the net.

 

Thanks,

Ryan

Ryan,

You can easily edit the customers.iif. Just be sure to always include the customer header line -- then there should be no problem.

Thanks,

Adam

Link to comment
Share on other sites

I got the same error as you did. What I found was if you put anything other than "Accounts Receivable" into the "Invoice Account" option in the QBI config, it gives you this error. I see you changed yours to "Web Sales". I need a way to be able to use a Current Asset account so I don't have to manually receive every payment once I import all my orders. any ideas?

Please re-read the instruction manual for directions on how to set-up and match payment methods, and how to set the QBI configuration to import paid orders as sales receipts. Then it will not be necessary to manually receive any payments.

 

Thanks,

Adam

Link to comment
Share on other sites

Yeah, my bad on the import screw-up, and thanks for your reply Adam. I was using Crimson editor and forgot that for purposes of uploading/editing in vi I had set it to save tabs as two spaces. No tabs...no import. :blush:

Link to comment
Share on other sites

i've followed the instructions for installing the QBI contribution and when i attempt to import the QBI database information i get the following error:

 

Quickbooks Import QBI

1 - Can't create/write to file './oscommerce/qbi_config.frm' (Errcode: 9)

 

CREATE TABLE qbi_config ( qbi_config_id smallint(5) unsigned NOT NULL auto_increment, qbi_config_ver decimal(6,2) unsigned NOT NULL default '0.00', qbi_qb_ver smallint(5) unsigned NOT NULL default '2003', qbi_dl_iif tinyint(2) unsigned NOT NULL default '1', qbi_prod_rows smallint(5) unsigned NOT NULL default '5', qbi_log tinyint(2) unsigned NOT NULL default '0', qbi_status_update tinyint(2) unsigned NOT NULL default '0', qbi_cc_status_select tinyint(3) unsigned NOT NULL default '1', qbi_mo_status_select tinyint(3) unsigned NOT NULL default '1', qbi_email_send tinyint(2) unsigned NOT NULL default '0', qbi_cc_clear tinyint(2) unsigned NOT NULL default '0', orders_status_import int(11) NOT NULL default '1', orders_docnum varchar(36) NOT NULL default '%I', orders_ponum varchar(36) NOT NULL default '%I', cust_nameb varchar(41) NOT NULL default '%C10W-%I', cust_namer varchar(41) NOT NULL default '%L10W-%I', cust_limit int(10) unsigned NOT NULL default '0', cust_type varchar(48) NOT NULL default '', cust_state tinyint(2) unsigned NOT NULL default '1', cust_country tinyint(2) unsigned NOT NULL default '0', cust_compcon tinyint(2) unsigned NOT NULL default '1', cust_phone tinyint(2) unsigned NOT NULL default '0', invoice_acct varchar(30) NOT NULL default 'Accounts Receivable', invoice_salesacct varchar(30) NOT NULL default 'Undeposited Funds', invoice_toprint tinyint(2) unsigned NOT NULL default '1', invoice_pmt tinyint(2) unsigned NOT NULL default '0', invoice_termscc varchar(30) NOT NULL default '', invoice_terms varchar(30) NOT NULL default '', invoice_rep varchar(41) NOT NULL default '', invoice_fob varchar(13) NOT NULL default '', invoice_comments tinyint(2) unsigned NOT NULL default '1', invoice_message varchar(128) NOT NULL default '', invoice_memo varchar(128) NOT NULL default '', item_acct varchar(30) NOT NULL default '', item_asset_acct varchar(30) NOT NULL default 'Inventory Asset', item_class varchar(30) NOT NULL default '', item_cog_acct varchar(30) NOT NULL default 'Cost of Goods Sold', item_osc_lang tinyint(2) unsigned NOT NULL default '0', item_match_inv tinyint(2) unsigned NOT NULL default '1', item_match_noninv tinyint(2) unsigned NOT NULL default '0', item_match_serv tinyint(2) unsigned NOT NULL default '0', item_default tinyint(2) unsigned NOT NULL default '0', item_default_name varchar(40) NOT NULL default '', item_import_type tinyint(2) unsigned NOT NULL default '0', item_active tinyint(2) unsigned NOT NULL default '0', ship_acct varchar(30) NOT NULL default '', ship_name varchar(30) NOT NULL default '', ship_desc varchar(36) NOT NULL default '', ship_class varchar(30) NOT NULL default '', ship_tax tinyint(2) unsigned NOT NULL default '0', tax_on tinyint(2) unsigned NOT NULL default '0', tax_lookup tinyint(2) unsigned NOT NULL default '0', tax_name varchar(30) NOT NULL default '', tax_agency varchar(30) NOT NULL default '', tax_rate float NOT NULL default '0', pmts_memo varchar(128) NOT NULL default '', prods_sort tinyint(2) unsigned NOT NULL default '0', prods_width smallint(5) unsigned NOT NULL default '48', qbi_config_active tinyint(2) NOT NULL default '0', qbi_config_added timestamp(14) NOT NULL, PRIMARY KEY (qbi_config_id) ) TYPE=MyISAM;

 

i've tried grepping for the error code and such but haven't found anything. i'm not even sure which directory this permission error is coming from since it's using a relative path. i'm not sure i'm posting in the right place :blink: .

 

just in case it's relevant, i'm running mysql-server-4.0.24 and php5-core-5.0.4 on openbsd 3.8-release. any advice appreciated.

Link to comment
Share on other sites

a "find / -name *.frm" points me to /var/mysql/oscommerce/qbi_config.frm, which has been created but is of size zero. it seems something is not working right with the mysql interaction. /var/mysql and all the files under it are owned by user/group _mysql/_mysql with mysql running chroot-ed to /var/mysql.

 

this is odd since oscommerce installed fine via the mysql credentials i have setup.

Link to comment
Share on other sites

a "find / -name *.frm" points me to /var/mysql/oscommerce/qbi_config.frm, which has been created but is of size zero. it seems something is not working right with the mysql interaction. /var/mysql and all the files under it are owned by user/group _mysql/_mysql with mysql running chroot-ed to /var/mysql.

 

this is odd since oscommerce installed fine via the mysql credentials i have setup.

Jacob,

 

This sounds like an issue on your server. I'm not even sure what a .frm file is -- QBI doesn't use them.

 

You can manually install the database files by executing the SQL in the file qbi_0-00_2-10.sql using phpMyAdmin or whatever MySQL interface your host provides.

 

Thanks,

Adam

Link to comment
Share on other sites

adam,

 

thanks for your speedy reply. just this morning i noticed that the mysql database on my server had jammed up, so i restarted it. now, magically, the install went without event and all is working :).

 

i look forward to using your contribution. i will donate if it works as advertised.

 

cheers,

jake

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...