Lection C: Excel tables with inconsistent column formats

 

 

1. Navigate to your Excel table. Format cell E3 as ‘Text’. The value is now shown left-aligned, which indicates the format ‘Text’. The format ‘Number’ on the other hand is aligned right, if not manually changed.

 

 

 

2. Switch to the GISconnector ribbon and transfer your data from Excel to ArcGIS using the button ‘Transfer All Data’. You will receive the shown error message.

 

 

ArcGIS attribute tables always are database tables. These have the same format in every cell of a field (or column). Because of that, the GISconnector can only transfer data to ArcGIS, when all cells inside a column (excluding the heading) of the connected Excel table have a consistent format. As soon as single cells inside a column have differing formats, the GISconnector cannot interpret the format precisely. Nevertheless, this must be possible if you want to connect the Excel table to your ArcGIS attribute table. Therefore, you will receive an according error message.

The error message will also occur if you use these functions in ArcGIS, because the GISconnector is not able to interpret the format of the Excel table there, as well. This behavior will be demonstrated in the next step.

 

 

3. Confirm the error by clicking ‘Ok’ (if not yet done) and switch to ArcGIS by using the button ‘Switch to ArcGIS’.

 

 

 

4. Now try to transfer data from ArcGIS to Excel while clicking ‘Transfer all data’. The already known error message will occur here as well.

 

 

 

5.  Click ‘Ok’ and switch back to Excel by clicking the button ‘Switch to Excel’ in order to correct the mistake.

 

 

 

6. Reset cell E3 to the format ‘Number’. Delete the decimals using the button 'Decrease Decimal'.

(If not, you will receive an error message again, because the data types are still different and therefore the column formatting is still inconsistent.)

 

 

With bigger amounts of data, it is impossible to examine every cell solely. Therefore, it is useful to check the format only in the first cell of a column (not the heading) and to reset it if necessary. You can leave the cell marked as it was and click ‘Format Painter’ in the ribbon ‘Home’. Afterwards you have to mark the rest of the cells or the whole column. The format has now been copied and is consistent in the whole column.

 

7. Transfer your data from Excel to ArcGIS again using the button ‘Transfer All Data’. No more error message will occur, because the data type of the Excel column is now formatted consistently.

All data have been transferred successfully, which you can see in the Log. You can open the Log while clicking the balloon tip in the system tray (lower right corner).

 

 

 

8. Save your Excel file and your MXD.

 

Even though Excel tables and database tables look similar, they differ in essential aspects. The differences are relevant when working with the GISconnector, because it establishes a connection between Excel and ArcGIS tables. The spreadsheet software offers the possibility to insert data in a free definable grid and to format them as desired and inconsistent and even change it subsequently. Inside a database, the data scheme respectively the formatting of the columns is consistent by default. The input of data that do not correspond with the defined format is impossible. Hence the data in a column are defined precisely. For the GISconnector to connect data between ArcGIS and Excel properly, it is recommended to define the formatting distinct, analogous to a database. It will also increase your Excel data quality. You will e.g. see immediately if you inserted a ‘Text’ inside a ‘Number’ field. If you use the format ‘General’ you will not realize things like this.