Oh boy, am I glad I've found this place!!!!!!!!!!!
I'm tearing out what little hair I've got left trying to import audit trail transactions from Excel into Sage. I thought I'd finally got this sorted but am now completely stuck.
I've attached screen shots of the Excel spreadsheeet I'm using and the errors I'm getting (some of them anyway!
I've checked all nominal codes, account references etc and they are all correct. What I really don't get is that some of the lines don't show any errors at all.
I must be doing something quite simple incorrectly! If anyone can help I'd be extremely grateful.
Hi Jon
Never seen so many errors on an import, lol.
Couple of questions first - is the default tax code for all your sales/individual accounts set as T9 in sage?
- are the account names set EXACTLY as they are in the spreadsheet
- have you done some updating of the spreadsheet before attaching it to the forum posting - the error codes are showing the net amount cannot be left blank in record 3, but it clearly has a figure in it!!
Worth noting that the error in line '2' usually means line 3 of your spreadsheet as it discounts the header line (well it does in the ones I use)
Im unsure on why the report is showing invalid data for transaction type is showing as incorrect unless your field mappings are not matching the ones you have set up in sage (or are you using the default ones) - Im assuming they are sales invoices as much as Im assuming the code field is your sales nominal code. Also - your standard sage mappings will have things like department - if you leave it in the mapping you need to leave a blank column in your spreadsheet. I always do as its easier than thinking about changing the mappings (but then Im lazy!
Hope that helps!
__________________
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
Note: I'm on Sage 50 Accounts Plus 2013, so things may have changed in the last couple of years - but probably not significantly.
When you import, after the file selection, you will probably see a screen called 'Field Mappings' - this allows you to specify what columns in the spreadsheet match what fields in Sage. One possibility is that these mappings are incorrect.
Looking at your screen grab, you probably want:
A = Type (text) B = Account Reference (text) C = Nominal A/C Ref (integer) D = Date (date) E = Reference (text) F = Details (text) G = Net Amount (decimal) H = Tax Code (text) I = Tax Amount (decimal)
Start by double checking that these mappings are set up in that screen. If any one of them differes, it could prevent the import (though I wouldn't expect the errors to be quite so spurious - I'd expect more consistency).
Another possibility is that the fields aren't set as the right 'type' for the import - if any of your Excel columns are formatted as the wrong type, then that might cause problems. For reference, I've put the correct data types in brackets after the columns above.
A date should take the form dd/mm/yy or dd/mm/yyyy
An integer is a number with no decimal part - e.g. 4000
A decimal is a number with a decimal part - e.g. 999.99 (though an integer could probably be read by Sage as a decimal).
Normally, I'd export the Excel spreadsheet - if I used Excel! - as a CSV file, which is what I'd then import (though I think Sage can import directly from an Excel file); if any of the columns are incorrectly formatted, this might result in the CSV file being incorrect.
Also, on the screen where you select the file, make sure the box marked "First row contains headings" is ticked, judging by your screenshot. (That could also cause problems - but all the errors would refer to the first line).
__________________
Vince M Hudd - Soft Rock Software
(I only came here looking for fellow apiarists...)
Hi Joanne, Thanks for the reply :) Default tax code is indeed set at T9 for everything & account names are exactly as in spreadsheet (I actually imported those from a spreadsheet that was copied & pasted from this one). I haven't amended the spreadsheet in any way, what I've put on here is exactly what I'm trying to import. I've allowed for the header line by ticking the "First row includes headings" box. The mapping is set as in spreadsheet attached.
Basically, the information in the details box was over two lines (copied from the invoices that are raised on another spreadsheet). I put all of this on one line instead then ran the import, et voila!
I've no idea why this would make a difference but it clearly does.
Might be useful information for anyone else who encounters this issue.
I'm wondering if there is a problem with the mapping from your spreadsheet to Sage. It looks like it is getting out of sync.
No errors, 1 error, 4 errors, no errors, 1 error, 4 errors ... is a repeating pattern which might be caused by a some sort of field mapping problem.
Regards,
Edit: You had it sorted before I posted . I think it may have been some sort of sync thing with the new line character in the two line field throwing the mapping out. Once you removed it the mapping worked.
-- Edited by Onion4Sage on Tuesday 14th of July 2015 07:41:46 PM
I almost suggested there might be too many character in the details box but it wasnt throwing up that as an error. But of course we also couldnt see it was over two lines. It makes a difference cos it doesnt match your mappings and the details line is only one line in sage/only has so many characters (Vince would explain it way better!
Well done for sorting it though - you wont make that mistake again, ha. Oh and welcome to the forum btw!
__________________
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
"But of course we also couldnt see it was over two lines. It makes a difference cos it doesnt match your mappings and the details line is only one line in sage/only has so many characters (Vince would explain it way better!"
Seems good enough to me. Well, ignoring the "cos" (ugh!)
The most succinct explanation I'd offer, if I was feeling obtuse, would be:
Invalid file was invalid.
As you said, there was no way for us to see that the details were on multiple lines from the screen grab. Why doesn't Excel do something to indicate that?
I've just tried this in OpenOffice Calc, and if not all of the cell contents are visible - whether that's because it's multi-line, or too much text to be displayed due to the width and contents of the next cell - it indicates that by displaying a red triangle at the right edge of the cell.
Saving a file with a split line as CSV shows the cell contents - including the newline - are enclosed in quotation marks. However, I'm not sure if the resulting CSV file is valid as a result - I'd have to look that one up!
But anyway, that's an aside to the problem that has now been solved.
As for the jollies... no, I've just been too busy to do any more than just glance at the forum's feed.
__________________
Vince M Hudd - Soft Rock Software
(I only came here looking for fellow apiarists...)
. I think it may have been some sort of sync thing with the new line character in the two line field throwing the mapping out. Once you removed it the mapping worked.
