After creating and testing SQL to embed within a VB or VBA application, it needs to be added to a VB project in usable strings. How to convert your SQL easily without introducing errors? Here\u2019s a PowerShell script that takes in a SQL file with a header (SQL) and condenses it into 80+ character strings for copying into your VB code.<\/p>\n
$MyFile=get-content -Path \"sql.txt\" \r\n$outfile = \"sqlNew.txt\"\r\nRemove-Item $outfile -ErrorAction SilentlyContinue\r\n$str=$null;\r\n$firstLine=$true;\r\nfor ($i=0; $i-lt $MyFile.Count; $i++)\r\n{\r\nif ($str.length -gt 80)\r\n{\r\n if ($firstLine)\r\n {\r\n $str = '\"' + $str + '\" _'\r\n $firstLine=$false;\r\n }\r\n else\r\n {\r\n $str = '& \"' + $str + '\" _'\r\n }\r\n Add-Content $outfile \"$($str)`n\"\r\n $str=$null;\r\n}\r\n$nextLine = $MyFile[$i]\r\n$nextLine = $nextLine.Replace(\"`t\",\" \");\r\n$nextLine = $nextLine.Replace(\" \",\" \");$nextLine = $nextLine.Replace(\" \",\" \");$nextLine = $nextLine.Replace(\" \",\" \");\r\n$idx = $nextLine.indexof(\"--\");\r\nif ($idx -ge 0)\r\n{\r\n $nextLine = $nextLine.Substring(0,$idx)\r\n}\r\n \r\n$str = $str + ' ' + $nextLine;\r\n \r\n}\r\n \r\nif ($firstLine)\r\n{\r\n $str = '\"' + $str + ' \"'\r\n}\r\nelse\r\n{\r\n $str = '& \"' + $str + ' \"'\r\n}\r\nAdd-Content $outfile \"$($str)`n\"\r\n$str = $null;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"Converting SQL for embedded use within VBA After creating and testing SQL to embed within a VB or VBA application, it needs to be added to a VB project in usable strings. How to convert your SQL easily without introducing errors? Here\u2019s a PowerShell script that takes in a SQL file with a header (SQL) […]<\/p>\n","protected":false},"author":2,"featured_media":1326,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[14,35],"tags":[],"class_list":["post-1323","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sharepoint-development-solutions","category-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1323","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/comments?post=1323"}],"version-history":[{"count":5,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1323\/revisions"}],"predecessor-version":[{"id":3555,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1323\/revisions\/3555"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media\/1326"}],"wp:attachment":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media?parent=1323"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/categories?post=1323"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/tags?post=1323"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}