Pull data from a source and write to file

From Starfish ETL
Jump to navigation Jump to search

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.
	Set objFile = objFSO.CreateTextFile(outFile,True)
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@@"
End Sub