{"id":240,"date":"2016-04-05T15:32:43","date_gmt":"2016-04-05T07:32:43","guid":{"rendered":"https:\/\/eligiblestore.com\/blog\/?p=240"},"modified":"2020-06-30T21:43:59","modified_gmt":"2020-06-30T13:43:59","slug":"export-data-to-csv-from-sql-using-batch-file-my-workaround","status":"publish","type":"post","link":"https:\/\/eligiblestore.com\/blog\/2016\/04\/05\/export-data-to-csv-from-sql-using-batch-file-my-workaround\/","title":{"rendered":"Export data to CSV from SQL using batch file (My Workaround)"},"content":{"rendered":"

For this trick you will need to download sed (streams editor) for win32<\/a> more about sed here<\/a><\/p>\n

To generate data from SQL you can use SQLCMD, for example below:<\/p>\n

SQLCMD -S SERVER_NAME\\SERVER_INSTANCE -d DB_NAME -U DB_USER -P DB_PASSWORD -Q "SET NOCOUNT ON;SELECT * FROM DATABASE ORDER BY COLUMN" -s "," -o "D:\\YOUR_DIRECTORY\\OUTPUT\\OUTPUT_%AWSDT%_temp.csv"<\/pre>\n
In case you don’t know.<\/p>\n
"SELECT * FROM DATABASE ORDER BY COLUMN"<\/pre>\n
is the query that we can to get the record from<\/p>\n
SET NOCOUNT ON<\/pre>\n
is to omit message “xx rows affected” at the end<\/p>\n
-s ","<\/pre>\n
to use comma “,” as column separator<\/p>\n
and here’s where the magic happened.<\/p>\n
sed -r "s\/ +\\t\/\\t\/g" D:\\YOUR_DIRECTORY\\OUTPUT\\OUTPUT_%AWSDT%_temp.csv | sed -r "s\/\\t +\/\\t\/g" | sed -r "s\/(^ +| +$)\/\/g" | sed 2d &gt; D:\\YOUR_DIRECTORY\\OUTPUT\\OUTPUT_%AWSDT%.csv<\/pre>\n
This will clean up all the long spaces, remove the header separator
\n“————-,————–”
\nand it will gives the the clean look CSV file.<\/p>\n
Below are the full sample:<\/p>\n
@echo off\r\nD:\r\nCD D:\\YOUR_DIRECTORY\\\r\n\r\necho.\r\necho.Copy current format to temporary\r\nreg copy "HKCU\\Control Panel\\International" "HKCU\\Control Panel\\International-Temp" \/f\r\necho.\r\necho.Modify date format\r\nreg add "HKCU\\Control Panel\\International" \/v sShortDate \/d "dd\/MM\/yyy" \/f\r\n@REM reg query "HKCU\\Control Panel\\International" \/v sShortDate\r\nset LogDate=%date%\r\n\r\nset yyyy=\r\n\r\nset $tok=1-3\r\nfor \/f "tokens=1 delims=.:\/-, " %%u in ('date \/t') do set $d1=%%u\r\nif "%$d1:~0,1%" GTR "9" set $tok=2-4\r\nfor \/f "tokens=%$tok% delims=.:\/-, " %%u in ('date \/t') do (\r\nfor \/f "skip=1 tokens=2-4 delims=\/-,()." %%x in ('echo.^|date') do (\r\nset %%x=%%u\r\nset %%y=%%v\r\nset %%z=%%w\r\nset $d1=\r\nset $tok=))\r\n\r\nif "%yyyy%"=="" set yyyy=%yy%\r\nif \/I %yyyy% LSS 100 set \/A yyyy=2000 + 1%yyyy% - 100\r\n\r\nset CurDate=%mm%\/%dd%\/%yyyy%\r\nset dayCnt=%3\r\n\r\nif "%dayCnt%"=="" set dayCnt=3\r\n\r\nREM Substract your days here\r\nset \/A dd=1%dd% - 100 - %dayCnt%\r\nset \/A mm=1%mm% - 100\r\n\r\n:CHKDAY\r\nif \/I %dd% GTR 0 goto DONE\r\nset \/A mm=%mm% - 1\r\nif \/I %mm% GTR 0 goto ADJUSTDAY\r\nset \/A mm=12\r\nset \/A yyyy=%yyyy% - 1\r\n\r\n:ADJUSTDAY\r\nif %mm%==1 goto SET31\r\nif %mm%==2 goto LEAPCHK\r\nif %mm%==3 goto SET31\r\nif %mm%==4 goto SET30\r\nif %mm%==5 goto SET31\r\nif %mm%==6 goto SET30\r\nif %mm%==7 goto SET31\r\nif %mm%==8 goto SET31\r\nif %mm%==9 goto SET30\r\nif %mm%==10 goto SET31\r\nif %mm%==11 goto SET30\r\nREM ** Month 12 falls through\r\n\r\n:SET31\r\nset \/A dd=31 + %dd%\r\ngoto CHKDAY\r\n\r\n:SET30\r\nset \/A dd=30 + %dd%\r\ngoto CHKDAY\r\n\r\n:LEAPCHK\r\nset \/A tt=%yyyy% %% 4\r\nif not %tt%==0 goto SET28\r\nset \/A tt=%yyyy% %% 100\r\nif not %tt%==0 goto SET29\r\nset \/A tt=%yyyy% %% 400\r\nif %tt%==0 goto SET29\r\n\r\n:SET28\r\nset \/A dd=28 + %dd%\r\ngoto CHKDAY\r\n\r\n:SET29\r\nset \/A dd=29 + %dd%\r\ngoto CHKDAY\r\n\r\n:DONE\r\nif \/I %mm% LSS 10 set mm=0%mm%\r\nif \/I %dd% LSS 10 set dd=0%dd%\r\n\r\nREM Set IIS and AWS date variables\r\nset IISDT=%yyyy:~2,2%%mm%%dd%\r\nset AWSDT=%yyyy%%mm%%dd%\r\nrem set AWSDT=20150323\r\n\r\nSQLCMD -S SERVER_NAME\\SERVER_INSTANCE -d DB_NAME -U DB_USER -P DB_PASSWORD -Q "SET NOCOUNT ON;SELECT * FROM DATABASE ORDER BY COLUMN" -s "," -o "D:\\YOUR_DIRECTORY\\OUTPUT\\OUTPUT_%AWSDT%_temp.csv"\r\n\r\nsed -r "s\/ +\\t\/\\t\/g" D:\\YOUR_DIRECTORY\\OUTPUT\\OUTPUT_%AWSDT%_temp.csv | sed -r "s\/\\t +\/\\t\/g" | sed -r "s\/(^ +| +$)\/\/g" | sed 2d &gt; D:\\YOUR_DIRECTORY\\OUTPUT\\OUTPUT_%AWSDT%.csv\r\n\r\ndel D:\\YOUR_DIRECTORY\\OUTPUT\\OUTPUT_%AWSDT%_temp.csv\r\n<\/pre>\n
I hope you enjoy the tricks any questions let me know in comment section below.<\/p>\n","protected":false},"excerpt":{"rendered":"
For this trick you will need to download sed (streams editor) for win32 more about sed here To generate data from SQL you can use SQLCMD, for example below: SQLCMD -S SERVER_NAME\\SERVER_INSTANCE -d DB_NAME -U DB_USER -P DB_PASSWORD -Q "SET NOCOUNT ON;SELECT * FROM DATABASE ORDER BY COLUMN" -s "," -o "D:\\YOUR_DIRECTORY\\OUTPUT\\OUTPUT_%AWSDT%_temp.csv" In case you […]<\/p>\n","protected":false},"author":3,"featured_media":247,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[9],"tags":[],"class_list":["post-240","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-programming-database"],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8pg5H-3S","jetpack-related-posts":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/eligiblestore.com\/blog\/wp-content\/uploads\/2016\/04\/cmd.jpg?fit=500%2C500&ssl=1","_links":{"self":[{"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/posts\/240","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/comments?post=240"}],"version-history":[{"count":4,"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/posts\/240\/revisions"}],"predecessor-version":[{"id":1268,"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/posts\/240\/revisions\/1268"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/media\/247"}],"wp:attachment":[{"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/media?parent=240"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/categories?post=240"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/eligiblestore.com\/blog\/wp-json\/wp\/v2\/tags?post=240"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}