CodeSnippetCreateSQL

The VBScript and template file to create SQL queries to enter all data file in a directory into database. Could be useful again in future, for instance if we have to include new variables.

VBScript

Option Explicit

Dim objFSO, sFolderLocation, sStartFolder, objFolder
Dim objFile, colFiles
Dim bFirstLine
Dim sTemplateSQL, sOutputSQLFile
Dim sFind, sTemplateString, sResultOne, sResultAll, sResultOutput
Dim objInFile, objOutFile

Const ForReading = 1
Const ForWriting = 2

'Set locations
Set objFSO = CreateObject("Scripting.FileSystemObject")
sFolderLocation = "E:\PostgreSQL\"
sStartFolder = sFolderLocation & "Data\"
sTemplateSQL = sFolderLocation & "TemplateDataSQL.txt"
sOutputSQLFile = sFolderLocation & "OutputSQL.txt"

sFind = "<<Path>>"

'Delete output file if already exists
If objFSO.FileExists(sOutputSQLFile) Then
    objFSO.DeleteFile sOutputSQLFile
End If
'Create output file
objFSO.CreateTextFile sOutputSQLFile

'Read in template file
Set objInFile = objFSO.OpenTextFile ( sTemplateSQL, ForReading)
sTemplateString = objInFile.ReadAll( )

Set objFolder = objFSO.GetFolder(sStartFolder)

'Loop though files in folder
Set colFiles = objFolder.Files
bFirstLine = True

For Each objFile In colFiles

    'Replace with file path and add to string
    sResultOne = Replace( sTemplateString, sFind, sStartFolder & objFile.Name )

    'Different behaviour for first line
    If bFirstLine Then
        sResultAll = sResultOne
        bFirstLine = False
    Else
        sResultAll = sResultAll & vbCrLf & vbCrLf & sResultOne
    End If
Next

'Back slash should be front slashs for use with pgAdminIII
sResultOutput = Replace( sResultAll, "\", "/" )

'Output SQL to output fil
Set objOutFile = objFSO.OpenTextFile ( sOutputSQLFile, ForWriting )
objOutFile.Write sResultOutput
objOutFile.Close

Template file

COPY graphdata_new(startdate, "value", series_id, region_id)
FROM '<<Path>>'
WITH DELIMITER ','
CSV HEADER;