Jeffrey Hunt - 2001-08-02

Logged In: YES
user_id=287277

Hello All,
Sorry about the ugly formatting of this reply. It is a
copy and paste from an e-mail I sent about this issue in
January. I believe that all of these steps are still valid
for the 1.2.3 code base. I will look into that and if this
is not accurate I will write an update. Also I am not sure
if the shipping files from the vendors have changed :( I
know that there were some issues with this is the past.

Jeff
2.4.2.6 Shipping Methods and
Rates Data Load

AllCommerce uses real-time
shipping
methods in checkout. The shipping carriers supported in this
release
are: UPS, Fed Ex and USPS.

Overview

import_shipping_tables.pl allows
you to use the download information from United Parcel
Service (UPS),
U.S. Postal Service (USPS), and Federal Express (FedEx)
to calculate shipping
costs for a
customer transaction. By using information that you download
from the
shippers you want to use, import_shipping_tables.pl
translates this files
into SQL
insert statements that can be imported into your database.

Preliminary
Information

In order to charge
shipping for an
item, you must have weight information for each item being
sold. This
information should be placed in the database at the time of
the creation of the
object in the
AllCommerce Administrator. Failure to provide weight
information could
result in incorrect shipping calculations.

Restrictions

import_shipping_tables.pl is
designed for U.S. domestic service only. International
shipping involves
a greater set of problems (e.g., import taxes, currency,
import
restrictions).

Information Needed For
import_shipping_tables.pl

Shipping tables are
normally
constructed based on the distance ("zone"), along with the
type of
service (e.g., "overnight") and weight ("rate"). In
practical terms,
two sets of tables are
needed from
each shipping company.

import_shipping_tables.pl is
designed to take information from UPS, USPS, and FedEx
Websites, in
order to construct the required shipping tables that will be
put
into the database.

For UPS:

Go to
www.ups.com/using/software/currentrates/rates_in_us.html .

On the UPS
Website, for
zone information input the first three digits of the "Origin
Postal
Code" (i.e., where you will be shipping from) and press the
download
button.

You will be
prompted to
download a file that is named by the three digit zip,
suffixed with the
extension .csv (e.g., 944.csv). This is your zone
information,
specific to
your shipping
location.

After getting
the zone
information, download the "Download All Rate Charts" via ZIP
format.

For USPS:

Go to
postcalc.usps.gov/Zonecharts. Enter the first three digits
of the origin
postal code, and press the "Get Zone Chart" button. You will
need to
save
this Web page.

Use the "Save
as" button
from the browser to save the file "default.asp".

For the rate
chart,
download the Express Mail and Priority Mail files (em.txt
and pm.txt)
from www.framed.usps.com/consumer/csv.htm (note that the
extension is
.com, rather
than .gov).

For FedEx:

Go to
www.fedex.com/us/rates/zonelocator. Enter the 5-digit ZIP
code and click
on the "search by zip" button.

You will then
be prompted
to download a PDF-formatted file. Download the file.

Open the PDF
format with
your PDF viewer (e.g., Adobe Acroread (TM)).

Click on the
text button on
the view and cut and paste the first table containing the
continental US
information (zipcodes and zones only - do not cut and
paste header
info) to a
file called fedexfile1.txt.

Cut and paste
the other PDF
table (containing Alaska and Hawaii ZIP codes and zones) to
a file
called fedexfile2.txt (remember to cut and paste only the
ZIP codes and
zones - no
header info is needed).

Download the
rates by
service at www.fedex.com/us/rates/services/download.html.
Use the unix
format to download Rates_by_Service.tar.gz.

Using
import_shipping_tables.pl

If you are using all
three
shipping companies, then you should have the following
files: UPS ->
xxx.csv (where xxx is the three digit post zip) usrates.zip
USPS ->
default.asp em.txt
pm.txt FedEx ->
regionlocator.cgi Rates_by_Service.tar.gz

In the tools
directory, create a
directory called "data", if it is not already there. Place
the files you
will be using into the data directory. For .tar.gz,
uncompress and
untar the file and
unzip the .zip
files, as well.

In the tools
directory, run
import_shipping_tables.pl, which should convert the files
above into SQL
insert statements for import into your database. The SQL
insert
statements are grouped
into files
per the shipping method and either zone or rate (e.g.,
UPS.rate.dbid or
UPS.994.zone.dbid).

Once these files are
created, you
will need to load them into your database. They will then be
available
as shipping options during customer checkout. Continental US
information
(zip codes and zones only - do not cut and
paste header
info) to a
file called fedexfile1.txt.

Cut and paste
the other PDF
table (containing Alaska and Hawaii ZIP codes and zones) to
a file
called fedexfile2.txt (remember to cut and paste only the
ZIP codes and
zones - no
header info is needed).

Download the
rates by
service at www.fedex.com/us/rates/services/download.html.
Use the unix
format to download Rates_by_Service.tar.gz.

Using
import_shipping_tables.pl

If you are using all
three
shipping companies, then you should have the following
files: UPS ->
xxx.csv (where xxx is the three digit post zip) usrates.zip
USPS ->
default.asp em.txt
pm.txt FedEx ->
regionlocator.cgi Rates_by_Service.tar.gz

In the tools
directory, create a
directory called "data", if it is not already there. Place
the files you
will be using into the data directory. For .tar.gz,
uncompress and
untar the file and
unzip the .zip
files, as well.

In the tools
directory, run
import_shipping_tables.pl, which should convert the files
above into SQL
insert statements for import into your database. The SQL
insert
statements are grouped
into files
per the shipping method and either zone or rate (e.g.,
UPS.rate.dbid or
UPS.994.zone.dbid).

Once these files are
created, you
will need to load them into your database. They will then be
available
as shipping options during customer checkout.

ok so once you have downloaded these files
I am outlining the steps for UPS - using mysql
When I make references to /home/httpd/os_allcommerce
this is the
installation directory of Allcommerce on my system
When I reference use osallcommerce if osallcommerce is not
the directory
your using substitute your db's name
To connect to mysql you may need to add the -u username
-p password
switches for your installation
All of the below commands are run from a linux command line.

from the directory where you saved the files
cp usrates.zip /home/httpd/os_allcommerce/tools/
cp 944.csv /home/httpd/os_allcommerce/tools/
cd /home/httpd/os_allcommerce/tools
mkdir data
cp usrates.zip ./data
cp 944.csv ./data
cd data
unzip usrates.zip
cd ..
./import_shipping_tables.pl
cd data
vim USPS.rates.dbid
at the top of the file add
use osallcommerce;
vim UPS.zones.944.dbid
add to the top of the file
use osallcommerce;
mysql < UPS.zones.944.dbid
mysql < UPS.rates.dbid