PSSC - MI Shipping Info

Order Import File Specification
Enhanced Version - updated: 07/14/2009

This document is the specification of the file structure for orders to be imported into the PSSC database management system using the Enhanced version. The Enhanced version is the same as the “base” version with 2 additional fields (caddress5 & csan) added.

File Type: ASCII text file

Field Delimiter: comma

Text Delimiter: double quotation (e.g. “SHDOME”)

The import file is a single ASCII text file with both Header records (orders) and Detail records (line items) included. Each order (Header record) shall have its own line followed by separate a Detail line for each line item on the order. The Detail lines are related to the Header records by the invoiceno. Each Detail record is also on its own separate line.

Header and Detail records shall have a carriage return, linefeed (CR-LF) at the end of the line to signify the end of the record.

NOTE: If you have no data for a field, you MUST still include an empty placeholder field to maintain field position. A placeholder field needs to be delimited the same as any other field, with a comma to separate it and empty quotes (“”) to represent its data.

Fields do not have to contain the maximum field width. The data may be any length less than the Max Width for each field.

Special Field Requirements – (see tables below)
Field Position	Field Name	Comment
Field 1 (Header File)	Type	For Header records, use “H” as the value for the first field (Type).
Field 1 (Detail File)	Type	For Detail records, use “D” as the value for the first field (Type).
Field 3 (Header File)	Custno	This is your assigned publisher code. This will be constant value for all your orders. Contact Publishers Storage & Shipping for this value.
Field 5 (Header File)	Serviceno	Use “SHDOME” as a constant value for all Header records.
Field 6 (Header File)	Dpubtrandate	Must be 10 characters long and use the following format: MM/DD/YYYY.

The following tables Detail the specific field position, widths and data types for both the Header and Detail records.

NOTE: Field position must be maintained. The field name is irrelevant and only used for reference

Header (Order) Record Fields
Field Position	Required	Name	Description	Max Width	Data Type	Example
1	Yes	Type	Defines record type. “H” for Header (Order) “D” for Detail (Line items)	1	Character	“H” for a Header record “D” for a Detail record
2	Yes	Invoiceno	Invoice number	20	Character	“123456789”
3	Yes	Custno	Publisher code	4	Character	“PUB1”
4	Yes	Ccarriercode	Shipping carrier code	6	Character	“M02”
5	Yes	Serviceno	Service code –This is a constant and shall always be “SHDOME”.	6	Character	“SHDOME”
6		Dpubtrandate	Order date	10	Character	“05/20/2009” – Format: MM/DD/YYYY
7	Yes	Caddress1	Address line 1	30	Character	“Bob Smith”
8	Yes	Caddress2	Address line 2	30	Character	“10 Main St.”
9		Caddress3	Address line 3	30	Character	“P.O Box”
10		Caddress4	Address line 4	30	Character	“Suite 1120”
11		Caddress5	Address line 5	30	Character	“Bldg A”
12	Yes	Ccity	City	30	Character	“Fitchburg”
13	Yes	Cstate	State	2	Character	“MA”
14	Yes	Czip	Zip/Postal code	10	Character	“01420”
15		Ccountry	Country	15	Character	“USA”
16		Cponumber	Purchase Order #	15	Character	“ABC1234”
17		Csan	Destination SAN (Standard Address Number) number	20	Character	“1234567” or “123-4567”
18		CShipAcct	Shipping Acct. #	10	Character	“0123456789”

NOTE: Each Detail record MUST relate to a Header record on the invoiceno (field 4). The invoiceno is the link between Header and Detail records.

Detail (lineitems) Record Fields
Field Position	Req.	Name	Description	Max Width	Data Type	Example
1	Yes	Type	Defines record type. for Header (Order) for Detail (Line items)	1	Character	“H” for a Header record “D” for a Detail record
2	Yes	Cbookcode	Bookcode or SKU	14	Character	“7856-598”
3	Yes	Iqty	Quantity Shipped	6	Character	“2”
4	Yes	Invoiceno	Invoice number – Related to Header (order) invoice num.	20	Character	“123456789”

Example: - Here is a sample file. It has three orders. The first order has two lineitems, the second order has 1 lineitem and the third order has 2.

NOTE: All information in example is false. It was created for demonstration purposes only.

"H","INVOICE1","TEST","U25","SHDOME","05/20/2009","John Doe","10 Main St.","Suite 110","","","Boston","MA","02134","USA","12345","123-4567","0123456789"
"D","7777","1","INVOICE1"
"D","9","1","INVOICE1"
"H","INVOICE2","TEST","M02","SHDOME","05/20/2009","Bob Smith","234 Maple Lane","PO Box 2345","","",”Fitchburg”,"MA","01420","USA","","123-4567","0123456789"
"D","1039","1","INVOICE2"
"H","INVOICE3","TEST","M02","SHDOME","05/20/2009","Julie Swain","456 Sugar St.","","","","Fitchburg","MA","01420","USA","swain01","789-1011","0123456789"
"D","9","11","INVOICE3"
"D","1039","1","INVOICE3"

If you are unable to create the import text file in your system you may download an excel template file
(available HERE) which you can fill in with your order information and email to your publisher relations
representative at PSSC.

Special Field Requirements – (see tables below)