When importing files into Sage 200, the file must be saved in a CSV format; this is text in notepad format with each cell separated by a comma.
However, it can also be opened in an Excel format. When populating a CSV file, I would encourage you to have a ‘working Excel file’. You can then use this to copy and paste into your CSV file.
In this blog, I will share with you some of my top tips to follow before importing your CSV!
1. Double Check You Column Titles
Your column headers need to be exactly right for the file to import successfully. This means you can not remove any columns that you don’t need or change any headings. Before you save your CSV file, double check that all the headings are exactly the same as your example file.
2. Make Sure You Have the Correct Field Types
Any columns containing numbers can sometimes change when entered as different format fields within Excel. You can change the format of a cell (or group of cells) by highlighting the area you want to change, and heading to the ‘Number Tab’.
Here, you can change the field type from ‘General‘ to ‘Text‘, this will keep whatever is there as simple text. This is also relevant when copying cells from a ‘working excel sheet’. Make sure you paste as values into the file.
3. Populate Your Optional & Mandatory Fields
Within each CSV file, there will be areas that are Mandatory and areas that are Optional. Make sure any areas marked as Mandatory are filled in but also that you check the Optional Fields. Some optional fields will be Mandatory in certain cases, for example… some fields may be optional unless the transaction is a receipt, in which case the field becomes Mandatory.
4. Use the Correct Number of Decimal Places
If a field asks for a number to two decimal places, the safest way to achieve this is to change the field type to allow two decimal places.
You can do this by going to Home > Number > Reduce Decimal Places. However, when you do this you will notice that in the cell it will be 2dp, but in the detailed box above there are still multiple decimal places. From here, the best thing to do would be to copy the column and paste over the top as values. This way, when you save as a CSV file, only 2 decimal places will show.
5. Perfect Your CSV Saving Routine
After you have completed your Excel file, you will need to save the file as a CSV. When you do this the safest way is to go to Save As > Change the document type to CSV. After you have done this, close the file. When you do close the file, it will ask you if you want to save changes, click ‘No’.
Your file should then be ready to import!
What did you think of this blog? If you found this blog helpful, or not, please let us know. We love feedback and we are always looking to improve our content here at itas, for you… the consumer!
If you have any questions or queries, please don’t hesitate to get in touch!
Kate is a self proclaimed Data Nerd! A specialist in Business Intelligence, she loves creating reports and dashboards the help solve problems. Outside of work, Kate can usually be found on the football pitch.