{"id":832,"date":"2014-08-04T19:28:19","date_gmt":"2014-08-04T17:28:19","guid":{"rendered":"http:\/\/thomasdt.com\/?p=832"},"modified":"2014-08-04T19:28:19","modified_gmt":"2014-08-04T17:28:19","slug":"how-to-search-in-databases-spread-all-over-the-company","status":"publish","type":"post","link":"https:\/\/rationalk.ch\/blog\/how-to-search-in-databases-spread-all-over-the-company\/","title":{"rendered":"How to search in databases spread all over the company"},"content":{"rendered":"<h2>The\u00a0problem<\/h2>\n<p>Knowledge is spread all over the company and hidden inside excel files. During the development of new products it is crucial to have a state of the art of what has been already done to save money.<\/p>\n<h2>The context and some contraints<\/h2>\n<ul>\n<li>Engineering department<\/li>\n<li>Each engineer manages its own formatted list of product datas using excel\n<p><figure id=\"attachment_833\" aria-describedby=\"caption-attachment-833\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/i0.wp.com\/rationalk.ch\/blog\/wp-content\/uploads\/2014\/08\/2014-08-04-18_22_43-Microsoft-Excel.png?ssl=1\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-833\" src=\"https:\/\/i0.wp.com\/thomasdt.com\/wp-content\/uploads\/2014\/08\/2014-08-04-18_22_43-Microsoft-Excel-300x143.png?resize=300%2C143\" alt=\"Examples of lists\" width=\"300\" height=\"143\" srcset=\"https:\/\/i0.wp.com\/rationalk.ch\/blog\/wp-content\/uploads\/2014\/08\/2014-08-04-18_22_43-Microsoft-Excel.png?resize=300%2C143&amp;ssl=1 300w, https:\/\/i0.wp.com\/rationalk.ch\/blog\/wp-content\/uploads\/2014\/08\/2014-08-04-18_22_43-Microsoft-Excel.png?w=682&amp;ssl=1 682w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-833\" class=\"wp-caption-text\">Examples of lists<\/figcaption><\/figure><\/li>\n<li>The headers may have different names (example : list 1 refers to temperature as\u00a0 \u00ab\u00a0Max Temperature\u00a0\u00bb while list 2 refers as \u00ab\u00a0T\u00a0\u00bb)<\/li>\n<li>The values may be in expressed in different units (exemple : list 1 expresses the temperature in \u00b0C while list 2 in Kelvin)<\/li>\n<li>No extra work for the engineers<\/li>\n<\/ul>\n<h2>My solution<\/h2>\n<ol>\n<li>Using\u00a0a scheduled task, the different excel lists are uploaded from the windows file system onto the company server\n<p><figure id=\"attachment_834\" aria-describedby=\"caption-attachment-834\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/i0.wp.com\/rationalk.ch\/blog\/wp-content\/uploads\/2014\/08\/2014-08-04-18_04_25-C__Users_DM5212_Dropbox_6www_Blog_Crawler_ftpscript.txt-Notepad-Administrat.png?ssl=1\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-834\" src=\"https:\/\/i0.wp.com\/thomasdt.com\/wp-content\/uploads\/2014\/08\/2014-08-04-18_04_25-C__Users_DM5212_Dropbox_6www_Blog_Crawler_ftpscript.txt-Notepad%2B%2B-Administrat-300x64.png?resize=300%2C64\" alt=\"Scheduled Task\" width=\"300\" height=\"64\" \/><\/a><figcaption id=\"caption-attachment-834\" class=\"wp-caption-text\">Scheduled Task<\/figcaption><\/figure><\/li>\n<li>Using a server scheduled task (cron), the excel files are converted into sql format\n<figure id=\"attachment_835\" aria-describedby=\"caption-attachment-835\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/i0.wp.com\/rationalk.ch\/blog\/wp-content\/uploads\/2014\/08\/Screenshot-2014-08-04-19.12.23.png?ssl=1\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-835\" src=\"https:\/\/i0.wp.com\/thomasdt.com\/wp-content\/uploads\/2014\/08\/Screenshot-2014-08-04-19.12.23-300x56.png?resize=300%2C56\" alt=\"List 1 in sql format\" width=\"300\" height=\"56\" srcset=\"https:\/\/i0.wp.com\/rationalk.ch\/blog\/wp-content\/uploads\/2014\/08\/Screenshot-2014-08-04-19.12.23.png?resize=300%2C56&amp;ssl=1 300w, https:\/\/i0.wp.com\/rationalk.ch\/blog\/wp-content\/uploads\/2014\/08\/Screenshot-2014-08-04-19.12.23.png?w=517&amp;ssl=1 517w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-835\" class=\"wp-caption-text\">List 1 in sql format<\/figcaption><\/figure>\n<p><figure id=\"attachment_836\" aria-describedby=\"caption-attachment-836\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/i0.wp.com\/rationalk.ch\/blog\/wp-content\/uploads\/2014\/08\/Screenshot-2014-08-04-19.13.25.png?ssl=1\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-836\" src=\"https:\/\/i0.wp.com\/thomasdt.com\/wp-content\/uploads\/2014\/08\/Screenshot-2014-08-04-19.13.25-300x101.png?resize=300%2C101\" alt=\"List 2 in sql format\" width=\"300\" height=\"101\" srcset=\"https:\/\/i0.wp.com\/rationalk.ch\/blog\/wp-content\/uploads\/2014\/08\/Screenshot-2014-08-04-19.13.25.png?resize=300%2C101&amp;ssl=1 300w, https:\/\/i0.wp.com\/rationalk.ch\/blog\/wp-content\/uploads\/2014\/08\/Screenshot-2014-08-04-19.13.25.png?w=335&amp;ssl=1 335w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-836\" class=\"wp-caption-text\">List 2 in sql format<\/figcaption><\/figure><\/li>\n<li>A conversion sql database helps to uniform\u00a0the parameters designations and units<\/li>\n<li>The result is quite powerful\n<p><figure id=\"attachment_837\" aria-describedby=\"caption-attachment-837\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/i0.wp.com\/rationalk.ch\/blog\/wp-content\/uploads\/2014\/08\/Screenshot-2014-08-04-19.17.32.png?ssl=1\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-837\" src=\"https:\/\/i0.wp.com\/thomasdt.com\/wp-content\/uploads\/2014\/08\/Screenshot-2014-08-04-19.17.32-300x134.png?resize=300%2C134\" alt=\"Result from the master search\" width=\"300\" height=\"134\" \/><\/a><figcaption id=\"caption-attachment-837\" class=\"wp-caption-text\">Result from the master search<\/figcaption><\/figure><\/li>\n<\/ol>\n<h2>Conclusion<\/h2>\n<p>With this method, we authorise\u00a0the engineers to work\u00a0with their own excel files and still provide a solution to search among all the different files.<\/p>\n<h2>Future improvements<\/h2>\n<ul>\n<li>Make sure that there is an unique identifier for each excel lines so that any project can make reference to it.<\/li>\n<li>Auto update\u00a0the sql databases structure to match structure\u00a0changes in the excel files.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>The\u00a0problem Knowledge is spread all over the company and hidden inside excel files. During the development of new products it is crucial to have a state of the art of what has been already done to save money. The context and some contraints Engineering department Each engineer manages its own formatted list of product datas [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[2,5],"tags":[],"class_list":["post-832","post","type-post","status-publish","format-standard","hentry","category-dev","category-project-management"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/rationalk.ch\/blog\/wp-json\/wp\/v2\/posts\/832","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/rationalk.ch\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rationalk.ch\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/rationalk.ch\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/rationalk.ch\/blog\/wp-json\/wp\/v2\/comments?post=832"}],"version-history":[{"count":0,"href":"https:\/\/rationalk.ch\/blog\/wp-json\/wp\/v2\/posts\/832\/revisions"}],"wp:attachment":[{"href":"https:\/\/rationalk.ch\/blog\/wp-json\/wp\/v2\/media?parent=832"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rationalk.ch\/blog\/wp-json\/wp\/v2\/categories?post=832"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rationalk.ch\/blog\/wp-json\/wp\/v2\/tags?post=832"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}