How to read csv file in script runner ?

[Pravin]

Hi Team ,

I  need to read .csv file which present in my local machine and create ticket based on that 

can you please give idea  how can i implement this through script runner or any other way 

Thanks in advance .

[Peter-Dave Sheehan]

When I need to read a csv for anything, I do it like this:

import com.opencsv.CSVReader
@Grapes(
@Grab(group='com.opencsv', module='opencsv', version='4.2')
)
def filePath = '/path/to/file/test.csv'
def reader = new CSVReader(new FileReader(new File(filePath)))
def data= reader.collect { it }.with { rows ->
    def header = rows.head()
    def dataRows = rows.tail()

    dataRows.collect { row ->
        [header, row].transpose().collectEntries()
    }
}

data.each{row->
    // access any data from the row by the name of the header
    // for example, row['colHeader'] or row.colHeader
}

[Pravin]

Hi @Peter-Dave Sheehan ,

Thanks for getting back .

can i put this csv file in my local machine because i d not have server access 

[Peter-Dave Sheehan]

The file has to be available at the file system level.

So if you don't have access to that, an option is to put your csv in a google sheet.

Then in sheets, go to file/publish to the web. Follow the instructions and get the URL for accessing the sheet as a csv.

Then in scriptrunner you can access it with

import groovyx.net.http.ContentType
import groovyx.net.http.HTTPBuilder
import groovyx.net.http.Method
import com.opencsv.CSVReader
@Grapes(
  @Grab(group='com.opencsv', module='opencsv', version='4.2')
)
def url = 'url for your google sheet csv'
def httpBuilder = new HTTPBuilder(url)
def csvReader
def data
httpBuilder.request(Method.GET, ContentType.TEXT) {
   response.success = {resp, text ->
      csvReader = new CSVReader(text).readAll()
   }
   response.failure = {resp, text ->
      log.error "Unable to read csv from \n url: $url \nError: $text"
   }
}
if(csvReader) {
   data = csvReader.collect { it }.with { rows ->
      def header = rows.head()
      def dataRows = rows.tail()

      dataRows.collect { row ->
        [header, row].transpose().collectEntries()
      }
   }
}

[Pravin]

Hi @Peter-Dave Sheehan ,

Thanks again for your reply but unfortunately getting below error while running this code in script runner 

The script could not be compiled: <pre>org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: General error during conversion: Error grabbing Grapes -- [unresolved dependency: com.opencsv#opencsv;4.2: not found] java.lang.RuntimeException: Error grabbing Grapes -- [unresolved

[Peter-Dave Sheehan]

The @grab line requires access to the internet. It will actually download some files for you and store them in a directory owned by the user that jira is running the application.

On linux, it would be something like /home/jira/.groovy/com.opencsv etc

Those files are downloaded from https://search.maven.org/artifact/com.opencsv/opencsv/4.2/jar

I've found that sometimes, the first time I try @grab a new package, I get an error, but the next time it works.

So I suggest to try it a couple more time and if it still doesn't work, then I would check with you system admin for what might be preventing those files to be reached.

[Pravin]

Hi Peter 

I can only see the grapes folder under .groovy .

Can we do it by attaching csv file to jira issue and read it through the script ?

[Peter-Dave Sheehan]

Yeah it's probably possible to get the csv file from the issue.

But that won't help with loading the CSVReader class.

That means you will have to code your own parser.

[Pravin]

Thanks peter .

i will check if i can put this open csv file in server location and check , as it is not downloading  this file directly .

[Pravin]

Hi @Peter-Dave Sheehan 

I have downloaded open csv.jar and added on server under path 

/home/jira/.grrovy/com.opencsv/  but still getting the same error .(unresolved dependency: com.opencsv#opencsv;4.2: not found])

do I need to keep it any another location in order to compile the code ?

Thanks ,

Pravin 

[Peter-Dave Sheehan]

Let's try to break it down with a simpler script ...

Can you try this and get the full result (the full stack trace if you get an error)... let's see if we can find out what's happening

@Grapes(
  @Grab(group='com.opencsv', module='opencsv', version='4.2')
)
import com.opencsv.CSVReader
String csv = "a,b,c"
new CSVReader(new StringReader(csv)).readAll()

[Sunny Kanade]

@Peter-Dave Sheehan : Thanks, works for me!