Groovy load .csv files

问题:

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 .

回答1:

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.

回答2:

Using xlson's GroovyCSV:

@Grab('com.xlson.groovycsv:groovycsv:1.1')
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.

回答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()
    }
}

回答4:

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('\\"','"');

回答5:

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())
}