IT Intern Blog

IT Challenges Solved

Archive for the ‘UPS WorldShip’ Category

Transfer (export/import) Commodities in UPS WorldShip 11

Posted by IT Intern on July 22, 2009

UPS WorldShip: A very complicated piece of software, and that’s putting it nicely. At my office we have two buildings we send shipments from, and each building has a copy of WorldShip that need to stay in sync. My first encounter with the software was to upgrade both copies from version 10 to 11 and get the negotiated rates updated correctly on both machines. A bit of a wonky process, but not too bad.

About a week later, a ticket comes in asking to have the commodities transferred from one building to the other. After a lengthy and complicated walkthrough with UPS Tech Support (during which even the UPS rep had difficulty… after getting me several steps in, she realized she had it wrong and actually put me on hold while she figured it out), I finally got the commodities transferred and closed the ticket hoping I never have to deal with that software again.

As soon as the UPS rep began “hmm”-ing and “let’s see”-ing on the phone, I grabbed a pen and wrote down every excruciating step. She walked me through the export process and explained the import process, for which I would have to hang up and go to the machine in the other building, and I wrote down everything. I wanted every detail documented in case I (or anyone else) ever had to do this again. I even took my time taking screenshots through the import for my own reference — I wasn’t taking any chances. I wrote up the steps, which turned into three full Word document pages, and now I post them for your reference (in case you are attempting this process yourself), or for your entertainment (in case you don’t have to deal with this software and wish to chuckle at what a pain it is for me and others who do).

UPS Commodities Import/Export Instructions

UPS WorldShip v.11

STEP 1: EXPORT:

Section 1.1: Create an Export Map

In this section you will create a new map for exporting commodities. If you have previously created an export map, you can skip this section.

  • Go to Import/Export Data > Create/Edit Map…
  • Under “New Map” select the radio button “Export data from Worldship” and check “Export CSV” when it becomes enabled.
  • From the dropdown list, select “Commodity
  • In the “Name” field, enter a memorable name for your map and click “Create
  • The “Commodity” tab should be selected; if not, select it and click “Add all columns
  • Select the “SED specific data” tab and click “Add all columns
  • Select the “NAFTA specific data” tab and click “Add all columns
  • As you add columns, the column names will show up on the right side of the window
  • Select the radio button “Overwrite existing file” and check “Include header row
  • Click “OK
  • Click “Save
  • Click “OK

Section 1.2: Export Commodities

In this section you will perform the actual export of the commodities.

  • Go to Import/Export Data > Batch Export…
  • Highlight the map you created in Section 1.1 and click “Next
  • Click “Next
  • Choose a memorable location to save the file and click “Save
  • You have now created a .csv file. You will need a copy of this file to use for importing commodities to the target computer

STEP 2: IMPORT

Section 2.1: Create an Import Map

In this section you will create a new map for importing commodities using the .csv file you created in Section 1.2. (1) If you have previously created an import map, you can skip this section.

  • Go to Import/Export Data > Connection Assistant…
  • Select the radio button “Create a new map for Import” and click “Next
  • Under “Import Data Types” select “Commodity” from the drop down list and click “Next
  • Select the radio button “By File” and click “Browse…
  • Next to “Files of type:” select “Text Files (*.txt, *.csv)” from the drop down list
  • Select the .csv file you created in Section 1.2 (1)
  • Under “Data Source Name (DSN)” enter a memorable and unique name (it must be different than the name you used in Section 1.1)
  • Under “ODBC Drivers” highlight “Microsoft Text Driver (*.txt, *.csv)
  • Click “Next
  • The radio button “New Map” should be selected; if not, select it
  • Under “New Map Name:” enter the unique name you created for the Data Source Name previously
  • Click “Next
  • Click “Finish
  • The “ODBC Text Setup” window will open
  • Click “Options>>
  • Click “Define Format…
  • Select your .csv file from the list
  • Check “Column Name Header
  • Click “Guess
  • The “Columns” field will be populated with the column names from your .csv file
  • Highlight each column name and verify that the “Data Type” is “Char” and the “Width” is 255, making changes as necessary
  • Click “OK
  • If you see an error that says “Failed to save table attributes of (null) into (null).” Click “OK” (this has no effect on the import)
  • Back in the “ODBC Text Setup” window, click “OK
  • Now you are back to the “Edit <mapname> import map” window, where you will manually define the database relationships. You will see “ODBC Tables” on the left with the column names from your .csv file, and “WorldShip Fields” on the right with field names that match your column names. Under “WorldShip Fields” there is a drop down list that includes “Commodities”, “SED specific data”, and “NAFTA specific data”.
    • On the left under “ODBC Tables” drag the “Column Name” column to the right until you can see the entire description of each item. Each item’s name on the left will correspond to the field names under each drop down category on the right. For example, the item on the left called “COMMODITYDESCRIPTIONOFGOODS” corresponds to the item on the right called “Description of goods” under the “Commodity” drop down category.
    • Highlight the first pair of corresponding items and click “Connect.” You will see a matching red symbol next to the paired items when they are connected.
    • Do this for each pair, except for the “COMMODITYPARTNUMBER” item. For this item, select the corresponding items on each side, click “Define Primary Key for Import” (you will see a key symbol show up next to the left item only), then click “Connect.
  • After you have connected all the items, click “OK
  • When it asks “Do you want to Batch Import now with <mapname>?”, click “Yes
  • Click “Next
  • Click “Save
  • You will see the UPS Truck progress bar. When it is finished, click “Save

Section 2.2: Import Commodities

In this section you will perform only an import of the commodities using the map you created in Section 2.1. (1) If you have just done Section 2.1, you have already done a batch import and do not need to do this section.

  • Go to Import/Export Data > Batch Import…
  • Highlight the map you created in Section 2.1 and click “Next
  • In the “Import/Export Preview“ window, click “Next
  • You will see the UPS Truck progress bar, followed by the “Import/Export Summary“ window
  • Click “Save

NOTES

(1) The Import Map includes a reference to the directory and filename of the .csv export file. When using the Import Map to import from a new Export file, be sure the exported .csv is saved with the exact directory and filename referenced in the Import Map. If you are unsure, follow the steps to create a new Import Map.

Posted in UPS WorldShip | Leave a Comment »