Groovy load .csv files
Asked Answered
W

6

41

How to read and import .csv file in groovy on grails. I have .csv file with data and
need to import in to db using user interface .

Willettewilley answered 12/4, 2010 at 10:6 Comment(1)
a lot of csv files from europe seem to be coming with semicolons instead of commas, are any of these answers below a globally acceptable way of loading .csv files? probably because europe already uses the comma for their decimal points.Poucher
A
66

There are as always different possibilities to work with CSV files in Groovy.

As Groovy is fully interoperable with Java, you can use one of the existing CSV libararies, e.g. OpenCSV.

Depending on the complexity of the CSV file you are using, you can also use the standard file/string handling possibilities of Groovy:

def sql = Sql.newInstance("jdbc:mysql://localhost:3306/mydb",
  "user", "pswd", "com.mysql.jdbc.Driver")
def people = sql.dataSet("PERSON")
new File("users.csv").splitEachLine(",") {fields ->
  people.add(
    first_name: fields[0],
    last_name: fields[1],
    email: fields[2]
  )
}

EDIT: Kelly Robinson just wrote a nice blog post about the different possibilities that are available to work with CSV files in Groovy.

EDIT #2: As Leonard Axelsson recently released version 1.0 of his GroovyCVS library, I thought I should definitely add this to the list of options.

Anaplastic answered 12/4, 2010 at 14:54 Comment(1)
I've written a groovified wrapper around OpenCSV which is called GroovyCSV and makes working with csv in Groovy a lot easier and idiomatic. It's available at xlson.com/groovycsvSclerotomy
A
14

Using xlson's GroovyCSV:

@Grab('com.xlson.groovycsv:groovycsv:1.3')
import static com.xlson.groovycsv.CsvParser.parseCsv

for(line in parseCsv(new FileReader('countries.csv'), separator: ';')) {
    println "Country=$line.COUNTRY, Capital=$line.CAPITAL"
}

The field names are taken from the header of the CSV file.
If the CSV file has no header, you can specify the field names programmatically.

Arteritis answered 20/9, 2016 at 8:58 Comment(0)
H
5

With Apache Commons-CSV

@Grab('org.apache.commons:commons-csv:1.2')
import org.apache.commons.csv.CSVParser
import static org.apache.commons.csv.CSVFormat.*

import java.nio.file.Paths

Paths.get('countryInfo.txt').withReader { reader ->
    CSVParser csv = new CSVParser(reader, DEFAULT.withHeader())

    for (record in csv.iterator()) {
        println record.dump()
    }
}

Commons-CSV has nice API and I recommend that.


With GroovyCSV:

@Grab('com.xlson.groovycsv:groovycsv:0.2')
import com.xlson.groovycsv.CsvParser

def csv = '''Name,Lastname
Mark,Andersson
Pete,Hansen'''

def data = new CsvParser().parse(csv)
for(line in data) {
    println "$line.Name $line.Lastname"
}

(Taken from it's samples)


Last resort: Regular expression.

Here's how I parsed a file that might contain a quoted escaped string in it's fourth column:

    File detailedStatsFile = new File("stats.csv");
    detailedStatsFile.eachLine { line, number ->
        // Number Of Executions, Total Milliseconds, Milliseconds per execution, "Type"
        def match = line =~ /([^,]*?),\s*([^,]*?),\s*([^,]*?),\s*(?:([^",]+)|(?:"((?:[^\\"]++(?:\\")?)++)"))$/; //"

        if (!match.matches())
            continue;

        def numberOfExecs = Integer.valueOf(match.group(1));
        def totalMillis = Integer.valueOf(match.group(2));
        def detailedStatName = match.group(4);
        if (detailedStatName == null)
            detailedStatName = match.group(5).replaceAll('\\"','"');
Herculaneum answered 2/5, 2016 at 17:39 Comment(1)
First method fails with groovy.lang.MissingMethodException: No signature of method: sun.nio.fs.UnixPath.withReader() is applicable for argument types: (myscript$_run_closure1) values: [myscript$_run_closure1@536f2a7e]Arteritis
A
3

Example using opencsv

@Grab('com.opencsv:opencsv:4.0')
import com.opencsv.CSVReader
import com.opencsv.CSVWriter

class TestCsvReader {


    static main(args) {
        def csv = '''"a","b","c"
"d","e","f"
'''
        def Reader csvFileReader = new StringReader(csv)
        def Writer csvFileWriter = new PrintWriter(System.out)
        def CSVReader reader = new CSVReader(csvFileReader)
        def CSVWriter writer = new CSVWriter(csvFileWriter)

        reader.iterator().each { fields ->
            writer.writeNext(fields)
        }
        reader.close()
        writer.close()
    }
}
Anserine answered 16/11, 2017 at 11:35 Comment(0)
P
2

Example from production code done by SAP developers in SCPi SAP Cloud Platform Integration groovy iFlow:

String[] parseCSVLine(String line) {
    // Create a pattern to match breaks
    Pattern p =
            Pattern.compile(",(?=([^\"]*\"[^\"]*\")*(?![^\"]*\"))");
    // Split input with the pattern
    String[] fields = p.split(line);
    for (int i = 0; i < fields.length; i++) {
        // Get rid of residual double quotes
        fields[i] = fields[i].replace("\"", "");
    }
    return fields;
}

Usage:

cols = parseCSVLine(line)
value = cols[0]
value = cols[1]
Poucher answered 12/9, 2020 at 23:51 Comment(1)
This is exactly what I was looking for. Thanks :)Gonococcus
H
1

I prefer a slight tweak on the accepted answer: zip the columns and values together, as opposed to indexing each one by number. The result is slightly shorter code.

def sql = Sql.newInstance("jdbc:mysql://localhost:3306/mydb", "user", "pswd", "com.mysql.jdbc.Driver")
def people = sql.dataSet("PERSON")
def columns = ['first_name', 'last_name', 'email']
new File("users.csv").splitEachLine(",") {values ->
    people.add([columns, values].transpose().collectEntries())
}
Halliard answered 27/7, 2018 at 22:5 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.