Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

QBI Quickbooks Import


adam5532

Recommended Posts

I had QBI installed and now while trying to set it up., I can not find the upload option( trying to upload lists.ii file).

 

Is this because of incomplete installation?

 

Please help.

 

hello. is there something i can call you besides dumb? anyway, if you can pull a page from your catalog/admin/qbi_create.php, that's the interface. if you get a 404, something is wrong with your install.

Link to comment
Share on other sites

I used ftp software such as WS_FTP or maybe smartftp (not sure about the name) to manually upload the list.iif file to the admin/import folder.

 

I know that if your webhosting company has cpanel, you may upload it from File Manager as well.

 

I don't know if this answers your questions.

 

Wendy

Hey!

 

Dumb is fine :0) ....I am totally insensitive to it!!

 

I can pull that page up......On the create iiF page, I just see download iiF file options.....nothing for uploadthen when i click on "setup" ..this is what I see "Did not find iif import file."

Link to comment
Share on other sites

help,

if the previous was dumb I am dumber or ignorant

 

but have 6000 items in my cart with from 1 to 16 attrbutes each. it would take month to match to QB

 

in the manual paragraph 15:

Preparing Quickbooks Using the Product Import Utility:

If you have never used Quickbooks with your on-line store, and you wish to import all your store items into

Quickbooks, click the ?Create iif file from all shopping cart products? button in the QBI Utilities to make an

iif file that you can import into Quickbooks. This will work properly with shopping cart products that have

attributes, but will not work for shopping cart products that you wish to match to Quickbooks item groups.

Unless you have a large number of items that are not already in Quickbooks, and you will not wish to

change the names or use item groups, this method is not recommended. It is preferable to add your items

directly in Quickbooks, and then match them to the items in the shopping cart using the ?Match|Products?

page as explained above. Note that the item import, if you choose to do it, only needs to be done one time.

 

I can't find the Utilities "Create iif file from shopping cart products".

 

Can anyone, please tell me where ths is located?

pm'd adam but he must be very busy as he has not been able to answer me since the 1st of the month.

will really appreciate if someone could tell me what to do.

 

Michael

Link to comment
Share on other sites

I can't find the Utilities "Create iif file from shopping cart products".

 

those come from QuickBooks. you are working in Quickbooks to create IIF files for import into QBI. when you have created the files described, you will upload them to your catalog/admin/qbi_import/ directory.

Link to comment
Share on other sites

thank you, actually that utiity is located in qbi as products download.

Now that I have everything workng in OSC and matched the products I would like to import my sales into quickbooks

everytime I do quickbooks tells me" soory we encountered an error" and shuts tself down.

does anyone have a suggestion PLEASE.

Mchael

Link to comment
Share on other sites

Adam,

 

Thanks for your quick reply. :-)

 

Sure I can send you product iif file for testing. I'm using premier version and would love to test code for you.

 

One suggestion for this contribution is that when create iif file for orders, is it possible to let the user decide for orders xxx to orders xxx. I have a couple of thousands of orders to export and haven't had a success yet since it takes too long and the admin section automatically logged off before it even completed.

Hi Wendy,

 

Yes, I'm aware of that problem. I will address it after 3.0 is released.

 

In the meantime, you can work around it manually:

 

File: qbi_create.php

Line: Add after line 56 in the section labeled "// Products"

$iif_form->where_clause("products_id>=1 AND products_id<1000");

 

This adds an additional "where clause" to the query to limit the number of products retrieved. You'll have to edit the code repeatedly to download groups of products, say 1-999, 1000-1999, etc.

I'm switching from Peachtree to Quickbooks so I'm quite new to Quickbooks. Is it true that quickbooks can't export transactions? I didn't see transactions as an option under Export section. I was just trying to see how an assembly looks like in a transaction in iif file but seems like Quickbooks can't export transactions to iif file.

True. You can not export transactions, and technical support for iif is lacking. The example files are old and do not include assemblies. QBI version 3.0 will be able to use XML as well as iif.

 

Thanks,

Adam

Link to comment
Share on other sites

Worked right out of the box!

I am trying to get streamline for next year so I installed this contribution. There were no problems that I could tell except the fact that this year I was learning how to use quickbooks and what a learning curve which i still did not conquer. I am considering either upgrading to quickbooks pro or downgrading to simple start. Either way I will start with a entire new quickbooks file which leads to the questions below.

 

Any advise on keeping everything in sync.

I would like to keep some sold items on my site, is there a way that code can be written that the iif file does not import data from this year?

If I decide on quickbooks simple start will this be compatable?

How long do you think I will be able to import iif files as apposed to the new file format xml and will it be hard to update.

 

Thank you

Elizabeth

Link to comment
Share on other sites

I have 2 more questions. On my website I do not put my items in the same category as I do in quickbooks. When adding items to the shop will it match only if I have the categories and subcategories match?

 

I sometime have an item in more than one category, what happens when I do this?

 

ok make that 3 questions java script:emoticon(':-"', 'smid_6')

smilie

 

At times I will change a category name to try for better placement on the search engines. What happens to matching of items between qb and shop here?

 

thanks

Link to comment
Share on other sites

Worked right out of the box!

I am trying to get streamline for next year so I installed this contribution. There were no problems that I could tell except the fact that this year I was learning how to use quickbooks and what a learning curve which i still did not conquer. I am considering either upgrading to quickbooks pro or downgrading to simple start. Either way I will start with a entire new quickbooks file which leads to the questions below.

Simple Start does not import iif files and therefore can not be used with QBI. I would suggest QUickbooks Pro, which at US $149 should be affordable for most US businesses.

You should be able to open or upgrade your data file from regular Quickbooks -- there's no need to start with a new file.

Any advise on keeping everything in sync.

I would like to keep some sold items on my site, is there a way that code can be written that the iif file does not import data from this year?

QBI will only put new sales and new products in the iif files each time you download, unless you specifically choose to download all previous sales or products. Currently, you can't limit which previous sales or products download from the interface, but you can easily do this by adding a "where clause" in the code (see my previous post).

If I decide on quickbooks simple start will this be compatable?

No

How long do you think I will be able to import iif files as apposed to the new file format xml and will it be hard to update.

I don't know. Intuit says they will eventually be dropping the iif format, but they have still included it in the 2006 products. QBI will soon be able to export using the XML format. QBI is easy to update, and you won't need to change anything in QB. Note that for the XML format, you'll need Quickbooks Pro or higher.

 

- Adam

Link to comment
Share on other sites

I have 2 more questions. On my website I do not put my items in the same category as I do in quickbooks. When adding items to the shop will it match only if I have the categories and subcategories match?

 

I sometime have an item in more than one category, what happens when I do this?

 

ok make that 3 questions java script:emoticon(':-"', 'smid_6')

smilie

 

At times I will change a category name to try for better placement on the search engines. What happens to matching of items between qb and shop here?

 

thanks

Quickbooks doesn't have categories, so the category that you use in oSC does not matter.

 

Thanks,

Adam

Link to comment
Share on other sites

I am having problems with Matching the products. I most likely self created it. While setting up QBI in oscommerce, I imported products from quickbooks. I have limited products but each product has more than 50 colors. While matching products,I looked at the oscommerce part and the liked the way QBI had generated products. Each color is a different product. This will help me in maintaining the inventory in quickbooks. I decided to export products list from oscommerce to quickbooks and then re-import it into oscommerce. However, while matching in oscommerce, it looks like oscommerce didnt register new changes.

 

Can someone help explain this. Is there a fix?

Link to comment
Share on other sites

I am having problems with Matching the products. I most likely self created it. While setting up QBI in oscommerce, I imported products from quickbooks. I have limited products but each product has more than 50 colors. While matching products,I looked at the oscommerce part and the liked the way QBI had generated products. Each color is a different product. This will help me in maintaining the inventory in quickbooks. I decided to export products list from oscommerce to quickbooks and then re-import it into oscommerce. However, while matching in oscommerce, it looks like oscommerce didnt register new changes.

 

Can someone help explain this. Is there a fix?

QBI does not in any way alter or update the products in osC. QBI reads in QB products only for the purpose of matching. You need to go to the Set Up:Products page to make it update any changes in your QB products iif file, then go back to Match:Products. All your previous work is saved, as long as the products still exist in QB.

 

It doesn't really make sense to export the product list and import it into QB, then export the products again back into QBI. Choose one method or the other, but not both. If you export your osC products into QB, using QBI, then your QB products have the same names as they do in osC and matching is not necessary.

 

Thanks,

Adam

Link to comment
Share on other sites

Adam,

 

Thanks so much. It worked. :-) I was having time out problem with the order iif file so I put it in the order creation section and it worked. It can easily handle 500 orders at a time without any problem. I'm guessing order iif may be much larger than product iif.

 

Since I have more than 1000 products, I found that when matching products it takes much less time if the listed products are sorted by product id rather than product model. I then found the merit of this contribution that if the product name is the same in OSC and Quickbooks, I don't have to match at all.

 

I also took a look at the Assembly option. I think assembly is much easier to handle than Group. Assembly is basically an item you have to manually build in advance. Once you build it in Quickbooks, it has nothing to do with the components any more. So it actually is one item. In iif file, assembly has extra lines to include all the components. But the contribution can certainly just ignore those lines and take just one line which is the same as a regular item. This may not take much work. Too bad I don't have any programming knowlege other than copy/paste and comment out lines. :-( What I did in my situation is to alter the iif file manually to elimate those extra lines and make it look same as a regular item. I changed one place in the code to allow assembly type to be uploaded. Then assembly items were loaded properly and ready for match (I will PM you a file with assembly item a little later).

 

The tricky part is the group item I think. Group is similar to assembly but it assembles on fly, that is when an group item is posted, it actually posts its components. I'm guessing you may have to add extra tables or at least extra columns to handle group items.

 

Again, thank you for such a great contribution and your constant technical support.

 

Wendy

 

File: qbi_create.php

Line: Add after line 56 in the section labeled "// Products"

$iif_form->where_clause("products_id>=1 AND products_id<1000");

 

This adds an additional "where clause" to the query to limit the number of products retrieved. You'll have to edit the code repeatedly to download groups of products, say 1-999, 1000-1999, etc.

 

True. You can not export transactions, and technical support for iif is lacking. The example files are old and do not include assemblies. QBI version 3.0 will be able to use XML as well as iif.

 

Thanks,

Adam

Link to comment
Share on other sites

Adam,

 

Thanks so much. It worked. :-)

Glad it worked!

(I will PM you a file with assembly item a little later).

Great, thanks!

The tricky part is the group item I think. Group is similar to assembly but it assembles on fly, that is when an group item is posted, it actually posts its components. I'm guessing you may have to add extra tables or at least extra columns to handle group items.

Group support and the necessary tables are already built in. Groups should work properly -- let me know if you have any problem (if you are using groups). I am using groups with QBI in my online store.

Again, thank you for such a great contribution and your constant technical support.

Thanks!

 

- Adam

Link to comment
Share on other sites

Yes, it doesnt make any sense . agree. didnt realize i dint have to match products when they are identical.

I went ahead and downloaded orders file but got error.message was " error on line 13"

How do i know if orders downloaded properly. Check sales receipts

 

Thanks for your help.

 

 

QBI does not in any way alter or update the products in osC. QBI reads in QB products only for the purpose of matching. You need to go to the Set Up:Products page to make it update any changes in your QB products iif file, then go back to Match:Products. All your previous work is saved, as long as the products still exist in QB.

 

It doesn't really make sense to export the product list and import it into QB, then export the products again back into QBI. Choose one method or the other, but not both. If you export your osC products into QB, using QBI, then your QB products have the same names as they do in osC and matching is not necessary.

 

Thanks,

Adam

Link to comment
Share on other sites

Yes, it doesnt make any sense . agree. didnt realize i dint have to match products when they are identical.

I went ahead and downloaded orders file but got error.message was " error on line 13"

How do i know if orders downloaded properly. Check sales receipts

 

Thanks for your help.

Open the iif file in Excel. Look at line 13, or the order above it. Compare it to the invoice in osC. You'll likely find that either a line item is missing, you haven't set up a discount item, or that you haven't set up shipping or sales tax properly in QBI.

 

Thanks,

Adam

Link to comment
Share on other sites

Open the iif file in Excel. Look at line 13, or the order above it. Compare it to the invoice in osC. You'll likely find that either a line item is missing, you haven't set up a discount item, or that you haven't set up shipping or sales tax properly in QBI.

 

Thanks,

Adam

 

 

Hi!

 

Line 13 says "Endtrns"

line 12 hs sales tax info...it seems to be correct

line 11 has customer comments"..please send it by...."

 

Did this line cause the error?

 

Also is it possible to download all previous order or only current orders can be downloaded.

 

 

Thanks for your help

Link to comment
Share on other sites

Adam...

 

Based on what I see it sounds like a great contibution.

 

Here is my site: http://www.chaiteadepot.com

 

I have a small problem.

 

I have checked and double checked the installed the 2.10b files are in the right spots. I have loaded the whole admin section to the server twice (Mine is Admin on this server since 'admin' takes you to the server admin control panel). I tried to let the your files execute the SQL and I tried executing within phpMyAdmin. I have checked all file permissions.

 

All looks good based on the instructions. I'm sure it's just an ID-10-T error.

 

When I click the QBI link in the Catalog section i get this:

 

Warning: main(includes/classes/navigation_history.php): failed to open stream: No such file or directory in /home/virtual/site24/fst/var/www/html/includes/application_top.php on line 126

 

Fatal error: main(): Failed opening required 'includes/classes/navigation_history.php' (include_path='.:/php/includes:/usr/share/php') in /home/virtual/site24/fst/var/www/html/includes/application_top.php on line 126

 

Any all help and advice is appreciated!

Link to comment
Share on other sites

Adam,

 

Thanks so much. It worked. :-) I was having time out problem with the order iif file so I put it in the order creation section and it worked. It can easily handle 500 orders at a time without any problem. I'm guessing order iif may be much larger than product iif.

 

Since I have more than 1000 products, I found that when matching products it takes much less time if the listed products are sorted by product id rather than product model. I then found the merit of this contribution that if the product name is the same in OSC and Quickbooks, I don't have to match at all.

 

I also took a look at the Assembly option. I think assembly is much easier to handle than Group. Assembly is basically an item you have to manually build in advance. Once you build it in Quickbooks, it has nothing to do with the components any more. So it actually is one item. In iif file, assembly has extra lines to include all the components. But the contribution can certainly just ignore those lines and take just one line which is the same as a regular item. This may not take much work. Too bad I don't have any programming knowlege other than copy/paste and comment out lines. :-( What I did in my situation is to alter the iif file manually to elimate those extra lines and make it look same as a regular item. I changed one place in the code to allow assembly type to be uploaded. Then assembly items were loaded properly and ready for match (I will PM you a file with assembly item a little later).

 

The tricky part is the group item I think. Group is similar to assembly but it assembles on fly, that is when an group item is posted, it actually posts its components. I'm guessing you may have to add extra tables or at least extra columns to handle group items.

 

Again, thank you for such a great contribution and your constant technical support.

 

Wendy

 

Wendy,

 

Do you have assemblies working correctly?

 

If you could post how you are doing this in a little more detail I would greatly appreciate it.

 

Thanks

 

Ed

Link to comment
Share on other sites

Well, I have finally gotten around to getting this contribution set up, and all seems to be going well. I have one (maybe two) minor problem so far, and trying to work the learning curve of Quickbooks and this, I haven't been able to figure it out yet. I have searched through the thread here and haven't seen an instance of this (although I might have missed it).

 

Here is the situation: I have orders from both in state and out of state. In-state orders are charged sales tax on both merchandise AND shipping (Florida sales tax). This works fine on instate orders. However, when I have an out of state order, it is also coming in as shipping being taxed. This doesn't cause a problem with totals or anything, but when I pull my sales tax report, it is included in the taxable sales, which it should not.

 

Products for out of state customers come in as non-taxable versus products for in state coming in as taxable, but I can't get shipping to do the same. When I set the checkbox in QBI to not tax shipping, then all orders are imported as non-tax. Is there a way to make the shipping work like the products? Did I miss something setting this up?

 

Another issue, and I don't know if this is even possible, is the screwy way Florida does sales tax. They have a base rate of 6%, then each county has an additional tax of anywhere from 0.5% to 1.5%. I have these all set up in osC to calculate the tax correctly, but I only have the option to set one tax rate in QBI. Right now I haven't set up all the counties in QB, but if this is doable, I can set up QB with the county tax rates also. Is this something to look forward to in the next version?

 

Thanks for your help, this is an awesome contribution! :lol:

 

John

Link to comment
Share on other sites

One more item while I'm at it. I am using the Multi-Vendor Shipping contribution, and as such, have my vendors associated with each product in the database. I need to rewrite the query for the qbi_engine_prod.php file, but can't seem to get it right.

 

The vendor has an id in the PRODUCTS table (vendors_id) which is associated with the same field in the VENDORS table. I need to pull the vendors_name field from the VENDORS table for each product. Here is what I have for the query, but obviously it doesn't work. Any idea how tyo correct this? I am not the best query writer, so any help in pointing me in the right direction would be appreciated.

 

You can see I have added the vendor's name to the product loop through and the add product line sections also.

 

 

// Read product data
 (ITEM_ACTIVE==1) ? $whereclause="AND products_status='1' " : $whereclause=""; 
 $resultpd = tep_db_query("SELECT p.*, v.vendors_id, v.vendors_name FROM ".TABLE_PRODUCTS." AS p, ".TABLE_PRODUCTS_DESCRIPTION." AS pd, ".TABLE_VENDORS." AS v WHERE p.products_id=pd.products_id AND v.vendors_id=p.vendors_id AND language_id='$languages_id' AND qbi_imported='$qbimported' ".$whereclause."ORDER BY products_model");

// Check for products
 if ($myrowpd=tep_db_fetch_array($resultpd)) {

// Open output file
$handle=fopen("qbi_output/qbi_prod.iif", "wb");

// Create iif header
$prod_data="!INVITEM\tNAME\tINVITEMTYPE\tDESC\tPURCHASEDESC\tACCNT\tASSETACCNT\tCOGSACCNT\tPRICE\tCOST\tTAXABLE\tPAYMETH\tTAXVEND\tTAXDIST\tPREFVEND\tREORDERPOINT\tQNTY\tEXTRA\n";

// Set item type
if (ITEM_IMPORT_TYPE==2) {
  $item_type="SERV";
} elseif (ITEM_IMPORT_TYPE==1) {
  $item_type="PART";
} else {
  $item_type="INVENTORY";
}  

// Loop through each product
do {
  $prod_id=$myrowpd["products_id"];
  $prod_price=round($myrowpd["products_price"],2);
  $myrowpd["products_tax_class_id"]>0 ? $ptaxable="Y" : $ptaxable="N";
  $prod_prefix="";
  $prod_name=$myrowpd["products_model"];
  $prod_desc=$myrowpd["products_name"];
  $prod_quan=$myrowpd["products_quantity"];
  $prod_cost=$myrowpd["vendors_product_price"];
  $vendor_name=$myprod["v.vendors_name"];

// Add product line without attributes
  $prod_data.="INVITEM\t$prod_name\t$item_type\t$prod_desc\t$prod_desc\t".ITEM_ACCT."\t".ITEM_ASSET_ACCT."\t".ITEM_COG_ACCT."\t$prod_price\t$prod_cost\t$ptaxable\t\t\t\t$vendor_name\t\t$prod_quan\t\n";

 

Thanks again,

 

John

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