Converting SQL for embedded use within VBA

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’s 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.

$MyFile=get-content -Path "sql.txt"  
$outfile = "sqlNew.txt"
Remove-Item $outfile -ErrorAction SilentlyContinue
$str=$null;
$firstLine=$true;
for ($i=0; $i-lt $MyFile.Count; $i++)
{
if ($str.length -gt 80)
{
	if ($firstLine)
	{
		$str = '"' + $str + '" _'
		$firstLine=$false;
	}
	else
	{
		$str = '& "' + $str + '" _'
	}
	Add-Content $outfile "$($str)`n"
	$str=$null;
}
$nextLine = $MyFile[$i]
$nextLine = $nextLine.Replace("`t"," ");
$nextLine = $nextLine.Replace("  "," ");$nextLine = $nextLine.Replace("  "," ");$nextLine = $nextLine.Replace("  "," ");
$idx = $nextLine.indexof("--");
if ($idx -ge 0)
{
	$nextLine = $nextLine.Substring(0,$idx)
}

$str = $str + ' ' + $nextLine;

}

if ($firstLine)
{
	$str = '"' + $str + ' "'
}
else
{
	$str = '& "' + $str + ' "'
}
Add-Content $outfile "$($str)`n"
$str = $null;