#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    98
    Rep Power
    2

    Shipping Rates & Zip code Zones


    I'm designing a shipping calculator for an ecommerce application using UPS rates and zip code zones documentation. There is an excel spreadsheet with zip code ranges with their corresponding zones, as well as the rates chart with the zones and corresponding weight and shipping costs.

    I guess my question is how would I transfer the data in the excel spreadsheets to my database in meaningful columns so I can query it for the appropriate rates based on customer entered zip code?

    Will I have to do this by hand, column for column, field for field or is there an easier way to import this file and will this solution be easier to implement than doing it by hand?
    -- Success achieved from tribulation --
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by BitZoid
    ... or is there an easier way to import this file
    yes, the LOAD DATA INFILE command

    Comments on this post

    • BitZoid agrees : ty
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    98
    Rep Power
    2
    Originally Posted by r937
    yes, the LOAD DATA INFILE command
    That was suspiciously easy... Thanks r937!
    -- Success achieved from tribulation --
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    98
    Rep Power
    2
    Here's another question.

    I have a table that houses zip codes and their corresponding UPS zones according to service selected (nextdayair, ground, etc...) So the table looks like this:

    Table (zones_ups):
    zoneKey (primary key)
    str_zip - (starting zip range)
    end_zip - (ending zip range)
    ground - (zone for ground for entered zip)
    2DA - (zone for 2DayAir for entered zip)
    NDA - (zone for NextDayAir for entered zip)
    END


    Now I have to create a table (or linking entity as well?) with the rates that match those zones. The rates data look like this, and contains columns for zones and rows for weights:

    Code:
    Ground shipment rates:
    Zones     Zone1     Zone2     Zone3     Zone4      Zone5....
    -------------------------------------------------------
    1 Lbs.     3          4          6         8          101
    2 Lbs.     2          3          4         7          108
    3 Lbs.     4          2          5         9          115
    Etc... to 150 lbs.
    
    
    2nd Day Air shipment rates:
    Zones     Zone1     Zone2     Zone3     Zone4      Zone5....
    ------------------------------------------------------
    1 Lbs.     3          4          6         8          101
    2 Lbs      2          3          4         7          108
    3 Lbs.     8          5           3        6          113
    Etc... to 150 lbs.
    
    
    Next Day Air shipment rates:
    Zones     Zone1     Zone2     Zone3     Zone4      Zone5....
    ------------------------------------------------------
    1 Lbs.     4          2          6         9          122
    2 Lbs      2          3          4         7          132
    3 Lbs.     6          8          9         6          118
    Etc... to 150 lbs.
    Question is: In terms of database structure, how do I turn this rates data into related and significant database tables or fields that can be queried against the matching entered zip code zones?
    Last edited by BitZoid; April 4th, 2013 at 01:50 PM.
    -- Success achieved from tribulation --
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    98
    Rep Power
    2
    I went with this method because I am not sure how to structure these fields in an efficient manner.

    Tables:

    ups_ground_rates
    rateKey
    weight
    z1
    z2
    z3
    z4
    z5
    z6
    z7
    z8
    z9
    z10
    z11


    ups_2dayair_rates
    rateKey
    weight
    z202
    z203
    z204
    z205
    z206
    z207
    z208
    z209
    z224
    z225
    z226


    ups_nextdayair_rates
    rateKey
    weight
    z102
    z103
    z104
    z105
    z106
    z107
    z108
    z109
    z124
    z125
    z126

    For each table there are about 80 rows
    -- Success achieved from tribulation --

IMN logo majestic logo threadwatch logo seochat tools logo