CSV Import Using CSVUTIL utility

In my previous post, we've seen how to import an individual CSV to insert/update/delete records of one table. But when we need to upload data into several tables and doing it one by one can be very very time consuming. Luckily, OTM gives us an utility with which we can import more than one CSV file.

Let’s take a scenario of creating a CONTACT and assign it to an existing contact group.

1. Prepare a CSV file for CONTACT Table

2. Prepare a CSV for CONTACT_GROUP Table

3. Open a notepad and write the below commands

-dataFileName CONTACT.CSV -command i

-dataFileName CONTACT_GROUP.CSV -command i

4. Save the file as csvutil.ctl

List the CSV files in the order they should be uploaded.

In this case, first Contact needs to be created & then assigned to a group.

5. Create a zip file with 3 files (CONTACT.CSV, CONTACT_GROUP.CSV, csvutil.ctl)

6. Navigate to Business Process Automation -> Integration -> Integration manager -> Upload a CSV/XML Transmission

7. Upload the zip file

8. If there are no errors OTM returns a log file.

CSV Export from OTM

There will be several occasions when you might have to export the data from OTM.

Look at the steps to follow for exporting CSV Data.

1.Navigate to Business Process Automation -> Data Export -> CSV Export

2.OTM asks what you want to export

exportWhat	You can select either exportTable – One table at a time exportTableSet – All tables in a table set exportQueryResults – All records in a SQL Query
outputDestination	You can export to Browser A remote OTM instance A file on your OTM Server
runInBackground	If you select ‘Yes’, this process will run in background and then output will be emailed to you. For that in 2nd step you need to provide your email address and SMTP host.
useSelectList	If you select ‘Yes’, you will be able to select specific records which you want to export.

3. Click on Run Button

4. Assuming that you selected ‘exportTable’, you can select a table from the drop down list.

5. Optionally you can write a condition. [No Need to use ‘where’; For eg: domain_name = ‘ABC’. ]

6. Click on Run & Copy the records into Notepad which are available in comma separated format.

7. Save the notepad with CSV extension.