Database Import Specifications
Import Data File Format:
Create a ‘’ delimited file containing the customer data. The format of the file needs to be as follows:
The first line of the file should look like the below example. The fields contained in your header will vary based on the data to be updated/inserted.
Header Line Example:
!CUSTOMER<TAB>USERNAME<TAB>NAME<TAB>COMPANY<TAB>ADDR1<TAB>ADDR2<TAB>CITY<TAB>STATE<TAB>ZIP<TAB>COUNTRY<TAB>TEL<TAB>FAX<TAB>EMAIL<TAB>MONTHLY<TAB>etc.
* Note: signifies the location of a TAB character, NOT the actual letters “T”, “A”, “B”. The first character in the line MUST be a “!” (exclamation mark), followed by the word “CUSTOMER”.
Every line there after will contain your customer data. One customer is to be entered per line. It should look something like this:
Customer Data Line Example:
CUSTOMER<TAB>johnjsmi<TAB>John Smith<TAB>Acme Soda Company<TAB>123 Main Street<TAB>Suite 101<TAB>Salt Lake City<TAB>NV<TAB>23232<TAB>US<TAB>631-761-0159<TAB>631-360-1213<TAB>Contact@mydomain.com<TAB>35.00
The above example is word-wrapped for display purposes and should not be so in your import file.
Every separate transaction entry needs to be represented on 1 line.
The first field will always need to be the characters “CUSTOMER”. The order of the other fields will depend on the order the field names appear in the first row.
The easiest way to create this type of file is to first create a spreadsheet with this information and then save the spreadsheet as a “TAB” delimited file.
The file to be imported must contain tab-delimited fields, in the following order:
***Please note that the field names are case-sensivite; they should be all capitalized.
| Field | Required | Max Size | Notes |
| CUSTOMER | REQUIRED | NA | System Flag. It needs to be there. |
| USERNAME | REQUIRED | 15 | Customer Username |
| NAME | OPTIONAL | 39 | Customer�s Name |
| COMPANY | OPTIONAL | 39 | Company Name |
| ADDR1 | OPTIONAL | 39 | Line 1 of Address |
| ADDR2 | OPTIONAL | 39 | Line 2 of Address |
| CITY | OPTIONAL | 39 | City |
| STATE | OPTIONAL | 39 | State |
| ZIP | OPTIONAL | 12 | Zip |
| COUNTRY | OPTIONAL | 39 | Country |
| PHONE | OPTIONAL | 12 | Telephone # |
| FAX | OPTIONAL | 12 | Fax # |
| OPTIONAL | 39 | Customers Email Address | |
| MONTHLY | OPTIONAL | 8 | Recurring Fee - Fee to be charged. (No “$” Allowed) |
| BALANCE | OPTIONAL | 8 | Balance Due. For INSTALLMENT Billing |
| ENDDATE | OPTIONAL | 8 | End Date of membership or Next Bill Date. Format: YYYYMMDD i.e. December 31st, 2000 would be “20001231” |
| STARTDATE | OPTIONAL | 8 | Start Date of membership. Format: YYYYMMDD i.e. December 31st, 2000 would be “20001231” |
| STATUS | OPTIONAL | 9 | Allowed Values: “active”, “pending”, “cancelled” |
| PASSWORD | OPTIONAL | 12 | Customers Password. |
| ACCT_CODE | OPTIONAL | 10 | Account Code. For tracking purposes in reports. |
| PURCHASEID | OPTIONAL | 20 | Used for custom features, group names, etc. |
| PLAN | OPTIONAL | 5 | Membership Plan ID |
| CARDNUMBER | OPTIONAL | 16 | Credit Card #. |
| EXP | OPTIONAL | 5 | Credit Card Expiration Date. Format: MM/YY |
| BILLCYCLE | OPTIONAL | 10 | The recurring billing cycle of account in months. Set to “0” if no rebilling is desired. “0.5” for biweekly. |
| SHIPNAME | OPTIONAL | 39 | Shipping Name |
| SHIPADDR1 | OPTIONAL | 39 | Shipping Address - Line1 |
| SHIPADDR2 | OPTIONAL | 39 | Shipping Address - Line 2 |
| SHIPCITY | OPTIONAL | 39 | Shipping City |
| SHIPSTATE | OPTIONAL | 2 | Shipping State |
| SHIPZIP | OPTIONAL | 10 | Shipping Zip |
| SHIPCOUNTRY | OPTIONAL | 3 | Shipping Country |
Notes:
Let us know of any extra fields you want to import. They will go at the end of the line in a specific order.
**** An Important Note For Excel Users ****
You should set all of your spread sheet’s columns to “TEXT” format to prevent Excel from converting numeric values to scientific notation or possibly changing to “0”. For example, Excel can turn the last digit of the credit card to “0” & convert the number to scientific notation (e.g. turning ‘4111111111111111’ to ‘4.11111E+15’). This can also be a problem with the expiration date.
In Excel when you change a column’s format to “TEXT”, Excel will preserve the data exactly as it was entered. Do this before entering data into the column or specify this format at the time the file is imported into Excel. This process will prevent Excel from auto-changing the data’s format to a format that it thinks is best for the information presented. This can be used on any column to preserve the data.