Navigation:  CONVERSION > Conversion of a Workbook into a Set Database

A database template passes on its inserted free fields to the database to create. The native field Unit can also be inherited and supplement the 5 basic fields.

QDV7 Converter executes automatically the phases #1, 3 and 4 of the process converting a spreadsheet into a grp file:

The phase #2 is manual or aided.

QDV7 Converter features two flaps whose separating line can be moved:

▪A right flap with the working spreadsheet; the third row can be used to fill in extra columns using formulas

▪A left flap with a table that lists out the mapped fields of the template, and the Tree key (optional index).

 

Each flap features its own focus. Both foci follow each other: to select a column, you can either click it on the right (or use the arrow keys  ← and →), or click the related field on the left (or use the arrow keys ↑and ↓).

 

Open

Click File>Open; in the dialog box, select Convert to a database of sets.

Select the spreadsheet file; QDV7 Converter opens in the right flap the file topped by two blank rows.

 

It is possible to enter new values either in the Converter, or in Excel by clicking Edit Outside With Microsoft Excel; both versions can be opened simultaneously; the changes made in Excel pass on to Converter when the xls file is closed.

To insert a new row, select the row that will follow/precede it, click Home>Edit>Insert and select Insert Rows Before/After.

To insert a new column, select the column that will follow it, click Home>Edit>Insert and select Insert Columns.

To enlarge/narrow the columns, click Home>Edit>Format>Column width.

To rearrange the sheets in the workbook or to hide some of them, click Build>Select Sheets.

 

Tool: bar Build

Map Columns

Click Build>Select Template and select a grp database.

To unravel the set columns and the articles columns, and match the columns and the template fields:

1.Right-click in a cell A1, B1 ... and select Map Column to Set OR right-click in a cell A2, B2 ... and select Map Column to Articles (alternative ways: double-click and menu Mapping>Map Column to Set/Articles); a dialog box Select column shows

2.Select a field (for more fields, check the box View all columns for mapping) and click OK; its name shows in the heading and in the left flap

3.Repeat with other columns; it is possible to map new columns and treat several sheets in different manners.

To speed up the step #3, preselect the fields in the Fields Manager: right-click their row and select Show by default in the converter.

 

The left flap shows, by default, only the columns Field and Data type (with the maximum char counts). To display Source column and Import field, drag the vertical separating line.

If a row owns at the same time a reference of set and a reference of article, it will show at both levels, as the reference is used to identify the rows.

To delete a mapping, on  step #2, select  the first item: 'Cancel mapping of this column' (in event of failure, select another field, then reselect 'Cancel...').

 

To aid mapping:

1.Select a heading row in the sheet (row 3 in the example)

2.Click Map>Identify according to Selected Row

3.Confirm in the message; then QDV7 Converter forcefully maps the columns whose label matches a field name.

 

In aided mapping, do not use any abbreviation in the reference row, as the matching must be total.

 

To delete all the mapping in a sheet, click Map>Clear Mapping.

To use a file QDV converter settings, see further.

 

The Reference field is indispensable at the upper level; at some others as well, according to the database expected behavior (refer to How to Change a Database Behavior). Click Identify and change the option if needed; the chosen option is repeated in the left flap.

Other fields:  

▪ Always Import, Bold, Italic, Underline: Y means Yes, anything else is ignored (it means No)

▪Color supported values: Blue, Green, Orange, Pink, Purple, Red, Yellow.

 

SOURCE DATABASE

The  Source database and Path # fields are used to test, in the check phase, the presence of the article reference in the specified database. Exception: Path 0, which means that the database is located in the same folder as the target set database.

A negative result produces:  

▪ orange color warnings upon checking

▪messages in the database, if you also request to fill  the articles list on building.

 

EXTENDED FIELDS

This applies to the articles columns and is used to define ahead a field missing in the template.

The field name becomes the name of the extra column, and the value in the cell becomes the value of the article being referred. Refer to How to Fill out the Fields of the List of Articles.

As the mnemonic will be automatically prefixed with /, you can enter the designation of a field already in the template.

 

Check Rows

QDV7 Converter checks the formats only at the intersection of rows with mapped columns. Note that a text is allowed for a quantity.

To check the conformance of imported data against the field format, carry out the following operations, at least step #3:

1.Select in column A a cell of rows to exclude from the estimate, and click Mapping>Exclude Row; the cell gets a red cross

2.To cancel the step #1, select in column A the cell of rows with a red cross and click Mapping>Include Row; the cell empties; to restore the green color, click Build>Check only

3.Click Build>Check Only; answer Yes to allow the conversion in spite of errors; then a validation report displays and the column A turns green in the valid rows.

 

Legend of column A:

 

* If the Database source and the Path # do not match, the message ARTICLE DATABASE NOT FOUND shows in place of Description.

If the article reference is not in the database source, ARTICLE  NOT FOUND shows in place of Description.

If the Path # is not defined, the message VIRTUAL ARTICLE DATABASE shows in place of Database source.

 

Whenever the column mapping is modified, the results of the check are deleted.

 

Build

To prevent some mapped columns from being inserted, uncheck the box Import field on the left. In addition, this exempts these columns from the check.

To create the database in QDV7:

1.Click Build>Check and Build; this opens the database selector (refer to How to Create & Manage the Databases)

2.Select a directory in the drop-down list

3.To create a new database, click New and enter a database name; it shows in the directory

4.Select the database and click Open; add/update/delete articles or replace the database, if it was existing.

 

It is up to you to restrain an update to the sets, or to also fill

▪the article list

▪the Excel file; For SQL Server set databases, the articles whose Path = 0 are marked with ‘Article database not found’ in the column whose ID is ArticlesDescription

If the Description is mapped to sets and articles, QDV7 Converter assigns the value to the set, whenever the row owns a set reference (see screen above).

 

To save the workbook and column mapping, click File>Save.

 

Additional Procedure to Build a Database with an Index

This applies to the upper level (sets).

The indexing is described in How to Index a Table (Tree Structure). To avoid showing the code in the treeview, check the 'Hide the code in tree' box either in the ribbon (Identify menu), or in the dialog box.

 

Two special columns are required:

▪the column that contains the roots of the key column; it must be mapped as Tree code

▪the column that contains the descriptors;  it must be mapped as Tree description

These columns can be located in a sheet apart from the other fields.

One-level example:

 

Two stages are required (1-2-3 and 4-5-6):

1.In addition, map these special columns as Tree code and Tree description

2.To specify the index key, check the desired item Build>Identify>Tree based on ...  

3.Check and build the new database; this creates the index, but not the articles

4.Delete the mapping of the two special columns ('Cancel mapping of this column')

5.If several levels exist in the tree-view, exclude the rows that do not describe articles  

6.Rebuild and select Open, then Update; this populates the database

 

If the field Tree contract code is mapped, it substitutes for the Tree code field (which is still required). This results in the index as: ‘Tree contract code’-[‘Tree description’].

The "The given key was absent from dictionary" message means that a value of the key is not defined.

Settings

To save the current file's settings, click Build>Save Settings and choose a name.

The settings file is a text file, whose extension is qdvconvert; it includes, in addition to the potential formulas:

1.After the template selection, the full path to the template

2.After the mapping, in addition, all the column mapping, including the check status of the box Import field and the potential extended fields, and also the status of the 'Hide code in tree' flag

3.After exclusion, in addition, the line number of the excluded rows.

 

To load settings:

1.Click Build>Load Settings and select a ".qdvconvert" file

2.Click Build>Mapping>Load Mapping and reselect the ".qdvconvert" file; if the workbook features several sheets requiring specific treatments, make the selection in the relevant dialog boxes; the column headings and the left flap populate accordingly

3.If the workbook features several sheets with some unchecked ones, to remediate this, click Build>Mapping>Copy to Sheets.

 

NOTE

The name atop and in the taskbar gets longer as you carry out the operations:

- After opening: -[<Sheet file path and name>.<extension> (Template:)] where the path is C:\Users\<user name>\AppData\Local\Temp\QDVTempFilesMain

- After template selection: -[<Sheet file path and name>.<extension> (Template: <template name>.grp)]

- After saving, the name by default is: -[<Sheet file path and name>.<extension> (Template: <template name>.grp) [With mappings]]