Microsoft Dynamics AX integration with UPS Worldship. Part2 – Export and import file formats
Galyna Fedorova
3,566
Like
Share
ReportThe second difficult part during the integration implementation is to understand what file format should be send to UPS from ERP system and what file format should be received from UPS, that will be imported with the tracking numbers into ERP system .
In this post, I will provide you with the UPS file formats for the export file and import file.
Export file format (from AX into UPS):
Common rule is to generate export file after sales packing slip posting. And file format will be the next:
File format-ASCII
Boundary mode-separator
Column separator- “;”
Row separator- CRLF
File name- UPS.txt
Extension-.txt
Decimals separator-“.”
The values in the file might be the next:
| Pos. | Export field in English | Length |
| 1 | Company name | 30 |
| 2 | Contact name | 30 |
| 3 | Address 1 | 30 |
| 4 | Address 2 | 30 |
| 5 | Address 3 | 30 |
| 6 | Zip code | 10 |
| 7 | City | 30 |
| 8 | Contact phone number | 30 |
| 9 | Country code | 2 |
| 10 | Contact email address | 50 |
| 11 | UPS service
The list of UPS service codes can be found here: https://www.ups.com/worldshiphelp/WS20/ENG/AppHelp/Codes/UPS_Service_Codes.htm |
2 |
| 12 | Package type
The list of UPS package types can be found here: https://www.ups.com/worldshiphelp/WS20/ENG/AppHelp/Codes/Package_Type_Codes.htm |
2 |
| 13 | Billing option | 2 |
| 14 | Shipper account | 6 |
| 15 | Gross weight | 7.2 |
| 16 | Parcel number | 6 |
| 17 | Reference 1
CustPackingSlipJour.DataAreaId CustPackingSlipJour.PackingSlipId |
30 |
| 18 | Reference 2
CustPackingSlipJour.DeliveryDate SalesTable.CustAccount |
30 |
| 19 | QVN option | 1 |
| 20 | QVN email address | 1 |
| 21 | QVN type | 1 |
| 22 | Goods description | 30 |
| 23 | Record separator | 2 |
You can adjust the file based on your business and what would you like to send to UPS. The full list of fields is here:
https://www.ups.com/worldshiphelp/WS20/ENG/AppHelp/CONNECT/Shipment_Data_Field_Descriptions.htm
UPS.txt file is generated once and not at each packing slip. Other Packing slip generated with carrier UPS is added in the same file after the last record.
Import file format (from UPS into AX):
Import file can be generated automatically, once Shipment is done in UPS. File is generated and stored in the specified folder. csv file is generated once and not at each shipment. Other shipments generated with UPS carrier are added to the same file after the last record/or could be overridden all data in the file. We implemented in the way that all data is added to the file. Since this file will be increased, and we need somehow to limit the data inside that file. Shipment Process Timestamp was analysed and the records with specific Timestamp were selected during the import process.
And file format will be the next:
File format-ASCII
Boundary mode-separator
Column separator- “;”
Row separator- CRLF
File name- can be configured in UPS (for example, ExportPackage.csv)
Extension-.csv
Decimals separator-“.”
The values in the file might be the next:
| Pos | UPS Field | Note |
| 1 | Reference 1 | CustPackingSlipJour.PackingSlipID
By this field, needed packing slip will be identified. |
| 3 | Lead tracking number | If shipment contains several packages, one lead tracking number will be set for all packages |
| 4 | Tracking Number | Tracking number |
| 5 | Weight | Actual weight of the package |
| 6 | Void indicator | Will be used in case if shipment was voided |
| 7 | Shipment Process Timestamp | Format YYYYMMDDHHMMSS
|
You can adjust the file based on your business and what would you like to send from UPS. It will be done in UPS.
That’s it!
This was originally posted here.
*This post is locked for comments