Loading data from Excel

 Print Top page

The application task «Loading data from Excel» is intended for converting Excel files (xls,xlsx) into DBF files with linking data from a file with map objects, as well as transferring values from Excel file cells to object semantics.

 

xls2dbf_en

 

Dialog of the application task «Loading data from Excel»

 

Main panel

 

The main panel contains the following buttons:

button_exit – exit;

button_help2 – help;

button_xls2dbf – execute.

 

File save directory

 

In this panel, the folder is selected where the DBF files will be saved.

 

Options

 

This panel contains the checkboxes «Convert to DBF file» and «Transfer to object semantics», as well as a drop-down list of encoding.

If the «Convert to DBF file» checkbox is checked, the Excel file will be converted to a DBF file.

If the «Transfer to object semantics» checkbox is checked, then the values ​​from the cells of the Excel file will be transferred to the corresponding map objects semantics.

If both checkboxes are checked, both the conversion and the filling of semantics will be performed.

The drop-down list of encoding is designed to select the encoding of the DBF file. Data in a DBF file can be written in ANSI and UTF-8 encoding.

 

List of Excel files

 

The list of Excel files contains information about Excel files, data from which will be transferred to the DBF file and object semantics.

The «Add» button adds Excel files to the list, which are selected in the standard file selection dialog.

The «Delete button deletes the selected Excel file from the list.

 

The Excel file list contains the following fields:

 

1. Number – file number in the list.

2. Excel file - the name of the Excel file.

3. DBF file - the name of the DBF file. The value of this field can be changed.

4. Map - a map from which objects will be taken. If more than one map is open, the value of this field can be changed in the drop-down list.

5. Excel column - the header of the Excel file column, the values of which will be used for linking with map objects. The value of this field can be changed in the drop-down list.

6. Map semantics - map semantics, the values ​​of which will be used for linking with data from the Excel file. The value of this field can be changed in the semantics selection dialog.

 

It is assumed that each line of the Excel file corresponds to a map object. For each line, the program finds a map object, the value of the selected semantics of which matches the value of the selected column of the Excel file.

When creating a DBF file, not only the columns of the Excel file will be transferred to it, but the following columns will also be added:

 

1. LATITUDE - the latitude of the object in the coordinate system of the map.

2. LONGITUDE – longitude of the objectin the coordinate system of the map.

3. LINKSHEET - the name of the map.

4. LINKOBJECT– object number.

 

If the «Transfer to object semantics» checkbox is checked, then the values of the columns of the Excel file whose headers match the keys of the object semantics will be written into the semantics of the map object.

If the name of the column selected for linking matches the semantics key, then the appropriate semantics is automatically selected in the «Map semantics» field.

 

Example

 

For example, we use the Excel spreadsheet «Buildings»

 

xls2dbf_table

 

Excel spreadsheet «Buildings»

 

When converting, the corresponding object will be found for each row, if there is one on the map. For the first row, not counting the .

 

xls2dbf_obj

 

Map object «Fire-resistant residential building»

 

This particular object was found, because the «HOUSE_NUMB» column and the «HOUSE NUMBERS» semantics were used for linking, as shown in the Dialog of the application task «Loading data from Excel», and the value of the «HOUSE_NUMB» column is 4a, that is, the value of the «HOUSE NUMBERS» semantics of this particular map object.

If the «Transfer ​​to object semantics» checkbox is checked, then the values ​​of the «FLOOR_NUMB», «PDK», and «STATE_3» columns will be transferred into the corresponding object semantics. The value of the «MATERIAL» column will not be transfered, since the object does not have semantics with such a key. The semantics of «GENPL_NUMB» will not be updated for the object, since there is no column with such a header in the Excel file.

Into columns the LATITUDE and LONGITUDE the object coordinates in YYY°MM'CC.CC format will be written down, into LINKSHEET column - the map name «Quarter», and into LINKOBJECT column - number of a map object, for the given object the number is equal 21.

 

For the remaining rows of the Excel file, the same actions will be done.