How to Import Data From a CSV File

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:

CSV Example

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.
VAT Treatment ID Integer Yes 12 The VAT Treatment ID for this document.
VAT Flat Rate Percent No 8.5% 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.
VAT Treatment ID Integer Yes 12 The VAT Treatment ID for this payment.
VAT Flat Rate Percent No 8.5% 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)