Hi can anyone help with importing an Excel spreadsheet into Sage? Can it be done effectively? I'm just a bit concerned that things will be allocated to the wrong codes and it all going horribly wrong.
I assume it is Sage Instant or Sage Line 50 / Sage 50 Accounts you're using.
I can assist with "hand holding" if you like - just drop me an email. You've probably noticed the File > Import facility available as standard. I've always found it very reliable. The main thing I've used is the Audit Trail import. The awkward thing is that the Excel file must always quote the numbers as positives - the transaction type, nominal code and account reference tell Sage about what gets debited and what gets credited. Journals are an exception to the norm in needing two lines, a JD line for the debit side and a JC line for the credit side. The JC is a positive figure - Sage infers a credit from the JC transaction type.
So long as you take a backup before you start you'll be OK. If you're super nervous you could have an initial play in the Practice Company.
If you want to import things not supported by standard Sage functionality, there are quite a number of 3rd party suppliers whose products do a good job too.
Regards,
-- Edited by Onion4Sage on Tuesday 23rd of February 2016 02:36:07 PM
Hi, I am using Sage 50 Accounts and it is really just the bank/cash purchases (fuel/materials etc) that need importing as there are just so many of them. The purchase and sales accounts I can cope with entering manually as they are fairly large amounts but not many of them if that makes sense.
Hi
I've used the import process but only for sales invoices. Alongside what Ian has suggested, I would also say, take double back up and test it works. Take great care when mapping your data so it has the same headings sage is expecting ( sage takes you through what is required when you start the import process) and maybe consider doing a small test of part of the Bank statement items or doing it in batches perhaps until you are familiar with what is required. I might have a spreadsheet somewhere, but can't look now until tomorrow (will sling it on here if I can find it or PM you if your profile allows). Although if I were you, I would take up Ian's offer!!
What year is the sage version as some have Bank feeds now?
__________________
Joanne
Winner of Bookkeeper of the Year 2015, 2016 & 2017
Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.
You should check out answers with reference to the legal position
I forgot to mention that Sage provide the Excel templates with which to do the imports. They'll be somewhere amongst your Sage program files. Mine are at C:\Program Files\Sage\Accounts\Import Templates. The list I have is:
Hi Ian
I never knew where I could find these from one minute to the next so I saved them all to a 'useful' folder I have, so I sent the audit trail one to Sam last night. Thanks for the suggestion on the location though as Im thinking of setting one up at a clients so will have a nosey in their program files too. Be interested to know how Sam gets on, as it may (initially at least) take a bit more faffing around to get the data in the right format.
__________________
Joanne
Winner of Bookkeeper of the Year 2015, 2016 & 2017
Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.
You should check out answers with reference to the legal position
Just had a quick go, I used the audit trail template, copied my data into the right columns and mapped it in Sage. Only problem is that it keeps saying there are no records to import so am a bit stuck. Any ideas?
Hi Sam
It might be something to do with the transaction allocations as I dont think you can use all of them Eg SR needs to be SA and then you need to manually allocate them, but not entirely sure. Might be worth you having another go and then take a screen print of where you get the error message so we can see the exact point, plus exact wording of the message. Also perhaps a copy of the sort of thing you have on your spreadsheet. (All with items suitably blanked out so we cannot see who it relates to!)
__________________
Joanne
Winner of Bookkeeper of the Year 2015, 2016 & 2017
Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.
You should check out answers with reference to the legal position
All mapped fields must contain values except when the type is either JD or JC when the Account Reference field may be left blank (it must still be mapped though).
As Joanne says, some Type codes are not allowed. The ones that are are listed below:
BR = Bank Receipt
BP = Bank Payment
CP = Cash Payment
CR = Cash Receipt
JD = Journal Debit
JC = Journal Credit
SI = Sales Invoice
SC = Sales Credit Note
SA = Sales Receipt on Account
SP = Sales Payment
PI = Purchase Invoice
PC = Purchase Credit Note
PA = Purchase Payment on Account
PR = Purchase Receipt
VP = Credit Card Payment
VR = Credit Card Receipt
I have just come across this in a Sage help item. It adds to what Joanne mentioned above:
Because Sage Accounts can't import transaction allocations, you can't import transaction types SR, PP, SD and PD. As a workaround, you can change the transaction types as follows:
SR - Change to SA.
PP - Change to PA.
SD - Change to SC.
PD - Change to PC.
I hadn't ever thought about the SD and PD items. Maybe this will help you out if you're still having issues?
