Please note the following with regards to CSV file formats when importing into Sage Accounting:

• There should only be 3 columns (Date, Description & Amount) in that order.
• These column headings should be spelled correctly.
• Delete all additional columns.

Ensure that date format is any of the below.

Ensure that the column headings are in the first row.

Each column also has its own rules (details below):

Date

• 2 numbers for the day
• 2 numbers for the month
• 4 numbers for the year
• To be safe use a slash as the separator
• Either 30/01/2019 or 01/30/2019 or 2019/01/30
• These should also be in date order

Description

• There should be no double spaces
• These can be removed easily using the Excel “Trim” formula
• Formula: =TRIM(cell containing text)

• Descriptions should not be longer than 100 characters

Amount

• There should be no comma thousand separators “6,888.99”
• The decimal should be a point “.”
• There should be no currencies “R688.99”

Excel tips

1. If your CSV includes the date format as dd Month(in words) yyyy eg 02 Dec 2020, you can reformat your date to dd/mm/yyyy as follows:

• Right click on the Date column, select Format cells.
• Under the Number tab, select Custom>General.
• Delete the word General in the Type field and type in “dd/mm/yyyy”
• Click ok

2. If you have the date format ddmmyyyy as displayed below or yyyymmdd you can reformat your date as follows:

• Insert a column on the right-hand side of the date column
• In cell B2 you will paste this formula: =CONCATENATE(RIGHT(A2,2),"/",MID(A2,5,2),"/",LEFT(A2,4)) and press enter      
• Then drag down the formula all the way down the column
• Select the column and copy all dates
• Paste the values in cell A2 so that all the values can be replicate in the first column
• Delete the second column
• Save file

3. Should all your data in your CSV file fall into one column follow these steps:

Highlight the whole column:

Click on Data on the headings and choose Data, then choose text to columns:

Then choose the following options as per the screenshots below:

First choose delimited: (click next)

Then choose whichever delimiters that is on your excel file, in the above case, the separators are commas: (click next)

Then you will receive a preview screen of how your file will reflect, (then click finish.)

Your file will now have its own columns as per the screenshot below, then delete the columns you do not need.

Once all has been removed, save the file and import into Sage Accounting