Importing Journals into Sage Accounts Plus 2009 V15.01 from Excel

 Does anyone know how to import journals from excel.

 In what format should the data be arranged and should the file be in a particular format eg. CSV ?

 Thanks 

Comments

CSV Import

johndon68 | | Permalink

The file does need to be in a CSV format.  The format of the file is actually in the Sage Help File but the columns are as follows with one transaction per line of the file:

Transaction Type 
Account Reference 
Nominal A/C Ref 
Department Code 
Date 
Reference 
Details 
Net Amount 
Tax Code 
Tax Amount 

If you want to see what a CSV file should look like, you can actually create one in Sage itself.  Go to Financials -> Reports -> Miscellaneous Reports and run the Transaction CSV report.  Save it as a CSV file and you'll have a file in the exact format for importing.

John

CSV Import from Excel

vorabj | | Permalink

 Thanks John.

 Tried your format using the columns suggested. But there are errors as the data is not in the correct column order.

 Import was done via File > Import > Nominal Ledger

Import

johndon68 | | Permalink

That's to import nominal accounts, you need to go to File -> Import -> Audit Trail Transactions.

John 

CSV Import from Excel

vorabj | | Permalink

 Ok I will try that and will let you know if it works for me.

 Q. How do you enter a code for eg 11 -  I have it in 2 digit number format but Sage requires in 4 digit '0011'.

 Do appreciate your help.

 Thanks

Nominal Codes

johndon68 | | Permalink

The codes have to be exactly as they appear in Sage so, if the code in Sage is 0011 then it has to appear that way in the CSV file.

John 

gssdubai's picture

CSV File with leading zero

gssdubai | | Permalink

Before saving your CSV File in Excel,

Select the Column where your Nominal Code is; 

Then, Under "Format Cells" command Choose Text  - Custom and then Format should be 0000 (Four times zero)

Save CSV File  Now. It will not remove leading zeroes

 

 

CSV Import from Excel

vorabj | | Permalink

 Thank you for your help.

 It worked fine ..thanks once again

Leading zeros in nominal codes

Alf | | Permalink

I have just followed the procedure suggested by johndon68 but when I save the csv file Excel doesn't allow me to keep the custom format ("0000") that retains the leading zeros (I am using Excel 2003)?

Losing the zeros

karlhobbins | | Permalink

If you put the following symbol before the digits it retains the 0's for the import    '   (on the @ key)

However when you go back into the saved csv file the 0's will have disappeared again.  As this is a csv file it will remove the ' and lose the 0's.

 

Leading zeros solved

Alf | | Permalink

Thank you both for your help - either way seems to work as long as I don't re-open the csv file before running the import, as that is when it seems to lose the formatting.

ara.martirossian's picture

Import the journal directly from Excel with out using CSV

ara.martirossian | | Permalink

Hi, you can import the data Journal directly from Excel into Sage 50 with out having to go to CSV using Excel2Sage. You can see videos of this and other imports below:

http://www.excel2sage.co.uk/index.php/tutorials/

Please email support@excel2sage.co.uk if you need any help.

 

 

 

Add comment
Log in or register to post comments