GISconnector for Excel - Quick Tutorial Format
×
Menu
Index
  • Lection B: The non-specific Excel format ‘General’

Lection B: The non-specific Excel format ‘General’


Assignment: Get to know the Excel format ‘General’ and understand the difference between the tolerant formatting of Excel tables and the distinct formatting of an attribute table in ArcGIS.
 
 
1. Create a new column in your Excel table while inserting the heading ‘Inhabitants_2011’ in cell E1. Now add the number of inhabitants for the districts of the German city Hamburg in 2011, which can be taken from the table below.
 
Districts
Inhabitants_2011
Altona
247,011
Eimsbuettel
242,757
Hamburg-Nord
275,349
Wandsbek
403,977
Bergedorf
118,828
Harburg
147,392
Hamburg-Mitte
271,382
 
 
2. Now click inside the different cells of the newly created column ‘Inhabitants_2011’. Examine the formatting of the cells. The whole column should be formatted as ‘General’. Change the format if this is not the case.
 
 

Information
The GISconnector always assumes that a column heading is a ‘Text’ and therefore converts it accordingly, if necessary. Hence the format in row 1 in column ‘Inhabitants_2011’ is irrelevant.
 

 
 
3. Switch to the tab ‘GISconnector’. Transfer your data to your connected Feature Class using the button ‘Transfer All Data’.
 
 
 
4. Your chosen format is not distinct. You will receive the following warning:
 
 

Information
Most of the times the format ‘General’ in Excel sets the format appropriately to the cell content. If you are only working in Excel this function will usually not cause problems. On the contrary, ArcGIS attribute tables have no dynamic formatting function. Every field (or column) has a distinct format. Therefore, the GISconnector must determine a certain format for a column if they are formatted as ‘General’ in Excel.

 
 
5. Continue using the format ‘General’, in other words confirm the warning while clicking ‘Yes’.
 
 
6. After transferring the data the GISconnector switches to ArcGIS. Examine the attribute table in your ArcMap project. Since the GISconnector message has only been a warning, the data has been transferred nevertheless.
 
 
 
7. Check the data type of the column ‘Inhabitants_2011’ in ArcGIS. Open the layer settings while double-clicking the layer ‘Districts’. Navigate to the tab ‘Fields’ and click the field ‘Inhabitants_2011’ in the left column. Afterwards you will see the details of the field in the right column. There you can see that the data type is ‘Text’. In this case the data is numbers though.
Close the layer settings afterwards.
 
 

Hint
Since the format ‘General’ is not distinct, ArcGIS is interpreting it as ‘Text’. Nevertheless, it can lead to compatibility problems in ArcGIS, if the data is numbers. Therefore, it is best if you always select a specific format yourself, in this case ‘Number’.

 
 
8. Switch to Excel. Copy the column E ‘Inhabitants_2011’ and insert it in column F. Change the heading to ‘Inhabitants_2011b’ and navigate to the area ‘Number’ in the tab ‘Home’. Format this column as ‘Number’. Delete the decimals using the button .
 
 
 
9. Switch to the tab ‘GISconnector’ and transfer all data to ArcGIS. You will receive the already known warning again. It concerns column E ‘Inhabitants_2011’ again since it is still formatted as ‘General’. Continue while clicking ‘Yes’. The GISconnector is transferring the data and switches to ArcGIS.
 
 
 
10. Compare the data type of the two columns ‘Inhabitants_2011’ and ‘Inhabitants_2011b’. Open the layer settings while double-clicking the layer ‘Districts’. Navigate to the tab ‘Fields’ and click the field ‘Inhabitants_2011’ or ‘Inhabitants_2011b’ resp. in the left column. In the field details, you will see that the new column ‘Inhabitants_2011b’ has the data type ‘Long’.
 
 

Information
‘Long’ stands for the field data type Long Integer, which is used for numerical values without decimals. Since you deleted all decimals beforehand, ‘Long’ is now the correct Number data type with which you can work in ArcGIS now. More information about ArcGIS field data types can be found on the Esri homepage.

 
 
11. To avoid reoccurring warnings close the layer settings and switch to Excel. Delete the complete column E while right-clicking ‘Delete cells’ and transfer your data to ArcGIS. Column E ‘Inhabitants_2011’ will be deleted in ArcGIS now, as well.
 
 
 
12. Transfer your data to ArcGIS. Examine the attribute table, column 'Inhabitants_2011' is deleted here as well now.
 
 
 
13. Save your Excel file and your MXD.