scriptrunner: Read csv file to insert to mssql table

uvo3 main public name

Does anybody know how to read csv file and insert data to mssql table using scriptrunner script in job

I've a csv file test.csv with structure like below:
"first_column","second_column"
"first_column_value","second_column_value"
I need to read this file and insert data to mssql table called DEST by scriptrunner script

Ram Kumar Aravindakshan _Adaptavist_

Hi @uvo3 main public name

For your requirement, I suggest using the ScriptRunner console.

Below is a sample working code for your reference:-

import groovy.sql.Sql
import java.sql.Driver

def props = new Properties()
props.setProperty('user', 'root')
props.setProperty('password', 'qwerty')

def driver = Class.forName('com.mysql.jdbc.Driver').newInstance() as Driver
def conn = driver.connect('jdbc:mysql://localhost:3306/MOCK', props)

def sql = new Sql(conn)

/**
 * Path to the CSV file
 */
def file = new File('/home/ram/Downloads/content.csv')

def list = [] as ArrayList<Map>

def insertions = [] as List<String>

/**
 * Extract the value from the CSV file and add it to a List of Maps
 */
file.eachLine {
 def column = it.split(',')
 def mapInput = [:] as Map
 mapInput.put('First_Column', column[0])
 mapInput.put('Second_Column', column[1])
 list.add(mapInput)
}

try {
 list.each {
 sql.execute("""
 INSERT INTO example 
 (First_Column, Second_Column) 
 values (${it.values().first()}, ${it.values().last()})
 """)
 }
} finally {
 sql.close()
 conn.close()
}

Please note that the sample code above is not 100% exact to your environment. Hence, you will need to make the required modifications.

Below is a screenshot of the configuration for your reference:-

Below is a copy of the sample csv file I have tested with:-

Sample 1	Input 1
Sample 2	Input 2

And below is the output in the database:-

I hope this helps to solve your question. :-)

Thank you and Kind regards,
Ram