{"id":550,"date":"2016-03-26T15:53:16","date_gmt":"2016-03-26T15:53:16","guid":{"rendered":"http:\/\/blog.tiran.info\/?p=550"},"modified":"2016-03-26T15:53:16","modified_gmt":"2016-03-26T15:53:16","slug":"chargement-de-donnees-au-format-excel-dans-une-table-oracle","status":"publish","type":"post","link":"https:\/\/blog.tiran.stream\/?p=550","title":{"rendered":"Chargement de donn\u00e9es d&rsquo;Excel vers une table Oracle"},"content":{"rendered":"<p style=\"text-align: justify;\">Il existe une multitude de mani\u00e8res de charger des donn\u00e9es issues d&rsquo;Excel dans une table. Les plus communes \u00e9tant sans doute SQL*Loader ou les tables externes. On a aussi \u00e9voqu\u00e9 pr\u00e9c\u00e9demment l&rsquo;<a href=\"http:\/\/blog.tiran.info\/pattern-matching-avec-oracle\">utilisation de SQLcl<\/a>.<br \/>\nLe probl\u00e8me de ces m\u00e9thodes r\u00e9side n\u00e9anmoins dans la conversion au format CSV\u00a0des donn\u00e9es \u00e0 importer. Celle-ci peut s&rsquo;av\u00e9rer complexe\u00a0si les donn\u00e9es contiennent des occurrences du s\u00e9parateur, des sauts de ligne etc&#8230;<\/p>\n<p style=\"text-align: justify;\"><a href=\"http:\/\/www.oracle.com\/technetwork\/developer-tools\/sql-developer\/overview\/index-097090.html\" target=\"_blank\">SQLDeveloper<\/a> dispose d&rsquo;un module d&rsquo;import qui permet de charger directement des donn\u00e9es \u00e0 partir d&rsquo;un fichier Excel. Il n&rsquo;y a pas besoin de r\u00e9aliser de conversion CSV pr\u00e9alable.<\/p>\n<p style=\"text-align: justify;\">Le module permet\u00a0aussi cr\u00e9er la table de destination&#8230;<\/p>\n<p style=\"text-align: justify;\">Dans le panneau de connexions, apr\u00e8s un clic-droit sur la rubrique \u00ab\u00a0Tables\u00a0\u00bb, on peut acc\u00e9der\u00a0au\u00a0menu contextuel \u00ab\u00a0Import Data\u00a0\u00bb :<\/p>\n<p><a href=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2016\/03\/ImportDataSqlDev1.png\" rel=\"attachment wp-att-522\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-522\" src=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2016\/03\/ImportDataSqlDev1.png\" alt=\"ImportDataSqlDev1\" width=\"298\" height=\"271\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>On s\u00e9lectionne ensuite\u00a0le fichier Excel \u00e0 partir duquel on souhaite importer les donn\u00e9es. La case \u00e0 cocher \u00ab\u00a0header\u00a0\u00bb permet d&rsquo;indiquer si une ent\u00eate est pr\u00e9sente ou pas.\u00a0On visualise les donn\u00e9es dans la partie basse:<\/p>\n<p><a href=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2016\/03\/ImportDataSqlDev2.png\" rel=\"attachment wp-att-523\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-523 size-full\" src=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2016\/03\/ImportDataSqlDev2.png\" alt=\"ImportDataSqlDev2\" width=\"900\" height=\"600\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>On pr\u00e9cise ensuite le type de m\u00e9thode \u00e0 utiliser (table externe, insertions conventionnelles&#8230;) et la table de destination. Si celle-ci n\u2019existe pas, elle sera cr\u00e9\u00e9e:<\/p>\n<p><a href=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2016\/03\/ImportDataSqlDev3.png\" rel=\"attachment wp-att-524\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-524 size-full\" src=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2016\/03\/ImportDataSqlDev3.png\" alt=\"ImportDataSqlDev3\" width=\"900\" height=\"600\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>On peut exclure certains champs de l&rsquo;import:<\/p>\n<p><a href=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2016\/03\/ImportDataSqlDev4.png\" rel=\"attachment wp-att-525\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-525 size-full\" src=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2016\/03\/ImportDataSqlDev4.png\" alt=\"ImportDataSqlDev4\" width=\"900\" height=\"600\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>L&rsquo;\u00e9cran suivant permet d&rsquo;indiquer le nom et le type des colonnes dans la table de destination:<\/p>\n<p><a href=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2016\/03\/ImportDataSqlDev5.png\" rel=\"attachment wp-att-526\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-526 size-full\" src=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2016\/03\/ImportDataSqlDev5.png\" alt=\"ImportDataSqlDev5\" width=\"900\" height=\"600\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>Un \u00e9cran de synth\u00e8se est finalement affich\u00e9 avant de proc\u00e9der \u00e0 l&rsquo;import:<\/p>\n<p><a href=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2016\/03\/ImportDataSqlDev6.png\" rel=\"attachment wp-att-527\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-527 size-full\" src=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2016\/03\/ImportDataSqlDev6.png\" alt=\"ImportDataSqlDev6\" width=\"900\" height=\"600\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>Cette m\u00e9thode est extr\u00eamement simple \u00e0 mettre en oeuvre. Le seul probl\u00e8me que j&rsquo;ai rencontr\u00e9 a \u00e9t\u00e9 l&rsquo;impossibilit\u00e9 de sp\u00e9cifier dans les \u00e9crans de pr\u00e9paration de l&rsquo;import\u00a0les param\u00e8tres NLS \u00e0 utiliser. C&rsquo;est probl\u00e9matique\u00a0si le s\u00e9parateur d\u00e9cimal ou le format des dates du fichier Excel ne correspondent pas aux param\u00e8tres de la session SQLDeveloper.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Il existe une multitude de mani\u00e8res de charger des donn\u00e9es issues d&rsquo;Excel dans une table. Les plus communes \u00e9tant sans<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"colormag_page_container_layout":"default_layout","colormag_page_sidebar_layout":"default_layout","footnotes":""},"categories":[6,10],"tags":[],"class_list":["post-550","post","type-post","status-publish","format-standard","hentry","category-oracle","category-preparation-des-donnees"],"_links":{"self":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/550","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=550"}],"version-history":[{"count":0,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/550\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=550"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=550"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=550"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}