Posts

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;