Pull data from a source and write to file

Pull data from a source and write to file

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
    • Related Articles

    • File System Connector

      File System Origin The File System Connector reads all files from a folder specified in the Directory field. The Username and Password fields are not used. Filter can be used to filter for certain file names/extensions. To search for all files with ...
    • Import Starfish Map file from a locally stored *.SPD file

      Import Starfish Map file from a locally stored *.SPD file Step Snapshot Go to project and select the Import view Select Browse to import the *.spd file Select *.spd file from directory folder. Once the import is complete, the project will be added to ...
    • StarfishETL security features

      StarfishETL security features     Compliance and data privacy ·       No data kept at rest on Starfish ETL iPaaS servers ·       Data in motion is encrypted using HTTPS Infrastructure protection ·       StarfishETL iPaaS servers hosted on AWS  ...
    • Interact with the local file system

      Read files from a folder Function ScriptedVariable Dim fso, folder, files, sFolder, res Set fso = CreateObject("Scripting.FileSystemObject") sFolder = "C:\Results" Set folder = fso.GetFolder(sFolder) Set files = folder.Files For each folderIdx In ...
    • Download File

      External Assemblies To download a file in a C# scripted function, you must first go to the .NET Global section (after you choose to create a global script as found HERE), and in the External Assemblies box, enter the ...