Closing the Shipment
Google Database
- run the carts on shipment query and verify that there are 20 carts listed
- in the dbo_Shipments table, change the StatusID to 2. (Change it to 4 once the shipment returns and the work orders have been cleared.)
- change the following shipment’s StatusID to 1, if not already
- run barcodes on shipment
- copy just the barcodes into a text file saved to desktop
- in Caia, run the Item Status Report from the file of barcodes
- submit ticket to Jon for metadata files
Creating the Shipping Manifest
If from a spreadsheet, start by converting the excel file:
- confirm that columns are: pallet barcode
- remove headings row
- save to: C:\queries\createShipments
- save as type: Text (Tab delimited)
- filename: UILAW-SF_<yyyymmdd>.txt
- dedup by barcode
- save
Continue with Shipment Report below
Shipment Report and Cart Files
Creating the cart files
- Go to Windows Start and type in cmd to launch the command window
- Type in the following, replacing # with the shipment id.C:\Users\davishow> cd \queries\createShipments
C:\Users\davishow\createShipments> set PATH=C:\oracle_instant_client\instantclient_12_2\;%PATH%
C:\Users\davishow\createShipments> perl createShippingCartFiles.pm # - Open Windows explorer (Windows key + e) and go to C:\queries\createShipments
- Drag the “Shipment #” folder to the Google Refile Carts – Shortcut folder.
- Delete the “Shipment #” folder from the createShipments folder.
Caia
- when the email says the Item Status report is ready, download to desktop
- open a new excel file and Get Data From Text
- delete columns after Last Job
- save as item status report on desktop
Google db
- import as item status report
- export to excel: export shipment report
- filename: G:\OakStreet\Google Project\Google Refile Carts\Shipment #\shipment #
Excel
- open G:\OakStreet\Google Project\Google Refile Carts\Shipment #
- open the shipment # spreadsheet
- auto-adjust column width through Last Job Date
- Format Cells as Text in barcode column
- Custom Sort:
- Cartid
- Aisle
- Ladder
- Step
- Side
- Footprint
- Container
- Save
- PivotTable
- Rows: Cartid and Aisle
- Values: barcode
- label fields: Cart; Count
- verify that the aisles sort correctly within carts
- name the tab: “cart x aisle”
- PivotTable
- Rows: Cartid
- Columns: Collection
- Values: barcode
- hide line 3
- label fields: Cart; replace (blank) with Stacks?
- name the tab: “cart x location”
- looking at the cart sheet and both pivot tables, identify the carts that are all or partly Stacks (Aisle and Collection are blank)
- in the shipment folder, delete files for any carts that are all Stacks
Outlook
email Nick and Andy letting them know the files are ready; cc ops
Metadata Files
Uploading for Google
Google only cares about two files: the shipping manifest that contains barcodes & the cart they’re on (UIUC_timestamp) and the zip file of MARCXML records.
- Open WinSCP
- connect to apacheprod2.library.illinois.edu.
- On the left, go into G:\GoogleProject\All_Shipments_And_Corrective_Loads\ND Shipment #
- On the right, change path to /www-s/GoogleBooksMetadata
(If for Law, /www-s/UILaw/GoogleBooksMetadata) - Copy over <timestamp>.zip and UIUC_yyyymmdd.txt
(If for Law, <timestamp>.zip and UILAW-SF_yyyymmdd.txt)
Emailing ACS
- Email MJ Han (mhan3@illinois.edu) letting her know that the shipment metadata is ready. They do some further processing on behalf of Hathi on the files.