Buy Import Spreadsheet Format

To import a spreadsheet using Import Buys, format the spreadsheet according to the rules and table below. You may also launch the sample Excel file that is available in Buy Import Spreadsheet Steps, then save it to your desktop and use it as a master spreadsheet for all future buys to be imported.

Rules:

Columns must be in the order shown in the table below.
Each buy line must have its own row on the spreadsheet.
No extra blank characters are allowed in a field (padding the field with extra spaces is not required). Example: If a field allows 2 characters and only 1 character is used, do not include a blank.
If the field is not required, it is not necessary to place a value in the field.
Multiple clients, products, media and estimates can appear on one spreadsheet.

Spreadsheet Column	Required	Number of characters	Format Cell As	Value	Example
A	N	75	Alphanumeric	Adserver or Third Party	3D
B	N	1	Text	Tran Type. Controls how this buy affects the worksheet. N=New (add this row to the Print worksheet) * M=Modify (overwrite an existing buy on the worksheet) * D=Delete (delete an existing buy from the worksheet) "Blank". Defaults to N (New). * Requires original Transaction ID. This option cannot be used if a Transaction ID was not assigned during the initial import of the buy record(s).	N
C	N	up to 50	Alphanumeric	Transaction ID. Key a unique code for the buy line. If this buy is being imported from a third party, such as a buying service, use their unique code for the buy. If you are manually keying the data into the spreadsheet, assign a unique identifier to the rows such as the estimate code plus a number. For example: Estcode1, Estcode2, etc. Transaction IDs can only be assigned during the original import of the buy records. The IDs cannot be assigned afterwards.	Estcode1
D	N			No Longer Used But Do Not Remove
D	N			No Longer Used But Do Not Remove
F	N	up to 2	Alphanumeric	Company. Leave blank to use the sign-on company when the Import Buys option is run.	01
G	N	up to 2	Alphanumeric	Office. Leave blank to use the sign-on office when the Import Buys option is run.	01
H	Y	up to 5	Alphanumeric	Client Code from Print	OAK
I	Y	up to 4	Alphanumeric	Product Code from Print	BRCK
J	Y	up to 2	Alphanumeric	Media Code from Print	M
K	Y	up to 6	Alphanumeric	Estimate Code from Print	2Q2004
L	Y	up to 10	Alphanumeric	Vendor Code from Print	LATIMES
M	N	up to 4	Alphanumeric	Market Code from Print
N	N	up to 3	Alphanumeric	Edition Code from Print	SUN
O	N	up to 2	Alphanumeric	Zone Code from Print	SW
P	N	up to 2	Number	Column. Optional.	0
Q	**	up to 20	Alphanumeric	Space Code. Required if Unit is blank.	PG 4/C BLD
R	Y	date	Date	Insertion Start Date. Can be in several date formats including custom date formats.	2009-10-31, yyyy/mm/dd, m/dd/yy, mm/dd/yyyy, etc
S	N	date	Date	Insertion End Date. To note an insertion end date for your reference, key a date within the flight date range.	2009-10-31, yyyy/mm/dd, m/dd/yy, mm/dd/yyyy, etc.
T	**	up to 3	Alphanumeric	Unit. Required if Space Code is blank.
U	Y	up to 17 13 numeric plus 4 after the decimal	Number	Quantity. Note that this is always required on the spreadsheet, unlike the buy worksheet which allows it to be blank.	1
V	Y	up to 15, 6 after decimal	Number	Rate. (Insertion Rate) Include the decimal but do not include commas; blank is allowed if the cost is 0.	1500 .00063
W	Y	1	Text	Insertion Rate Qualifier. Blank for Gross Rate N for Net Rate S for Special Net Rate, A for Total Gross B for Total Net C for Total Cost F for Free X for Fee Only G for Gross CPM H for Net CPM I for Total CPM	G
X	N	up to 40	Alphanumeric	Headline. Optional.
Y	N	up to 20	Alphanumeric	Ad Number.	A1345-B6789
Z	N	up to 10	Alphanumeric	Location ID. This must be a valid code already set up in OOH\|Admin\|Location Definition.	10612
AA	N	up to 20	Alphanumeric	Material Type. Use to specify the type of material to be used.	Vinyl
AB	N	up to 40	Alphanumeric	Space Descriptor. Use to provide more specific information. Complements the Space Code.	14 x 48 with extension
AC	N	up to 25	Alphanumeric	Mailer PO (Client PO#)	PO999
AD	N	up to 15, 6 after decimal	Number	Client Bill Rate. Use when there is a negotiated billing rate based on quantity. Use if Client Bill Rate overrides standard bill commission formulas.	15.7345
AE	N	Date	Date	Material Close Date.	2007-01-01, yyyy/mm/dd, mm/dd/yyyy, etc.
AF	N	5	Alphanumeric	Comment ID. Must be a valid code. Set up in Database\|Standard Comments Definition.	B006
AG	N	up to 5	A/N	Traffic Rep. Use when specifying a different order address. Must be a valid code in Admin\|Representatives Definition.
AH	N	up to 5	A/N	Materials Rep. Use when specifying a different shipping address. Must be a valid code in Admin\|Representatives Definition.
AI	N	up to 15, 6 after decimal	Number	Vendor Pay Rate. Not available, except for custom installation.
AJ	**	up to 20	Alphanumeric	Ad Caption 1. Required when using Ad Number (column Y).
AK	N	up to 20	Alphanumeric	Ad Caption 2. (Optional)
AM	N	up to 20	Alphanumeric	Ad Caption 3. (Optional)

**Rules for entering Space Code, Column, and Unit are the same as in the Buy Module, except that the column field must contain a zero to be imported as blank in the Buy Module. See Worksheet Buy Columns.