Sales Tax

Yeah, I said it. Sales tax. It has to be one of the uglier sides of configuring online shopping carts. Now there are rumblings that some states are going to require that tax be collected by e-tailers for each state, county, city, or municipality. That is a lot of tax. No one seems to really know how it’s supposed to work. Everyone seems to have a different opinion, including accountants. I’m not a tax expert, and work hard at keeping it that way, but I still have to work with it here and there.

Well, that time came again with a need to implement a country-wide sales tax based on all counties. I started with data for all counties in one state. Since I work exclusively with X-Cart, I was looking to convert a third-party zipcode sales tax table into the zone and tax tables. I ended up writing a perl script that parses the flat file. Here are the steps:

1. Use Excel’s Text-do-Data to convert the position-delimited table to useable columns.
2. Come up with a key to name each tax zone. I used a format of ST-COUNTY (STate).
3. Load the list of zones into the db to a) get them loaded and b) utilize the db auto-increment to generate zoneid’s.
4. Save the zoneid-zone as a csv.
5. Leverage Perl’s data handling to load in the zone csv and tax table csv into hashes, then spit out csv files for zone_elements and tax_rates. It’s possible Excel could have been used to do this but that may have taken me just as long and not quite as fun.
6. Import the two new csv files into their corresponding tables.

That’s it! Now the tax data corresponding to customer zip code is working! Now when we get the data for the rest of the states I have a method ready to go.

If you need help implementing this solution, just let me know!