Data Conversion Guidelines

This article describes the requirements and formatting details when converting data over to OASIS. Note: these are instructions on how to format the excel spreadsheet. The spreadsheet should be sent to OASIS support to be imported into OASIS. This service may be for charge depending on the circumstances.

Customer Data

Field	Description	Example
Customer ID	REQUIRED, NO DUPLICATES! (Max 32 Characters); background ID OASIS uses	CUST123
Customer Name	REQUIRED! (Max 255 Characters)	Customer 123
Display ID	REQUIRED! (Max 32 Characters); ID visible to the user (usually matches CustomerID)	CUST123
Customer Role	Optional, must match a role you have set at Customers->File->Configuration->Roles Tab	Distributor
Sold To/Ship To/Bill To Addresses
Name	Optional, Ship/Bill/Sold To can be identical	Customer 123
Line 1	Optional, Ship/Bill/Sold To can be identical	123 Main St.
Line 2	Optional, Ship/Bill/Sold To can be identical	Suite 1
City	Optional, Ship/Bill/Sold To can be identical	Cabot
State	Two Letters, Optional, Ship/Bill/Sold To can be identical	AR
Zip	Optional, Ship/Bill/Sold To can be identical	72023
Country	Three Letters, Optional, Ship/Bill/Sold To can be identical	USA
County	Optional, Ship/Bill/Sold To can be identical	Lonoke
Phone	Optional, may have duplicate/additional entries	555-555-5555
Fax	Optional, may have duplicate/additional entries	555-555-5555
Email	Optional, may have duplicate/additional entries	person@cust123.com
Website	Optional, may have duplicate/additional entries	www.cust123.com
Assigned Sales Account	Optional (use OASIS user ID)	KH
Parent Company	Optional, unique Customer ID of the company that you want to assign to	CUST456
Invoice Term Text	Optional	All values are in USD
Tax Status	Optional, Either U (Unknown), T (Taxable), or E (Exempt)	T
Tax ID	Optional, use the ID of a Tax Account listed in Configuration->Tax Accounts	AR Local

Manufacturer Data

Field	Description	Example
Manufacturer ID	REQUIRED, NO DUPLICATES! (Max 32 Characters)	MYMFG
Manufacturer Name	REQUIRED! (Max 255 Characters)	My Manufacturer
Display ID	REQUIRED! (Max 32 Characters); ID visible to the user (usually matches CustomerID)	MYMFG
MailTo/ShipTo/RemitTo
Mail Line 1	Optional, Mail/Ship/Remit To can be identical	123 Main St.
Mail Line 2	Optional, Mail/Ship/Remit To can be identical	Suite 1
Mail City	Optional, Mail/Ship/Remit To can be identical	Cabot
Mail State	Two Letters, Optional, Mail/Ship/Remit To can be identical	AR
Mail Zip	Optional, Mail/Ship/Remit To can be identical	72023
Mail County	Optional, Mail/Ship/Remit To can be identical	Lonoke
Mail Country	Three Letters, Optional, Mail/Ship/Remit To can be identical	USA
Phone	Optional, may have duplicate/additional entries	555-555-5555
Fax	Optional, may have duplicate/additional entries	555-555-5555
Email	Optional, may have duplicate/additional entries	person@mymfg.com
Website	Optional, may have duplicate/additional entries	www.mymfg.com
Allowance Amount	Optional (avoid currency formatting)	1000
Allowance Weight	Optional (avoid specific weight labels)	100
Standard Commission	Optional (numbers only, no percent formatting)	10
Std Overage Split	Optional (numbers only, no percent formatting)	75

Representative Data

Field	Description	Example
Representative ID	REQUIRED, NO DUPLICATES! (Max 32 Characters)	AGENT1
Representative Name	REQUIRED! (Max 255 Characters)	Agent 1
Mail Name	Optional, may be split into Mail/Bill if needed	Agent 1
Mail Line 1	Optional, may be split into Mail/Bill if needed	123 Main St.
Mail Line 2	Optional, may be split into Mail/Bill if needed	Suite 1
Mail City	Optional, may be split into Mail/Bill if needed	Cabot
Mail State	Two Letters, Optional, may be split into Mail/Bill if needed	AR
Mail Zip	Optional, may be split into Mail/Bill if needed	72023
Mail Country	Three Letters, Optional, may be split into Mail/Bill if needed	USA
Mail County	Optional, may be split into Mail/Bill if needed	Lonoke
Phone	Optional, may have duplicate/additional entries	555-555-5555
Fax	Optional, may have duplicate/additional entries	555-555-5555
Website	Optional, may have duplicate/additional entries	www.cust123.com

Contact Data

Field	Description	Example
Name	REQUIRED! May be split into First/Middle/Last columns	John Doe
Position	Optional	Sales
CustomerID ManufacturerID RepresentativeID	REQUIRED! One of the fields Customer, Manufacturer or Representative ID is required to map this contact to an existing customer, manufacturer or representative.	CUST123 MYMFG AGENT1
Manufacturer ID	Optional (If MFG Contact)	MYMFG
Representative ID	Optional (If Representative Contact)	AGENT1
Phone	Optional, may have duplicate/additional entries	555-555-5555
Email	Optional, may have duplicate/additional entries	john@abc.com
Mobile	Optional, may have duplicate/additional entries	555-555-5555
Fax	Optional, may have duplicate/additional entries	555-555-5555

Note: When converting contacts, they may be either intermingled with customer data or as their own spreadsheet.

Formatting

Data must be in spreadsheet format, with columns forming common data elements, and rows signifying separate transactions (see below). The spreadsheet may be an Excel document or CSV file. Data in any other format will be rejected due to technical limitations (unless specified otherwise).

When using the optional entries, have each in their own columns:

Customer ID	Customer Name	Role	City	ST
CUST1	Customer ABC	Distributor	Cabot	AR
ENG1	Engineer 123	Engineer	Little Rock	AR
ARCH1	Architect XYZ	Architect	Conway	AR

Note: If your spreadsheet does not have IDs, we will ask you to add them in. IDs are crucial for OASIS to link transactions together, and while using names will work in some cases, IDs are much more compact (useful for reporting). Be sure when entering IDs to keep them as short as possible, but also unique. Keep in mind that you will be using these IDs to enter customer information in Orders/Quotes, so understanding them is key. Some ideas for IDs:

Try a combination of characters from the name: My Test Customer -> MTC
Add City/ST abbreviations for customers with multiple branches: Cabot, AR -> MTC-CAB; Little Rock -> MTC-LR

You may use a single spreadsheet, or multiple spreadsheets if needed. You may use one of the following templates:

Data on a single spreadsheet (Customers/Manufacturers/Reps, with contacts).

Your data will need a Type column so you can specify if the line is for a customer/mfg/rep, etc. This will double as your "Role" for your customers as well. If you have multiple contacts for one customer/mfg, be sure to list the data twice (at least the ID, Name, and Type). See example below:

ID	Name	Type	Contact
CUST1	Customer 123	Distributor	Joe Brown
CUSTABC	Customer ABC	Distributor	John Doe
CUSTABC	Customer ABC	Distributor	Jane Doe
ARCH1	Architect XYZ	Architect	Bob Green
MYMFG	My Manufacturer	Manufacturer	Gary Smith
MFG999	Manufactuer 999	Manufacturer	Amy Doe

Download: OASISData1.xls

Data on multiple spreadsheets (Customers, Manufacturers, Reps all separate) with contacts mixed in.

Your data will be clearer to read this way. Just like above, you will need to list your information twice if you have more than one contact at a company (at least the ID, Name, and Type). See example below:

ID	Name	Type	Contact	City
CUST1	Customer 123	Distributor	Joe Brown	Cabot
CUSTABC	Customer ABC	Distributor	John Doe	Conway
CUSTABC	Customer ABC	Distributor	Jane Doe	Conway
ARCH1	Architect XYZ	Architect	Bob Green	Sherwood
CONT-CAB	Contractor 777 - Cabot	Contractor	Gary Smith	Cabot
CONT-LR	Contractor 777 - Little Rock	Contractor	Amy Doe	Little Rock

ID	Name	Contact	Std Commission
MYMFG	My Manufacturer	Joe Brown	5
MFG999	Manufacturer 999	John Doe	7
AWEMFG	Awesome Manufacturer	Amy Doe	12

You may want to download the attached spreadsheet as a template, which contains all the columns that have been listed in the examples above.

Download: OASISData2.xls

Data on multiple spreadsheets, contacts included.

When converting contacts separately, be careful to make sure you link the Contacts back to the company they are associated with. If they have no company, then leave all three ID fields blank.

Customer ID	Manufacturer ID	Representative ID	Contact Name	Phone
CUST123			Jane Doe	555-555-5555
CUST123			John Doe	555-555-5555
CONT-LR			Joe Brown	555-555-5555
CONT-CAB			Gray Smith	555-555-5555
	MYMFG		Bill Brown	555-555-5555
	MYMFG		Amy Smith	555-555-5555
	MFG999		Mary White	555-555-5555
		AGENT1	Frank Red	555-555-5555