Solar Accounts

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:



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).

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

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.

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.
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
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 *

* Repeat for Line 2, Line 3, etc.

Column Name	Column Type	Required?	Example	Description
Type	Text	Yes	Invoice Payment	Must be either 'Invoice 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.
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. Note used for an invoice payment.
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)

* 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.
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 *.

* 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.
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)

* Repeat for Line 2, Line 3, etc.

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)