Setting TAX CLASS to TAXABLE ITEM (ON ALL PRODUCTS)

[m2d^]

Is there any fast way to set the tax class of all my products to TAXABLE ITEM?

i have got over 10000 products but all have the tax class to non, any quick solution?

Best Regards,

Francis Doody

[jpf]

3 options:

SQL statement.
Export/Import.
Change/edit the non tax class to be a tax class.

Last one would be easiest.

[m2d^]

thank you for your reply!

can you guide me to make the non tax to be classed?

Regards,

Francis Doody

[jpf]

In admin - Tax classes..... change name then setup taxes for that class.

[m2d^]

thank you for your sopport,
all item are set to: none.

there is 2 class:

non taxable
taxable

on the products there is 3:
--none--
non taxable
taxable

whatever i do, makes no diference :X

any solution?

[jpf]

Before you do ANYTHING - BACKUP your database.....

1 - Modify ONE product with the proper tax class.

2 - Then export just the table "products" in phpmyadmin as a "SQL" file.

You will get SOMETHING like:

...
CREATE TABLE "products" (
  "products_id" int(11) NOT NULL,
  "products_quantity" int(4) NOT NULL,
  "products_model" varchar(12) default NULL,
  "products_image" varchar(64) default NULL,
  "products_price" decimal(15,4) NOT NULL,
  "products_date_added" datetime NOT NULL,
  "products_last_modified" datetime default NULL,
  "products_date_available" datetime default NULL,
  "products_weight" decimal(5,2) NOT NULL,
  "products_status" tinyint(1) NOT NULL,
  "products_tax_class_id" int(11) NOT NULL,
  "manufacturers_id" int(11) default NULL,
  "products_ordered" int(11) NOT NULL default '0',
  "products_ship_price" decimal(15,4) NOT NULL default '0.0000',
  "products_length" decimal(6,2) NOT NULL default '12.00',
  "products_width" decimal(6,2) NOT NULL default '12.00',
  "products_height" decimal(6,2) NOT NULL default '12.00',
  "products_ready_to_ship" int(1) NOT NULL default '0',
  PRIMARY KEY  ("products_id"),
  KEY "idx_products_date_added" ("products_date_added")
) AUTO_INCREMENT=31 ;

...

INSERT INTO `products` VALUES 
(3, 32, 'MG200MMS', 'matrox/mg200mms.gif', 299.9900, '2003-11-23 19:49:08', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 23.00, 1, 1, 1, 0, 0.0000, 12.00, 12.00, 12.00, 0),
(4, 22, 'MG400-32MB', 'matrox/mg400-32mb.gif', 499.9900, '2003-11-23 19:49:09', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 23.00, 1, 1, 1, 0, 0.0000, 12.00, 12.00, 12.00, 0),
(5, 2, 'MSIMPRO', 'ap_ups1000.gif', 49.9900, '2003-11-23 19:49:09', '2008-10-12 17:21:14', NULL, 7.00, 1, 1, 2, 0, 0.0000, 12.00, 12.00, 12.00, 0),
(6, 13, 'DVD-RPMK', 'dvd/replacement_killers.gif', 42.0000, '2003-11-23 19:49:09', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 23.00, 1, 1, 3, 0, 0.0000, 12.00, 12.00, 12.00, 0),
(7, 17, 'DVD-BLDRNDC', 'dvd/blade_runner.gif', 35.9900, '2003-11-23 19:49:09', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 7.00, 1, 1, 2, 0, 0.0000, 12.00, 12.00, 12.00, 0),
(8, 10, 'DVD-MATR', 'dvd/the_matrix.gif', 39.9900, '2003-11-23 19:49:09', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 7.00, 1, 1, 2, 0, 0.0000, 12.00, 12.00, 12.00, 0),
(9, 10, 'DVD-YGEM', 'dvd/youve_got_mail.gif', 34.9900, '2003-11-23 19:49:09', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 7.00, 1, 1, 2, 0, 0.0000, 12.00, 12.00, 12.00, 0),
(10, 10, 'DVD-ABUG', 'dvd/a_bugs_life.gif', 35.9900, '2003-11-23 19:49:09', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 7.00, 1, 1, 2, 0, 0.0000, 12.00, 12.00, 12.00, 0),
(11, 10, 'DVD-UNSG', 'dvd/under_siege.gif', 29.9900, '2003-11-23 19:49:10', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 7.00, 1, 1, 2, 0, 0.0000, 12.00, 12.00, 12.00, 0);

3 - Open the file in a REAL TEXT editor (not Word or Notepad) - I suggest using something good like Notetab or Ultraedit.

4 - Here comes the work.... copy all the "VALUES" into a NEW file - save it as say IMPORT.CSV (this extension is important).

5 - replace all "^P(" with "^P" and all "),^P" with "^P" - (without the "")
ALSO remove on the FIRST line the "(" at the beginning and on the LAST line at the end remove the ");"
ie change from:

(3, 32, 'MG200MMS', 'matrox/mg200mms.gif', 299.9900, '2003-11-23 19:49:08', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 23.00, 1, 1, 1, 0, 0.0000, 12.00, 12.00, 12.00, 0),
(4, 22, 'MG400-32MB', 'matrox/mg400-32mb.gif', 499.9900, '2003-11-23 19:49:09', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 23.00, 1, 1, 1, 0, 0.0000, 12.00, 12.00, 12.00, 0),
(5, 2, 'MSIMPRO', 'ap_ups1000.gif', 49.9900, '2003-11-23 19:49:09', '2008-10-12 17:21:14', NULL, 7.00, 1, 1, 2, 0, 0.0000, 12.00, 12.00, 12.00, 0),
(6, 13, 'DVD-RPMK', 'dvd/replacement_killers.gif', 42.0000, '2003-11-23 19:49:09', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 23.00, 1, 1, 3, 0, 0.0000, 12.00, 12.00, 12.00, 0),
(7, 17, 'DVD-BLDRNDC', 'dvd/blade_runner.gif', 35.9900, '2003-11-23 19:49:09', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 7.00, 1, 1, 2, 0, 0.0000, 12.00, 12.00, 12.00, 0),
(8, 10, 'DVD-MATR', 'dvd/the_matrix.gif', 39.9900, '2003-11-23 19:49:09', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 7.00, 1, 1, 2, 0, 0.0000, 12.00, 12.00, 12.00, 0),
(9, 10, 'DVD-YGEM', 'dvd/youve_got_mail.gif', 34.9900, '2003-11-23 19:49:09', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 7.00, 1, 1, 2, 0, 0.0000, 12.00, 12.00, 12.00, 0),
(10, 10, 'DVD-ABUG', 'dvd/a_bugs_life.gif', 35.9900, '2003-11-23 19:49:09', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 7.00, 1, 1, 2, 0, 0.0000, 12.00, 12.00, 12.00, 0),
(11, 10, 'DVD-UNSG', 'dvd/under_siege.gif', 29.9900, '2003-11-23 19:49:10', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 7.00, 1, 1, 2, 0, 0.0000, 12.00, 12.00, 12.00, 0);

to

3, 32, 'MG200MMS', 'matrox/mg200mms.gif', 299.9900, '2003-11-23 19:49:08', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 23.00, 1, 1, 1, 0, 0.0000, 12.00, 12.00, 12.00, 0
4, 22, 'MG400-32MB', 'matrox/mg400-32mb.gif', 499.9900, '2003-11-23 19:49:09', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 23.00, 1, 1, 1, 0, 0.0000, 12.00, 12.00, 12.00, 0
5, 2, 'MSIMPRO', 'ap_ups1000.gif', 49.9900, '2003-11-23 19:49:09', '2008-10-12 17:21:14', NULL, 7.00, 1, 1, 2, 0, 0.0000, 12.00, 12.00, 12.00, 0
6, 13, 'DVD-RPMK', 'dvd/replacement_killers.gif', 42.0000, '2003-11-23 19:49:09', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 23.00, 1, 1, 3, 0, 0.0000, 12.00, 12.00, 12.00, 0
7, 17, 'DVD-BLDRNDC', 'dvd/blade_runner.gif', 35.9900, '2003-11-23 19:49:09', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 7.00, 1, 1, 2, 0, 0.0000, 12.00, 12.00, 12.00, 0
8, 10, 'DVD-MATR', 'dvd/the_matrix.gif', 39.9900, '2003-11-23 19:49:09', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 7.00, 1, 1, 2, 0, 0.0000, 12.00, 12.00, 12.00, 0
9, 10, 'DVD-YGEM', 'dvd/youve_got_mail.gif', 34.9900, '2003-11-23 19:49:09', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 7.00, 1, 1, 2, 0, 0.0000, 12.00, 12.00, 12.00, 0
10, 10, 'DVD-ABUG', 'dvd/a_bugs_life.gif', 35.9900, '2003-11-23 19:49:09', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 7.00, 1, 1, 2, 0, 0.0000, 12.00, 12.00, 12.00, 0
11, 10, 'DVD-UNSG', 'dvd/under_siege.gif', 29.9900, '2003-11-23 19:49:10', '2008-10-01 16:55:23', '0000-00-00 00:00:00', 7.00, 1, 1, 2, 0, 0.0000, 12.00, 12.00, 12.00, 0

6 - Save.

7 - IMPORT the CSV file into Excel or a spreadsheet as a CSV - Comma Separated Values file.

8 - 11 column or "K" should be the "products_tax_class_id" for each item. Search for the item you changed in step 1. Copy that value in "K" to all items.

9 - Save the CSV file.

10 - OPEN the CSV in the text editor again.

11 - replace all "^P" with "^P(" and all "^P" with "),^P" (without the "") - remove and single lines with JUST ( or ) on it. ALSO the FIRST line add a "(" at the beginning and on the LAST line change (or add) the end from ")," to ");"
IE reversing everything from step 5.

12 - COPY all values over top of the "VALUES" in your original SQL export file.

13 - Save the SQL file as a new file ie: "updated.sql".

14 - Import the SQL file into phpmyadmin.

Done.


OR .........

SQL the change......

In phpmyadmin - Run SQL query

UPDATE 'name of-your-database'.'products' SET 'products_tax_class_id' = '2'

Where "2" is the Proper tax class id....

Good Luck!

[m2d^]

Thank you Thank You...

the second method did it for me. and thats what i was looking for, a sql command to update the products.

did i say thank you?

THANKS!

[jpf]

Originally Posted by m2d^

Thank you Thank You...

the second method did it for me. and thats what i was looking for, a sql command to update the products.

did i say thank you?

THANKS!

Better yet - click on my post on the "THANKS!" button!

and your WELCOME!...