CSV Structures
- Steve Evans
Below are the required structures for Toucan Reporting data.
Each are must be in a separate file and can be called anything.
The delimiter can be any character.
Apart from the required fields, you only need to supply the fields you’ve got data for, e.g. if Sales_Notes against the customer isn’t available, don’t supply the header field name
All values have to be in base currency
If you cannot supply a file, e.g. order headers, there is no need to supply an empty file
- 1 Accounting Period Details (Required but we can generate)
- 2 Customers
- 3 Customer Locations
- 4 Customer Contacts (If not supplying contacts against location)
- 5 Customer Trading Status (Optional)
- 6 Customer Turnover Band (Optional)
- 7 Customer Transactions (Optional)
- 8 Invoice Headers
- 9 Invoice Lines
- 10 Order Header (Optional)
- 11 Order Lines (Optional)
- 12 Sales Rep Forecasts (Optional)
- 13 Products (Required)
Accounting Period Details (Required but we can generate)
Field Name | Type | Additional Info |
|---|---|---|
YearName | TEXT | Required |
PeriodNo | TEXT | Required |
PeriodName | TEXT | Required |
StartDate | DATE | Required |
EndDate | DATE | Required |
Customers
Field Name | Type | Additional Info |
|---|---|---|
Customer_ID | TEXT | Required |
Name | TEXT | Required |
Brand | TEXT | E.g. “Retail”, “Trade” |
Currency_ID | TEXT |
|
Status | TEXT | E.g. “Active”, “Dormant” |
Type | TEXT | Text of “customer” or “lead” |
Sales_Notes | TEXT | Any notes relevent to the customer |
Credit_Limit | REAL |
|
Current_Balance | REAL |
|
Overdue_1 | REAL | Ageing, e.g. 0-30 |
Overdue_2 | REAL | Ageing, e.g. 31-60 |
Overdue_3 | REAL | Ageing, e.g. 61-90 |
Overdue_4 | REAL | Ageing, e.g. 91-120 |
Overdue_5 | REAL | Ageing, e.g. 121+ |
Average_Payment_Days | REAL |
|
Sales_Target_This_Year | REAL |
|
Service_Level | TEXT |
|
Trading_Status | TEXT |
|
Turnover_Band | TEXT |
|
Analysis_1 | TEXT | E.g. customer category |
Analysis_2 | TEXT | E.g. region |
Analysis_3 | TEXT |
|
Analysis_4 | TEXT |
|
Analysis_5 | TEXT |
|
Sales_Rep_ID | TEXT |
|
Sales_Rep_Name | TEXT |
|
Date_Created | DATE | YYYY-MM-DD |
Customer Locations
Field Name | Type | Additional Info |
|---|---|---|
Customer_ID | TEXT | Required |
Address_ID | TEXT | Required |
Main_Location | TEXT | “true” or “false” |
Address_Reference | TEXT |
|
Address_Name | TEXT |
|
Address_Line_1 | TEXT |
|
Address_Line_2 | TEXT |
|
Address_Line_3 | TEXT |
|
Town | TEXT |
|
County | TEXT |
|
Postcode | TEXT |
|
Country | TEXT |
|
Latitude | TEXT |
|
Longitude | TEXT |
|
Sales_Rep_ID | TEXT |
|
Sales_Rep_Name | TEXT |
|
Contact_Name | TEXT | If not supplying separately |
Contact_Email | TEXT | If not supplying separately |
Contact_Telephone | TEXT | If not supplying separately |
Contact_Mobile | TEXT | If not supplying separately |
Contact_Website | TEXT | If not supplying separately |
Contact_Other | TEXT | If not supplying separately |
Customer Contacts (If not supplying contacts against location)
Field Name | Type | Additional Info |
|---|---|---|
Customer_ID | TEXT | Required |
Contact_ID | TEXT | Required |
Address_ID | TEXT | Required |
Type | TYPE | Required “tel” or “email” or “mobile” |
Contact_Type | TEXT | E.g. “Sales”, “Accounts” |
Name | TEXT |
|
Value | TEXT | E.g. telephone number |
Customer Trading Status (Optional)
Field Name | Type | Additional Info |
|---|---|---|
Customer_ID | TEXT | Required |
Trading_Status | TEXT |
|
Previous_Trading_Status | TEXT |
|
Date_Last_Change | DATE | YYYY-MM-DD |
Customer Turnover Band (Optional)
Field Name | Type | Additional Info |
|---|---|---|
Customer_ID | TEXT | Required |
Turnover_Band | TEXT |
|
Previous_Turnover_Band | TEXT |
|
Date_Last_Change | DATE | YYYY-MM-DD |
Customer Transactions (Optional)
Field Name | Type | Additional Info |
|---|---|---|
Customer_ID | TEXT | Required |
Transaction_ID | TEXT | Required |
Reference | TEXT | Required Invoice/Credit number |
Transaction_Date | DATE | YYYY-MM-DD |
Due_Date | DATE | YYYY-MM-DD |
Type | TEXT | Required “invoice” or “credit” |
Value | REAL | Outstanding Value |
Currency_ID | TEXT |
|
Invoice Headers
Field Name | Type | Additional Info |
|---|---|---|
Invoice_ID | TEXT | Required |
Type | TEXT | Required “invoice” or “credit” |
Document_No | TEXT | Required |
Order_ID | TEXT | Link to order header |
Description | TEXT |
|
Customer_ID | TEXT | Required |
Customer_Name | TEXT | Required |
Delivery_Address_Name | TEXT |
|
Invoice_Date | DATE | Required YYYY-MM-DD |
Customer_Order_Ref | TEXT |
|
Channel_ID | TEXT |
|
Channel_Description | TEXT |
|
Invoice Lines
Field Name | Type | Additional Info |
|---|---|---|
Invoice_ID | TEXT | Required |
Line_ID | TEXT | Required |
Product_ID | TEXT | Required |
Product_Description | TEXT | Required |
Quantity | REAL | Required |
Base_Unit_Price | REAL | Required |
Base_Line_Price_Net | REAL | Required |
Base_Line_Price_VAT | REAL | Required |
Base_Line_Price_Gross | REAL | Required |
Unit_Cost | REAL |
|
Line_Cost | REAL |
|
Order Header (Optional)
Field Name | Type | Additional Info |
|---|---|---|
Order_ID | TEXT | Required |
Customer_ID | TEXT | Required |
Customer_Name | TEXT | Required |
Channel_ID | TEXT |
|
Channel_Description | TEXT |
|
Delivery_Address_Name | TEXT |
|
Delivery_Address_Line_1 | TEXT |
|
Delivery_Address_Line_2 | TEXT |
|
Delivery_Address_Line_3 | TEXT |
|
Delivery_Town | TEXT |
|
Delivery_County | TEXT |
|
Delivery_Postcode | TEXT |
|
Delivery_Country | TEXT |
|
Order_Date | DATE | Required YYYY-MM-DD |
Delivery_Date | DATE | Required YYYY-MM-DD |
Customer_Order_Ref | TEXT |
|
Internal_Notes | TEXT |
|
External_Notes | TEXT |
|
Type | TEXT | Required “order”, “quote” |
Status | TEXT | Status of the order, e.g. “picked” |
Hold_Status | TEXT | Reason for hold |
Created_By | TEXT |
|
Source | TEXT | e.g. Caliq, Madics, Sage |
Order Lines (Optional)
Field Name | Type | Additional Info |
|---|---|---|
Order_ID | TEXT | Required |
Line_ID | TEXT | Required |
Product_ID | TEXT | Required |
Product_Description | TEXT | Required |
Unit_Price | REAL | Required |
Original_Price | REAL |
|
Quantity | REAL | Required |
Quantity_Outstanding | REAL |
|
Line_Value | REAL | Required |
Value_Outstanding | REAL |
|
Warehouse_Value | REAL |
|
Allocated_Value | REAL |
|
Required_Date | DATE | YYYY-MM-DD |
Estimated_Date | DATE | YYYY-MM-DD |
Sort | TEXT | Line order sequence |
Unit_Cost | REAL |
|
Line_Cost | REAL |
|
Sales Rep Forecasts (Optional)
Field Name | Type | Additional Info |
|---|---|---|
Sales_Rep_ID | TEXT | Required |
Sales_Rep_Name | TEXT |
|
Accounting_Year | TEXT | Required |
Accounting_Period | TEXT | Required |
Sales_Target | REAL |
|
Sales_Forecast | REAL |
|
Products (Required)
Field Name | Type | Additional Info |
|---|---|---|
Product_ID | TEXT | Required |
Product_Description | TEXT | Required |
Product_Status | TEXT |
|
On_Toucan | TEXT | “True”, “False” |
List_Price | REAL | Required |
Cost_Price | REAL |
|
Quantity_Warehouse | REAL |
|
Quantity_Available | REAL |
|
Quantity_Allocated | REAL |
|
Quantity_Picked | REAL |
|
Next_Purchase_Order_Date | DATE |
|
Next_Purchase_Order_Quantity | REAL |
|
Quantity_Inner | REAL |
|
Quantity_Outer | REAL |
|
Quantity_Pallet | REAL |
|
Quantity_Default | REAL |
|
Analysis_1 | TEXT | E.g. product group 1 |
Analysis_2 | TEXT | E.g. product group 2 |
Analysis_3 | TEXT | E.g. product category |
Analysis_4 | TEXT |
|
Analysis_5 | TEXT |
|
Analysis_6 | TEXT |
|
Analysis_7 | TEXT |
|
Analysis_8 | TEXT |
|
Analysis_9 | TEXT |
|
Analysis_10 | TEXT |
|