Import or Update Conquest Object data in bulk, from a comma separated variable (CSV) file or a TXT file. A Conquest Object is stored in one or more SQL Server tables in rows and columns, where the column is the name of the data. The CSV format requires the Conquest column names to be in the first row of the CSV file, with the data values in rows below; each value separated with a column, to match the first row.
Note: Keep the business processes that the Import Manager is to perform, as discrete as possible because mixing them can lead to data being rejected, ignored or misinterpreted; especially with financial Actions e.g. put ‘Purchases’ in one file, ‘New Works’ in another, parameter changes in another and so on.
Most people use Excel to construct and manage their CSV files, which means they don’t have to worry about commas. If you want to construct a CSV file by using a text editor, you manage the commas, the number of which must be the same in all rows.
The basic rules:
•Each Conquest Object ID e.g. AssetID, must be in the CSV file once only, otherwise later “AssetID”s will overwrite earlier ones. This does not apply in the case of Action Tasks, where the ActionID and TaskName are combined to be the unique identifier
•Each Conquest column name, in the first row of the CSV file, must be unique. Otherwise the entire file will be rejected
•Each Conquest column has a Data Type assigned to it in the database, so the CSV column data must be of that type or empty. Otherwise the entire file will be rejected
When bulk updating data, it is recommended that the Conquest Query Builder be used to generate the CSV file for the selection of Conquest Data Objects to be updated:
•Before running the Query, tick the ‘Use field names in SQL’ option
•Before doing the Export to CSV, check the columns that are generated in the output list and remove any that are not permitted Database Columns of the Conquest Data Object being exported
•Edit the CSV file and change the values that need to be, leaving all other data as is
The Data Dictionary Report is a useful aid for determining column headings. See Data Dictionary for details. Alternatively, view the columns in a software tool such as SQL Server Management Studio. There are exceptions for some column headings such as:
•tblValtrans.Comments column, which must be imported as “ValuationComments”
•Use “ActionEstQuantity” as the column heading, when Importing the Estimated Quantity of Actions but ‘EstQuantity’, when creating Action Resources
Things to be aware of:
Although this Help Section is focused on CSV files, TXT files can be used, based on the same structure as described below for CSV files.
Familiarity with MS Access is advantageous, as it is a good tool to manipulate the data for the creation of CSV files. However, many people prefer to use Excel although our experience has shown us that Excel tends to do some odd things with date, text and column formats, which can create problems.
When bringing in data from various places and formats, some data cleansing should normally be performed first. This entails going through the rows and removing erroneous data (e.g. text data in number fields) and, if data is coming from more than one source, ensuring it is structured in a consistent fashion (i.e. collecting the same sort of data for the same types of Assets). One way to do this is to; use MS Access to set up a new table that includes all the appropriate field names, run append and update queries to move the data into the table, export the table as a CSV file.
Since the 2.59 database, the Editor and EditDate columns have been provided in the tables for Conquest Data Objects. These columns can be included, when doing an import but there is little point, as they will immediately change to meet the Conquest Edit Data Business Rules.
Fields must be formatted into one of five data types:
•Integer - This is for ID fields such as TypeID, FunctionID, etc.
•Decimal - This is for any other number fields such as Dimension1, UserNumber1, etc.
•Yes/No - This is for true/false type data and must be formatted as 0 (for No) and -1 (for Yes)
•Date - This is for dates and must be formatted yyyy/mm/dd
•Text - This is for all other fields. Carriage Returns are not allowed in text values. A Text Field should have a double-quote at start and end with none between
•Don’t use the following special characters if you can help it:
•|
•“ (except two, as delimiters)
•&
When setting up date fields in Access it can be easier to define them as text and then run a query that copies the data in the “yyyy/mm/dd” format.
When including data that reference other tables, such as Functions, Types, Organisations, etc. the ID field of the referenced data must be used, not the description (i.e. FunctionID, TypeID). For fields that are drawing from the Code Editor (conditions, user lists, priority parameters) record the CodeID from tblCode for the particular data being referenced.