Data Files

When data is loaded into PIM360 or CLS360 it is done so via a tab delimited text file, (also excel files in PIM360 only).

Data that is destined for CLS360 or PIM360 quite often comes from other software, or applications (such as word, excel etc), each of which can apply their own formatting, which can affect how the data looks and behaves in a text file.

The following guide provides examples of common issues with data when transferring to a text file for import to CLS30 or PIM360. Each should be considered when creating new data files.

Data File Headers

Case sensitivity: when loading data files be aware that PIM360 and CLS360 are case sensitive, this means that the following data file headers (column headers) would all be interpreted as different attributes:

• Tag Number

• Tag number

• tag Number

• tAg number

• etc.

Spaces: quite often trailing and leading spaces are added to data file headers, these can be removed in Excel using the TRIM() function, or by carefully reviewing the data file attributes in a text editor.

Loading in headers that are incorrect (i.e. not as stated in CLS360), will result in new attributes being created in CLS360 (if loading to CLS360), and data being placed in a temporary unsearchable attribute in PIM360.

Minimum Attributes Required for Each Data Load in PIM360

• Facility

• Tag number / equipment ID / document number

• Class.

Required Attributes for Association Imports

• Facility (for first object)

• Tag number / equipment id / document id

• Facility (for second object)

• Tag number / equipment id / document id.

Encoding

Save all text files with UTF-8 encoding

To do this in notepad:

Click the Save option from the File menu.

A dialogue box will appear, there is a drop-down box named encoding next to the save button, click on it and select UTF-8, then save in the normal way.

To do this in Notepad++:

Click on the Encoding menu, select Encode in UTF-8.

Separating out UoMs

Registers and other data sources will have within them measure attributes, Operating Pressure for example. More often than not the UoM and the value will be in the same field. In order to benefit from PIM360s UoM validation, the UoM needs to be stripped out and placed in a column next to the measure attribute called UOM.

Loading Lookups in CLS360

When bulk uploading into and attributes look-up tab, be aware that the order in which the data is loaded, will be the order the data shown in PIM360 / CLS360.

If the look-up list should be sorted in a particular way, alphabetically for example, make sure this is done prior to loading it.

Beware of using special characters in look-up descriptions, i.e. / : etc. as it can cause errors when loading data.

Dates

When copying data that is formatted as a date from one application to another, (excel to notepad++ for example), a default blank time might also be copied over, i.e. the date 01/01/2016 might become 01/01/2016 00:00:00 in the text file.

Non removal of these blank times will not result in a failed load, but the data will be loaded in PIM360 or CLS360.

Integers

When copying data that is formatted as an integer from one application to another, (excel to notepad++ for example), a two-digit blank decimal may also be copied over i.e. 11 may become 11.00.

Non removal of these blank decimals will not result in a failed load, but the data will be loaded in PIM360 or CLS360.

Text Identifiers

When copying data from one application to another, (excel to notepad++ for example), text identifiers, i.e. speech marks “” may be placed at the beginning and end of a fields data automatically.

This normally happens because the data in the field has an item within it that means something different in the application that the “” are showing in. the following points are examples of different items that may cause issues:

• Carriage return line feeds: when a tab delimited file is loaded to PIM360 or CLS360, a new line indicates a new row of data. If there are erroneous carriage returns and line feeds (CRLF), (normally created by hitting the return key within the source field), then the file will not load correctly (i.e. it will fail to load as the row did not have as many attributes as expected, or it will load and will create erroneous data entries). These CRLF need to be removed prior to loading.

• Inch signs “: inch signs are quite often found within pipeline tag numbers, indicating the size of the pipeline. The inch sign is also the symbol that indicates the start and finish of text “”. The possibility exists that the receiving application may place additional “ around the inch symbol in order for it to process it as expected, i.e. 2”-PA-080001-FS-NI would become 2”””-PA-080001-FS-NI. At the best case the data will look unsightly, at worst it occurs within a unique identifier (tag number, doc id, attribute name), and a new record is created.

• Text fields “”: when a field defined as text is copied across, it could be brought over with the speech marks appended at each end i.e. some text becomes “some text”. Although this won’t stop the data file loading, the speech marks will be loaded.

• Tabs: the source file may have a tab within its field. The data load files are tab delimited so an additional tab will misalign the data to the headers and cause an error when loading.

Non Ascii Characters

A non-ascii character are special characters and symbols, which you won’t find on your keyboard.

Non ascii characters such as ¼, ½, € may find their way into the data load file and need to be removed.

To do this in Notepad++ click on the Search menu, then select Find characters in range… (it’s at the bottom) and select the Non-Ascii Characters (128-255).

You can then step through them all deciding to remove them, or to use the find and replace function to replace a lot all in one go, i.e. ¼ replaced by 1/4.

Excel Auto Formatting

Excel will auto format certain data that it recognises as particular data types, which on occasion is not what is expected. The following are a list of things to watch out for:

• Leading 0 removal (to resolve the field must be formatted to text).

• Data separated with . or / formatted to dates.

• If your data value starts with = excel will recognise it as a formula and try to calculate it, when it can’t you will see #ref, #value etc. instead of the data.

Advice on Removal of Erroneous Data in Data Load Files

When using Notepad++, the find and replace function can be utilised.

To access this function press CTRL + F from within Notepad++, in the dialogue box that opens select the Replace tab.

There are three search modes:

• Normal – this will search for an exact match of what you type.

• Extended – this includes none visible characters like carriage returns (\r), line feeds (\n) and tables (\t)

• Regular expression – this allows the use of regular expressions to build up a pattern that is to be searched for, and all items in the file that match that pattern are returned.

For example “”” may be an issue in the data load file. Type “”” in the Find what: box, and type “in the Replace with: box. Ensure Search Mode = Normal and click Replace All. All instances of “”” will now be replaced with a singular “.