{"id":8379,"date":"2019-03-25T00:20:44","date_gmt":"2019-03-24T23:20:44","guid":{"rendered":"http:\/\/www.sigterritoires.fr\/?p=8379"},"modified":"2019-03-26T09:53:12","modified_gmt":"2019-03-26T08:53:12","slug":"excel-tables-with-model-builder","status":"publish","type":"post","link":"https:\/\/www.sigterritoires.fr\/index.php\/en\/excel-tables-with-model-builder\/","title":{"rendered":"Excel tables with Model Builder"},"content":{"rendered":"\n

Although this article is, primarily, aimed at novice\nusers of Model Builder, it can be helpful for more advanced users since, even\nthough, the procedure may seem simple at first, has a trap. <\/p>\n\n\n\n

In our example, we will use an Excel table downloaded from\nINSEE, with unemployment data by municipality from 2001 to 2012 ( download\nlink of the table<\/a> , link\nof the download page<\/a> ). <\/p>\n\n\n\n

In addition to the Excel table, we will use the IGN Geofla Municipalities\nlayer (link of the download page). <\/p>\n\n\n\n\n\n\n\n

We want to create a points layer with unemployment data.\nTo this end,  we will\nuse as information, in the Excel table, the INSEE town code (text field), and\nthe same code is found in the GeoFla Municipalities layer. In the\nprevious table, we find the XY coordinates of the municipalities centroid.\n<\/p>\n\n\n\n

Therefore we will make a joint between the Excel table and\nthe Municipalities table based on the INSEE code, then, we will create an XY\nevent layer using the coordinates of the municipalities centroids.\nWe will save everything in a new feature class that will be\nable to use directly later. \n\nThe central tool of this\nmodel is the joint tool:   \n\n\n\n<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Followed by the creation of a points layer with the XY\ncoordinates. <\/p>\n\n\n\n

If you are new to Model Builder, you will try, in vain, to\njoin your Excel table and your municipalities table with the Joint tool.\n<\/p>\n\n\n\n

To succeed, the first clue is given by the title of the entries\nin the tool. Always read these headings\ncarefully as they will always save you time!
\nLet’s\nstart with the second heading: \u00ab\u00a0Joint Table\u00a0\u00bb. You\nwant to attach your data to the attribute table of the Municipalities layer.\nYou will not have any problem.
\nNow\nlet’s look at the first heading: \u00ab\u00a0Layer name or tabular view\u00a0\u00bb.\nThere it goes. A layer necessarily implies an\nexisting geometry (point, line, polygon). Your\nExcel table is not a geographic layer. It is\nnot a table in the sense of ArcGis (dbf of a shape, table of a geodatabase,\n…). And a \u00ab\u00a0Tabular View\u00a0\u00bb, what is it? <\/p>\n\n\n\n

A tabular view is an interface between the file on your disk\nand the ArcGis tools. You cannot access data directly\nfrom external files, you have to go through a view, an interface.\nThis is what ArcMap does when you load an Excel table or a\ntext file, it creates a view making it available to you in the legend window.\n
\nIt\nis this \u00ab\u00a0View\u00a0\u00bb that matches the ArcMap table field name to an Excel\ncolumn in your sheet.
\nIf\nyou do not have a view, ArcMap cannot use Excel columns. \n\nTherefore, you need a tabular\nview and you only have one Excel table at the moment. You need a tool to solve this\nproblem and this tool is \u201cGenerate tabular view<\/strong> \u00a0\u00bb which you will\nfind in the toolbox \u00a0\u00bb Tools of Data Management<\/strong> \u00a0\u00bb -> \u00a0\u00bb Layers\nand tabular views<\/strong> \u00ab\u00a0.  \n\n\n\n<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The trap is here!<\/strong>\n\n\nNow we can configure the joint\nof the two data sources:  \n\n\n\n<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

For the joints, pay attention to the order of the\nentries, whether in Model Builder or in ArcMap. The\nfirst entry is the table to which you want to add the attributes of the second.\n<\/p>\n\n\n\n

If you now run the model you will have the unpleasant\nsurprise that the model cannot succeed because an error message tells you that\nthe input table does not have an OID (internal identifier). Even\nif in interactive mode you can apply a joint to an Excel table, you cannot with\nthe geoprocessor tools. <\/p>\n\n\n\n

To solve this problem, you must import the Excel table into\nan ArcGis table. \n\nYou will use the Excel to\nTable<\/strong> command in the Data Conversion<\/strong> -> Excel<\/strong> toolbox .  \n\n\n\n<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Model continuation<\/strong>\n\n\nNow it remains to configure\nthe generation of points (XY events):  \n\n\n\n<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

But, this is not the end.<\/p>\n\n\n\n

The events layer exists only within the current\ndocument and, moreover, it does not have an internal identifier (OID).\nThis lack of identifier seriously limits the possibilities of\nworking on the layer. So you have to create a real\ndata layer, by registering this \u00ab\u00a0events\u00a0\u00bb layer in a geodatabase (or a\nshape file). \n\nWe will use the \u00a0\u00bb Feature\nclass to feature class<\/strong> \u00a0\u00bb tool from the toolbox \u00a0\u00bb Conversion\ntools<\/strong> \u00a0\u00bb -> Towards a geodatabase<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

We have finished building the flow model, still\nremains to define the model parameters for its execution: <\/p>\n\n\n\n

For input data, right-click the blue ovals and select Model\nParameter<\/strong>
\nFor\nthe output layer, do the same. <\/p>\n\n\n\n

For fields to be used as X and Y, right-click the Generate\nXY Event Layer<\/strong> -> Generate Variable<\/strong> -> Start Variable<\/strong>\ntool and select X<\/em> Field<\/em> and Y Field<\/em> .
\nRight-click\non the two blue ellipses and set them as model parameters. \n\nWe obtain the following model:  \n\n\n\n<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\nIn\norder to use it as toolbox tool, rename the elements that will be presented as\nparameters with explicit texts: \n\n\n\n<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\nYou\ncan save the template and run it from the toolbox:  \n\n\n\n<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\nThe\nresult of the model execution is as follows: \n\n\n\n<\/p>\n\n\n\n

\"\"<\/figure>\n","protected":false},"excerpt":{"rendered":"

Although this article is, primarily, aimed at novice users of Model Builder, it can be helpful for more advanced users since, even though, the procedure may seem simple at first, has a trap. In our example,…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"give_campaign_id":0,"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"sfsi_plus_gutenberg_text_before_share":"","sfsi_plus_gutenberg_show_text_before_share":"","sfsi_plus_gutenberg_icon_type":"","sfsi_plus_gutenberg_icon_alignemt":"","sfsi_plus_gutenburg_max_per_row":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1260],"tags":[],"class_list":["post-8379","post","type-post","status-publish","format-standard","hentry","category-non-classe-en"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p6XU0A-2b9","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/8379","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/comments?post=8379"}],"version-history":[{"count":0,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/8379\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/media?parent=8379"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/categories?post=8379"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/tags?post=8379"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}