Data Conversion Guidelines

Have more questions? Submit a request

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  

Download: OASISData3.xls

 

Additional Data

For certain items, we can convert them as long as you follow one of the templates below:

Customer Accounts: OASISCustomerAccounts.xls

Inventory (In-stock parts only): OASISInventory.xls

 

All Example Spreadsheet Download Links:

Articles in this section

Was this article helpful?
1 out of 1 found this helpful

Comments

0 comments

Please sign in to leave a comment.