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_ · Accepted solution]

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

[uvo3 main public name]

Hello,
Thanks for detailed solution.
It is works perfectly