Instead of manually typing data into Solar Accounts, you can create a Comma-Separated Values (CSV) data file which can then be imported. The layout of the file must follow the rules described below. Here is an example of a CSV file which creates a supplier and two payments:
Tip: The simplest way to create a CSV in the correct format is to export a sample data set from Solar Accounts. To do this, click menu File > Export To > Comma Separated CSV File. The file can then be edited in a spreadsheet program and imported back into Solar Accounts.
The first row in the CSV file must be a header row containing the names of each column. The first column must be named 'Type'; the remaining column names depend on the type as described below. Solar Accounts will process each row in turn and create a new data record of the specified type (or update the existing record if it already exists).
Customer and Supplier Format
Column Name
Column Type
Required?
Example
Description
Type
Text
Yes
Customer
Must be either 'Customer' or 'Supplier'
Name
Text
Yes
ABC Supplies Ltd
The customer or supplier's name
Phone 1
Text
No
0208 444 7820
The customer or supplier's primary phone number
Phone 2
Text
No
0208 444 7824
The customer or supplier's secondary phone number
Fax
Text
No
0208 444 7877
The customer or supplier's fax number
Email
Text
No
someone@example.com
The customer or supplier's email address
Website
Text
No
www.example.com
The customer or supplier's web site address
Contact
Text
No
Paul Jones
The customer or supplier's contact name
Salutation
Text
No
Mr
The customer or supplier's contact salutation
Notes
Text
No
Closed over Christmas
Private notes related to this customer or supplier
Bill To Address
Text
No
134 High Street Welton
The billing address of the customer, or address of a supplier.
Ship To Address
Text
No
27 New Abbey Road Newport
The shipping address of the customer. Not used for suppliers.
EU VAT Authority
Text
No
AT
For customers registered for VAT outside the UK, this is the two-letter country code of the VAT authority
EU VAT Nuber
Text
No
U12345678
For customers registered for VAT outside the UK, this is the VAT number as issued by the given VAT authority
Account and Account Group Format
Column Name
Column Type
Required?
Example
Description
Type
Text
Yes
Account
Must be either 'Account' or 'Account Group'
Name
Text
Yes
Depreciation Expense
The account name
Group Under
Text
Yes
Expenses
The name of the account's parent in the account tree. Not required for root account.
Notes
Text
No
Includes computer depreciation
Notes associated with this account
Opening Balance
Amount
Yes
£4,027.40
The opening balance for the account. Not required for an Account Group.
Invoice, Credit Note, Quote and Purchase Order Format
*Repeat for Line 2, Line 3, etc.
Column Name
Column Type
Required?
Example
Description
Type
Text
Yes
Invoice
Must be one of 'Invoice', 'Credit Note', 'Quote' or 'Purchase Order'
Date
Date
Yes
01/07/2010
The date of the document
ID
Text
Yes
INV142
The document ID (Invoice Number, Credit Number, etc)
Memo
Text
No
Sale; Paul Jones
The journal memo for this document
Name
Text
Yes
Paul Jones
The customer or supplier name for this document.
Address
Text
No
72 Castle Lane St Cyrus
The shipping address for an invoice. Not used for other document types.
Counterparty Reference
Text
No
ABC2345
The customer or supplier's reference number.
Comments
Text
No
Thank You!
The comment for this document.
Other Information
Text
No
Please pay into account 12345667
The extra information for this document.
Edit Inc VAT
True/False
No
False
If True, the VAT-inclusive price will be shown when editing the document. If False, the VAT-exclusive price will be shown.
Credit Terms
Text
No
Prepaid
The credit terms for this document.
Job or Department
Text
No
South East Region
The job or department associated with this document.
Notes
Text
No
Payment for the gas
Internal office use-only notes for this transaction.
If the Flat Rate Scheme applies to this document, this is the flat rate used.
VAT Direct Rate
Percent
No
17.5%
If the VAT Treatment requires that customer pays VAT directly to HMRC (eg. purchase of goods from EU) this is the rate that used to calculate the VAT amount
VAT Force Non Cash
True/False
No
True
If set to 'True' then VAT will be calculated when the invoice is issued (not paid) even if the business normally uses the 'cash method' for calculating VAT figures.
Lines
Integer
Yes
1
The number of lines on the document.
Line 1 Quantity
Decimal
Yes
4.5
The quantity on line 1
Line 1 Description
Text
No
Office Table and Chairs
The description on line 1
Line 1 Account
Text
Yes
Sales Income
The account used on line 1
Line 1 Unit Price
Amount
No
£266.6667
The unit price (excluding VAT) used on line 1 If this field is missing, the unit price will be calculated by dividing the line total by the quantity, then rounding to 4 decimal places.
Line 1 Amount
Amount
Yes
£1,200.00
The amount (excluding VAT) on line 1
Line 1 VAT Amount
Amount
Yes
£180.00
The VAT amount on line 1
Line 1 VAT Rate
Percent
Yes
15%
The VAT rate on line 1
Invoice Payment, Advance Payment and Credit Allocation Format
*Repeat for Line 2, Line 3, etc.
Column Name
Column Type
Required?
Example
Description
Type
Text
Yes
Invoice Payment
Must be one of 'Invoice Payment', 'Advance Payment' or 'Credit Allocation'
Date
Date
Yes
01/07/2010
The date of the transaction
ID
Text
Yes
PAY44
The ID of the transaction
Memo
Text
No
Payment Received; Paul Jones
The journal memo for this transaction
Job or Department
Text
No
South East Region
The job or department associated with this transaction.
Notes
Text
No
Payment for the gas
Internal office use-only notes for this transaction.
Account
Text
Yes
Cheque Account
The Paid From or Deposit Into Account for an invoice payment. Not used for a credit allocation.
Credit ID
Text
Yes
CRD20
The ID of the credit note being allocated. Not used for an invoice payment.
Name
Text
No
Paul Jones
The name of the customer or supplier who made or received the payment.
Advance Payment Amount
Amount
No
£400.00
The amount of the advance payment. This field is not used for other payment types.
Advance Payment VAT Rate
Percent
No
20%
The rate used when calculating VAT on the advance payment. This field is not used for other payment types.
Advance Payment VAT Is Flat Rate
True/False
No
False
Whether or not the VAT rate should be treated as a Flat Rate when calculating VAT on the advance payment. This field is not used for other payment types.
Lines
Integer
Yes
1
The number of lines for the transaction.
Line 1 Invoice
Text
Yes
INV401
The invoice number of the invoice on line 1
Line 1 Amount
Amount
Yes
£400.00
The credit amount allocated on line 1 . Use a negative value for debit amounts (eg. customer refund, supplier invoice payment)
Money Paid Out and Money Received Format
*Repeat for Line 2, Line 3, etc.
Column Name
Column Type
Required?
Example
Description
Type
Text
Yes
Money Paid Out
Must be either 'Money Received' or 'Money Paid Out'
Date
Date
Yes
01/07/2010
The date of the transaction
ID
Text
Yes
PAY56
The ID of the transaction
Name
Text
Yes
Paul Jones
The name of the customer or supplier for the payment.
Memo
Text
No
Money Paid Out; Paul Jones
The journal memo for this transaction
Job or Department
Text
No
South East Region
The job or department associated with this transaction.
Notes
Text
No
Payment for the gas
Internal office use-only notes for this transaction.
Account
Text
Yes
Cheque Account
The Paid From or Deposit Into Account for the transaction.
If the Flat Rate Scheme applies to this payment, this is the flat rate used.
VAT Direct Rate
Percent
No
17.5%
If the VAT Treatment requires that customer pays VAT directly to HMRC (eg. purchase of goods from EU) this is the rate that used to calculate the VAT amount
Lines
Integer
Yes
1
The number of lines for the transaction.
Line 1 Account
Text
Yes
Rent
The account allocated on line 1
Line 1 VAT Rate
Percent
Yes
0%
The VAT rate on line 1
Line 1 Amount
Amount
Yes
£400.00
The VAT-inclusive amount allocated on line 1 .
General Transaction Format
*Repeat for Line 2, Line 3, etc.
Column Name
Column Type
Required?
Example
Description
Type
Text
Yes
General Transaction
Must be 'General Transaction'
Date
Date
Yes
01/07/2010
The date of the transaction
ID
Text
Yes
GEN28
The ID of the transaction
Memo
Text
No
Bank deposit
The journal memo for this transaction
Job or Department
Text
No
South East Region
The job or department associated with this transaction.
Notes
Text
No
Payment for the gas
Internal office use-only notes for this transaction.
Lines
Integer
Yes
1
The number of lines for the transaction. That is, the number of 'From Account' lines plus 'To Account' lines.
Line 1 Account
Text
Yes
Cheque Account
The account on line 1
Line 1 Amount
Amount
Yes
£400.00
The credit amount on line 1 . Use a negative value for debit amounts (ie. the 'To Account' rows)
Job or Department Format
Column Name
Column Type
Required?
Example
Description
Type
Text
Yes
Job or Department
Must be 'Job or Department'
Name
Text
Yes
Sout-West Region
The name for the job or department
VAT Treatment ID
VAT Treatment ID
Description
0
Out of Scope
1
Sale of Goods or Services to UK
2
Sale of Goods to EU where Supplier Collects VAT
3
Sale of Goods to EU where Customer Accounts for VAT
4
Sale of Services to EU where Supplier Collects VAT
5
Sale of Services to EU where Customer Accounts for VAT
6
Sale of Goods to Customer Outside EU
7
Sale of Services to Customer Outside EU where Supplier Collects VAT
8
Sale of Services to Customer Outside EU where Customer Does Not Pay VAT
9
Purchase of Goods or Services from UK
10
Purchase of Goods from EU (Requires the 'VAT Direct Rate' to be set)
11
This treatment should no longer be used as it doesn't reclaim the VAT correctly.See here for details.
12
Purchase of Services from EU where Customer Accounts for VAT (Requires the 'VAT Direct Rate' to be set)
13
Purchase of Goods from Supplier Outside EU
14
Purchase of Services from Supplier Outside EU where Customer does not pay VAT
15
Purchase of Services from Supplier Outside EU where Customer accounts for VAT (Requires the 'VAT Direct Rate' to be set)