OA contains a number of functions that allow users to “upload” data into the system without the need to complete the specific data entry in OA. The reasons for doing this are numerous, but most importantly it is for the speed of data entry. A spread sheet allows users to add, edit and change long lists of data using a tool that deals well with the navigation of significant row and column information.
The notes contained here have been written with the Excel spread sheet tool in mind as it is be most common third party software used for this purpose. However the details contained here should apply to any tool used to create or edit a data file used for upload into OA.
The first item to understand is that in OA we almost always use a ”csv” file format will for uploads. “CSV” stands for Comma Separated Values. This format has for years been a common text file format used by file editing tools and most spread sheet applications. You should be able to perform a “Save As” operation in your spread sheet software and be able to select this type of file format for the data you have created. With this format the “,” comma character is used as a delimiter between each column of data you are saving. Hence the formats name. It is therefore extremely important that the data you enter does not contain any such character within it. (See tip below).
Secondly it is highly recommended that you set the formats of the columns of your spread sheet to use a “text” format. The use of formats like numeric, currency, or custom formats to display the column data in a more presentable way in the spread sheet will only cause problems during the upload as these formats will change the way the data is stored in the file. For a successful upload we want the raw data only with no formatting whatsoever as additional characters in the data just cause validation issues during the upload process.
Note: Please be aware that products like Excel will change the format of the data you enter when columns are not set with standard text or string input format. A good example of this are the many date formats in Excel which will change significantly the way any dates you enter are displayed and stored in the data file. The data formats may also be changed “automatically” when a product like Excel opens or Imports a file, or if you change the file type and then change it back again. Please check the formats before proceeding with the upload into OA, or if you are having issues getting a file to upload.
Tip: where you have some text that may contain a ‘comma’ e.g. an address such as “12 Derby St, Luddenham”, using Excel when you do ‘Save As’ in ‘csv format’, the text is automatically surrounded by quotes (“”). You can verify this by editing the saved file in a simple text editor such as Notepad.