VBAにSQLを埋め込むためのコードを生成するpythonコード

内容

• SQLコードを分かりやすい形でExcel VBAなどに埋め込むためのコードを生成
  • ADO(ActiveX Data Objects)での利用を想定
  • /* */コメントや--コメントは除去する仕様

コード

import re

# Use to remove -- comment
def remove_after_string(input_string, target_string):
    index = input_string.find(target_string)
    if index != -1:
        return input_string[:index]
    else:
        return input_string

path = input("Enter OpenFilePath(ex ./fileName.sql): ")

strSQL = "    strSQL = "  + " \"\" " + "\n"
with open(path, "r", encoding="Shift-JIS") as f:
# with open(path, "r", encoding="utf-8") as f:
    for line in f:
       s = line.replace("\n", "")
       s = remove_after_string(s, "--")
       strSQL = strSQL + "    strSQL = strSQL & " + "\"" + " " + s + " " + "\"" + "\n"

# Use to remove /* */ comment
pattern = re.compile(r'/\*.*?\*/', re.DOTALL)
strSQL =  re.sub(pattern, '', strSQL)
   
outputPath = "./" + path.split("/")[1].split(".")[0] + "SqlToVBA.txt"
with open(outputPath, "w") as f:
    f.write(strSQL)

使い方

• 上記コードをコピーして.pyファイルとして保存
• 同じ階層に対象となる.sqlファイルを配置

実行例

C:\Users\nakamura0493\Desktop> python .\SqlToVBA.py
Enter OpenFilePath(ex ./fileName.sql): ./sample.sql

対象ファイル(sample.sql)

SELECT EmployeeID,
       Name,   /*  */
       Salary, -- 
       CASE 
           WHEN Salary < 40000 THEN 'Low'
           WHEN Salary BETWEEN 40000 AND 80000 THEN 'Medium'
           ELSE 'High'
       END AS SalaryCategory
FROM Employees;

実行結果

    strSQL =  "" 
    strSQL = strSQL & " SELECT EmployeeID, "
    strSQL = strSQL & "        Name, "
    strSQL = strSQL & "        Salary, "
    strSQL = strSQL & "        CASE  "
    strSQL = strSQL & "            WHEN Salary < 40000 THEN 'Low' "
    strSQL = strSQL & "            WHEN Salary BETWEEN 40000 AND 80000 THEN 'Medium' "
    strSQL = strSQL & "            ELSE 'High' "
    strSQL = strSQL & "        END AS SalaryCategory "
    strSQL = strSQL & " FROM Employees; "