Pull data from a source and write to file: Difference between revisions

From Starfish ETL
Jump to navigation Jump to search
Line 23: Line 23:
Set fso = CreateObject("Scripting.FileSystemObject")
Set fso = CreateObject("Scripting.FileSystemObject")
'Note the second parameter is using a Constant set above.  In this case, I want to append to the file.
'Note the second parameter is using a Constant set above.  In this case, I want to append to the file.
Set MyFile = fso.OpenTextFile("@@VAR:filename@@", 8, True)
Set MyFile = fso.OpenTextFile("@@VAR:filename@@", ForAppending, True)


'Write to the file.
'Write to the file.

Revision as of 16:01, 1 February 2019

With this map, I wanted to pull all accounts from a Sugar instance and dump a few fields into a pipe delineated file. In this map I used the same Sugar instance as both the Origin and Destination. There were no Stages created, so no action was taken against the Destination. There were two scripts used.

Before Operation

This script is a Variable/VBScript Action Type that runs "Once Before Conn". This script created the destination file, so I only wanted to to run once per job. I used a Variable Action Type so I could pass the file name and location to the next script.

Function ScriptedVariable
	'This script will overwrite any files with the same name.
	Set objFSO=CreateObject("Scripting.FileSystemObject")
	'I could make this a unique file name by adding the date/time stamp to the filename.
	fileName="test.csv"
	outFile="c:\temp\test.csv"
	Set objFile = objFSO.CreateTextFile(outFile,True)
	ScriptedVariable=outFile
End Function

After Operation

This script is a VBScript Procedure that runs "Repeat Each Row". It opens the file previously created for appending and dumps a few fields into the file.

Sub VBScriptProcedure
	Const ForReading = 1, ForWriting = 2, ForAppending = 8
	Dim fso, MyFile
	Set fso = CreateObject("Scripting.FileSystemObject")
	'Note the second parameter is using a Constant set above.  In this case, I want to append to the file.
	Set MyFile = fso.OpenTextFile("@@VAR:filename@@", ForAppending, True)

	'Write to the file.
	'These fields should be processed separately:
	'If the field contains a comma, then the field should be encapsulated by Double Quotes.
	'In addition, if a field contains a Double Quote, the Double Quote should be doubled: " => "".
	MyFile.WriteLine "@@ORG:id@@,@@ORG:name@@,@@ORG:billing_address_street@@,@@ORG:billing_address_city@@,@@ORG:billing_address_state@@,@@ORG:website@@"
	MyFile.Close
End Sub