Reading Time: 5 minutes
When preparing data for Sage imports invariably most of us will be working with the data in Microsoft Excel. Whether inputting the data from scratch or pulling through from other sources, there are some handy Excel tricks to help create and prepare your data. From generating the values using formulas or cleansing data to remove any potential errors below are some of my favourites!
Changing views in Excel
When working with spreadsheets that use multiple formulas its useful to toggle between data and formula views. You can do this in Windows by using Ctrl + Grave Accent Key to change between the views.
Working with formulas
Lets take a moment to consider the language used. A formula is an expression which calculates the value of a cell. Functions are predefined formulas that are already available in Excel.
A formula starts the moment you type an equal sign = into a cell. This is the instruction to let Excel know you want to enter a formula.
Very often you might need to create a unique reference for each record to use for analysis or to locate the record once imported. You might want to create the reference based on a combination of existing fields or using set text or data. Excel offers both a function and a cheat when combining records!
The function used is CONCATENATE to pull a number of values into one string. Very simple to use and you can combine with text elements. In newer versions of Excel this is now the CONCAT function but CONCATENATE will work in all versions for backwards compatibility.
Straightforward to use, it is literally a statement specifying which values to combine separating each value with a comma.
You can combine with specified text items by using quotations marks around your text item. For example if you wanted to add a blank space you would embed ” ” within the function.
The cheat is that you can simply use the & symbol to generate the same result. Handy to know when typing quickly but I recommend using the function if sharing spreadsheets as it allows other users to identify what is happening in the cell more clearly.
You can also embed additional functions within your statement. For example you might want to use the date function TODAY. If you are preparing the import file over a number of days you can use this function to bring back the current date to to add to your reference when ready to import.
Now if you notice in the last step the TODAY date function returned a numerical value. This won’t be recognisable to your users so you can combine with a text format function to show as desired. I’ve used TEXT below and specified the date format required in the quotation marks. This function will work with dates, times, number formats all manner of things.
Had enough?? No? Then let’s continue!5r
It might be when you’re creating your reference you want to extract only certain elements from an existing reference. This can be easily done and you have a host of function choices depending on where in the string is the data you want to use.
LEFT – Will return a value starting from the beginning up to the number of characters you specify
MID – Will return a value from the point you specify up to the number of characters you require.
RIGHT – Will return a value starting from the end up to the number of characters you specify
Many import fields in Sage are limited to a number of characters. You can use the LEN function to check the length of your string.
Another handy function is TRIM. Formulas and previous downloads can sometimes generate blank characters. Use TRIM to remove them.
Sage Import files are CSV files. One of the most common errors is leaving items such as commas within values to be imported! A CSV file reads a comma as the start of a new column so extras will cause errors in the import. Check out our blog on troubleshooting import files.
You can use ‘Find And Replace’ to simply locate one value and replace with another. Sometimes however you may find this can corrupt data or remove values when working with alpha / numeric values. A great function is SUBSTITUTE which allows you to specify the value to be found and what you wish to replace with. In the example below I have used it to remove the dashes between the sort code.
Finding Sage Import Templates
You can locate the template import files by accessing Sage help. Within Sage use the help icon (?) on the navigation top bar or press F1 to bring up the help website. Then search for imports and you will have the different options displayed. If you’re not sure which file you require contact us and we will be happy to advise or supply.
Making imports from Excel easier
If you are doing frequent imports or using custom spreadsheets to build your import files you might want to take a look at how Codis Excelerator can help speed up your import processing. Contact us and we’ll be happy to arrange a demonstration.
Get in touch
Don’t forget you can register today and download our free Sage 200 guides. If there are any guides you’d like to see then contact us and let us know what you would like!