{"id":249,"date":"2016-06-22T21:09:13","date_gmt":"2016-06-22T14:09:13","guid":{"rendered":"http:\/\/www.oopsbox.com\/?p=249"},"modified":"2016-06-23T09:25:39","modified_gmt":"2016-06-23T02:25:39","slug":"dirty-lab-import-many-delimited-files-into-mysql","status":"publish","type":"post","link":"https:\/\/www.oopsbox.com\/?p=249","title":{"rendered":"Dirty Lab : Import many delimited files into MySQL"},"content":{"rendered":"<p>\u0e14\u0e39\u0e42\u0e04\u0e4a\u0e14\u0e40\u0e2d\u0e32\u0e2b\u0e25\u0e48\u0e30\u0e01\u0e31\u0e19\u0e40\u0e19\u0e4a\u0e2d\u0e30 \u0e21\u0e35\u0e04\u0e27\u0e32\u0e21\u0e02\u0e35\u0e49\u0e40\u0e01\u0e35\u0e22\u0e08\u0e15\u0e31\u0e49\u0e07\u0e41\u0e15\u0e48\u0e40\u0e2b\u0e47\u0e19\u0e08\u0e33\u0e19\u0e27\u0e19\u0e44\u0e1f\u0e25\u0e4c\u0e25\u0e30 (\u0e04\u0e37\u0e2d\u0e41\u0e1a\u0e1a\u0e40\u0e22\u0e2d\u0e30\u0e21\u0e32\u0e01\u0e01\u0e01\u0e01\u0e01\u0e01\u0e01) \u0e41\u0e15\u0e48\u0e08\u0e33\u0e40\u0e1b\u0e47\u0e19\u0e15\u0e49\u0e2d\u0e07\u0e43\u0e0a\u0e49\u0e01\u0e47\u0e40\u0e25\u0e22\u0e40\u0e01\u0e34\u0e14\u0e41\u0e25\u0e1b\u0e41\u0e1a\u0e1a\u0e14\u0e48\u0e27\u0e19 \u0e46<br \/>\n&#8211; \u0e43\u0e0a\u0e49\u0e42\u0e04\u0e23\u0e07\u0e2a\u0e23\u0e49\u0e32\u0e07\u0e02\u0e2d\u0e07 HDC<br \/>\n&#8211; \u0e44\u0e1f\u0e25\u0e4c\u0e17\u0e35\u0e48\u0e19\u0e33\u0e40\u0e02\u0e49\u0e32\u0e2d\u0e22\u0e39\u0e48\u0e43\u0e19\u0e23\u0e39\u0e1b\u0e41\u0e1a\u0e1a\u0e02\u0e2d\u0e07\u0e42\u0e04\u0e23\u0e07\u0e2a\u0e23\u0e49\u0e32\u0e07\u0e21\u0e32\u0e15\u0e23\u0e10\u0e32\u0e19\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25\u0e14\u0e49\u0e32\u0e19\u0e2a\u0e38\u0e02\u0e20\u0e32\u0e1e \u0e01\u0e23\u0e30\u0e17\u0e23\u0e27\u0e07\u0e2a\u0e32\u0e18\u0e32\u0e23\u0e13\u0e2a\u0e38\u0e02 (43 \u0e41\u0e1f\u0e49\u0e21)<br \/>\n&#8211; \u0e1b\u0e25\u0e49\u0e33 Servlet \u0e02\u0e2d\u0e07 HDC \u0e42\u0e14\u0e22\u0e44\u0e21\u0e48\u0e15\u0e34\u0e14\u0e15\u0e31\u0e49\u0e07\u0e23\u0e30\u0e1a\u0e1a\u0e19\u0e35\u0e48\u0e14\u0e39\u0e22\u0e32\u0e01 \u0e46 \u0e2d\u0e48\u0e30 \u0e23\u0e2d\u0e1b\u0e23\u0e36\u0e01\u0e29\u0e32 <a class=\"profileLink\" href=\"https:\/\/www.facebook.com\/phoubon.ict\" data-hovercard=\"\/ajax\/hovercard\/user.php?id=100002655181658\">Phoubon Ict<\/a><span class=\"text_exposed_show\"><br \/>\n&#8211; \u0e2d\u0e31\u0e19\u0e19\u0e35\u0e49\u0e0a\u0e48\u0e27\u0e07\u0e23\u0e2d\u0e44\u0e1f\u0e25\u0e4c\u0e19\u0e33\u0e40\u0e02\u0e49\u0e32<\/span><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"shell\">#!\/bin\/sh\r\n#Import delimited file to database;\r\n\r\nIMPORTEDFOLDER=\"imported\"\r\n\r\nfileCount=0\r\nfileName=\"\"\r\nfilePath=\"\"\r\n\r\nmkdir -p \"..\/${IMPORTEDFOLDER}\"\r\n\r\nfor zip in *.zip; do\r\n    zip_filename=\"${zip%%.*}\"\r\n    unzip \"${zip}\" -d \"${zip_filename}\"\r\n    mv -f \"${zip}\" \"..\/${IMPORTEDFOLDER}\"\r\n    \r\n    for file in $(find .\/ -name '*.txt' -or -name '*.TXT'); do\r\n        fileName=${file##*\/}\r\n        fileName=${fileName%.txt}\r\n        filePath=\"$(dirname $(readlink -f \"${file}\"))\/${file##*\/}\"\r\n        \r\n        #TODO: Extend to GNU Parallel\r\n        sh importdelimited.sh \"${filePath}\" \"${fileName}\" \r\n        \r\n        fileCount=$((fileCount+1))\r\n    done\r\n    \r\n    #rm -rf \"${zip_filename}\"\r\n    \r\ndone\r\n\r\necho \"${fileCount} files completed.\"<\/pre>\n<p>\u0e2a\u0e04\u0e23\u0e34\u0e1b\u0e2a\u0e33\u0e2b\u0e23\u0e31\u0e1a\u0e19\u0e33\u0e40\u0e02\u0e49\u0e32\u0e10\u0e32\u0e19\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25\u0e02\u0e2d\u0e07 MySQL<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"null\">#!\/bin\/sh\r\n#Import delimited file to database;\r\n#Usage : sh importdelimited.sh {Source} {Target}\r\n\r\nLOGFILE=\"importlog.log\"\r\nDB=\"hdc\"\r\nUSERDB=\"a1a1a1\"\r\nPASSDB=\"b1b1b1\"\r\nSQL=\"\"\r\n\r\nSQL=\"SET SESSION sql_mode=''; LOAD DATA LOCAL INFILE '$1' REPLACE INTO TABLE $2 FIELDS TERMINATED BY '|' LINES TERMINATED BY '\\r\\n' IGNORE 1 LINES;\"\r\necho \"$(date -u) $2:${SQL}\" &gt;&gt; \"..\/${LOGFILE}\" 2&gt;&amp;1\r\nmysql -u${USERDB} -p${PASSDB} ${DB} -e \"${SQL}\"\r\nrm -f $1<\/pre>\n<p>\u0e2a\u0e48\u0e27\u0e19\u0e43\u0e04\u0e23\u0e08\u0e30\u0e1b\u0e23\u0e31\u0e1a\u0e42\u0e04\u0e4a\u0e14\u0e43\u0e2b\u0e49\u0e14\u0e39\u0e40\u0e21\u0e1e\u0e02\u0e36\u0e49\u0e19\u0e01\u0e47\u0e15\u0e32\u0e21\u0e2a\u0e1a\u0e32\u0e22\u0e19\u0e30\u0e04\u0e23\u0e31\u0e1a \u0e16\u0e49\u0e32\u0e43\u0e2b\u0e49\u0e14\u0e35\u0e01\u0e47\u0e14\u0e34\u0e2a\u0e04\u0e31\u0e2a\u0e01\u0e31\u0e19\u0e15\u0e48\u0e2d\u0e01\u0e47\u0e44\u0e14\u0e49 \u0e40\u0e23\u0e32\u0e19\u0e39\u0e1b\u0e40\u0e0a\u0e25\u0e2a\u0e04\u0e23\u0e34\u0e1b\u0e17\u0e4c \u0e2d\u0e22\u0e48\u0e32\u0e07\u0e2d\u0e37\u0e48\u0e19\u0e01\u0e47\u0e19\u0e39\u0e1b (\u0e14\u0e31\u0e01)<\/p>\n<div id=\"s-share-buttons\" class=\"\"><div class=\"s-single-share\">\n\t\t\t\t\t\t\t\t\t\t\t\t<div class=\"fb-share-button\" data-href=\"https:\/\/www.oopsbox.com\/?p=249\" data-type=\"button_count\"><\/div><\/div><div class=\"s-single-share\"><a href=\"https:\/\/twitter.com\/share\" class=\"twitter-share-button\"><\/a>\n\t\t\t\t\t\t\t\t\t\t\t\t<\/div><div class=\"s-single-share\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t<div class=\"g-plusone\" data-size=\"medium\"><\/div>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div><div class=\"s-single-share\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t<a href=\"\/\/www.pinterest.com\/pin\/create\/button\/\" data-pin-do=\"buttonBookmark\"  data-pin-color=\"red\"><img decoding=\"async\" src=\"\/\/assets.pinterest.com\/images\/pidgets\/pinit_fg_en_rect_red_20.png\" \/><\/a>\n\t\t\t\t\t\t\t\t\t\t\t\t\t<script type=\"text\/javascript\" async defer src=\"\/\/assets.pinterest.com\/js\/pinit.js\"><\/script>\n\t\t\t\t\t\t\t\t\t\t\t\t<\/div><div class=\"s-single-share\">\n\t\t\t\t\t\t\t\t\t\t\t\t<a href=\"mailto:?Subject=Dirty%20Lab%20:%20Import%20many%20delimited%20files%20into%20MySQL&Body=Here%20is%20the%20link%20to%20the%20article:%20https:\/\/www.oopsbox.com\/?p=249\" title=\"Email\" class=\"s3-email\"><img decoding=\"async\" src=\"https:\/\/www.oopsbox.com\/wp-content\/plugins\/simple-social-share\/includes\/..\/images\/share-email.png\"><\/a>\n\t\t\t\t\t\t\t\t\t\t\t<\/div><\/div>","protected":false},"excerpt":{"rendered":"<p>\u0e14\u0e39\u0e42\u0e04\u0e4a\u0e14\u0e40\u0e2d\u0e32\u0e2b\u0e25\u0e48\u0e30\u0e01\u0e31\u0e19\u0e40\u0e19\u0e4a\u0e2d\u0e30 \u0e21\u0e35\u0e04\u0e27\u0e32\u0e21\u0e02\u0e35\u0e49\u0e40\u0e01\u0e35\u0e22\u0e08\u0e15\u0e31\u0e49\u0e07\u0e41\u0e15\u0e48\u0e40\u0e2b\u0e47\u0e19\u0e08\u0e33\u0e19\u0e27\u0e19\u0e44\u0e1f\u0e25\u0e4c\u0e25\u0e30 (\u0e04\u0e37\u0e2d\u0e41\u0e1a\u0e1a\u0e40\u0e22\u0e2d\u0e30\u0e21\u0e32\u0e01\u0e01\u0e01\u0e01\u0e01\u0e01\u0e01) \u0e41\u0e15\u0e48\u0e08\u0e33\u0e40\u0e1b\u0e47\u0e19\u0e15\u0e49\u0e2d\u0e07\u0e43\u0e0a\u0e49\u0e01\u0e47\u0e40\u0e25\u0e22\u0e40\u0e01\u0e34\u0e14\u0e41\u0e25\u0e1b\u0e41\u0e1a\u0e1a\u0e14\u0e48\u0e27\u0e19 \u0e46 <\/p>\n","protected":false},"author":1,"featured_media":250,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[6],"tags":[12,10],"class_list":["post-249","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-lab","tag-mysql","tag-shell-script","entry"],"views":2934,"_links":{"self":[{"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/posts\/249","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=249"}],"version-history":[{"count":5,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/posts\/249\/revisions"}],"predecessor-version":[{"id":255,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/posts\/249\/revisions\/255"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=\/wp\/v2\/media\/250"}],"wp:attachment":[{"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=249"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=249"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.oopsbox.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=249"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}