Manipulate CSV file using groovy and java

Question

Newbie here with a question. I have the following .csv file as an example:

10;06.07.2022;This is test;

08;01.07.2020;This is test;

15;06.07.2021;This is test;

09;06.07.2021;This is test;

So its multiple rows with the same setup. I want to delete each row which have a date earlier then 06.07.2022. So in theory only the first row should still be in the .csv file and the other ones should get deleted.

I want to be able to declare the date as a variable. I already did the following to try to understand:

     private String dateii      = 'test.csv';                               // Filename Input
     private String dateio      = '';                                           // Filename Output


void openInputfile() {
        File outputfile = new File(dateio);
        outputfile.write('');
        
        File inputfile = new File(dateii);
        if (!inputfile.exists()) {
             println("No File")

        }
            
        List data = inputfile.readLines();
        for (String zeile in data) {
            
            
            if (zeile.startsWith('BEREICH')) {
                Header = zeile;
            } else {
                List buffer = zeile.split(";");
                if (zeile.size() > 0) {                                     // Remove Empty rows
                    
                    
                }
            } 
        }

EDIT:

So my questions are the following:

How can I delete a complete row?
How can I specify which rows to delete using the date?

Thank you!

why there are 2 languages in tags - java & groovy ? which one will be accepted as response? — daggett
– daggett, Commented Jul 6, 2022 at 13:48
@daggett Hello! Both are accepted. I can use both but don't know which one is better. I'm trying to use more groovy then Java — Gusdl
– Gusdl, Commented Jul 6, 2022 at 13:49
@Eritrean Hello thanks for your help! I edited the question to be more precise. I hope this clears it up a bit. Thank you again. — Gusdl
– Gusdl, Commented Jul 6, 2022 at 14:01

Roger Glover · Accepted Answer · 2022-07-06 15:46:40Z

1

Given the input file projects/test.csv containing these lines:

10;06.07.2022;This is test;
08;01.07.2020;This is test;
15;06.07.2021;This is test;
09;06.07.2021;This is test;

The following Groovy script:

import java.time.format.DateTimeFormatter
import java.time.LocalDate

final FMT = DateTimeFormatter.ofPattern('dd.MM.yyyy')

def targetDate = LocalDate.parse('06.07.2022', FMT)

def test = new File('projects/test.csv')
def filtered = new File('projects/filtered.csv')
def filteredOutput = filtered.newPrintWriter()

test.filterLine(filteredOutput) { line ->
   def parts = line.split(';')
   def lineDate = LocalDate.parse(parts[1], FMT)
   lineDate >= targetDate
}

Produces the following output in file projects/filtered.csv:

10;06.07.2022;This is test;

Is this what you were looking for?

It takes advantage of idiomatic Groovy code shortcuts and modern java.time date manipulation classes.

edited Jul 6, 2022 at 15:46

answered Jul 6, 2022 at 15:41

Roger Glover

3,27625 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gusdl Over a year ago

This is awesome! Thank you very much :) I polished it a bit and played with arguments so whenever I call the program I can say in which column the date is stored and also the targetDate is a argument now. Thanks Thanks Thanks!

Gusdl Over a year ago

I'm currently finishing my program. Is there a possibility to say at which line it should start? So for example I want to skip the first 3 lines and then start the filtering. Thank you!

Roger Glover Over a year ago

RE start line: 1) Define an integer variable initialized to zero (0) above the filterLine call. 2) Increment the variable on the first line INSIDE the closure in the filterLine call. 3) Modify the the boolean expression on the LAST line inside that closure to first check that the integer variable is greater than the skip count (something like this: i <= skipLines || lineDate >= targetDate).

Basil Bourque · Accepted Answer · 2022-07-06 15:01:39Z

This seems to be a schoolwork assignment, so I’ll be brief and omit code.

First, define your target date as a LocalDate object.

LocalDate target = LocalDate.of( 2021 , 6 , 7 ) ;

Your existing code has a list of fields for each row. Your goal is to filter by comparing the second field, the date.

Extract the second field, the date input.
Parse that date input as a LocalDate. Use a DateTimeFormatter object for parsing, with a formatting pattern you define.
Compare dates using LocalDate#isBefore.
If your criterion is met, write that line to the output file. If not met, move on to the next line, omitting this current line from your output file.

All aspects of this have been covered many many times on Stack Overflow. Search to learn more.

Tip: Java NIO.2. Java has old ways of handling files, and new ways. Use the new ways.

You asked:

How can I delete a complete row?

Skip it. Don’t write that line to the output file.

You asked:

How can I specify which rows to delete using the date?

Parse the text of the date field as a LocalDate. Compare to your target date using a LocalDate method such as isAfter or isBefore.

By the way, better naming will make your code easier to read and debug. data is vague. buffer is inaccurate. Header should start with a lowercase letter, header.

In real work we would not parse the CSV file directly. We would use any of the several good CSV parsing libraries available in the Java ecosystem. Example: Apache Commons CSV.

daggett · Accepted Answer · 2022-07-06 15:28:07Z

1

in groovy code could look like this if input file this simple - otherwise better to use csv library

import java.time.LocalDate

//could be replaced with src = new File(source_file_name)
def src = '''
10;30.07.2022;This is test;
08;01.07.2020;This is test;
15;06.07.2021;This is test;
09;06.07.2021;This is test;
'''

//could be replaced with dst = new File(target_file_name)
def dst = System.out
def now = LocalDate.now()

dst.withWriter{writer->
    src.eachLine{line->
        def row = line.split(/;/)
        if( row.size()==3 ){
            if( LocalDate.parse(row[1], 'dd.MM.yyyy').toEpochDay() > now.toEpochDay() ) {
                writer << line << '\n'
            }
        }
    }
}

edited Jul 6, 2022 at 15:28

answered Jul 6, 2022 at 15:03

daggett

29k3 gold badges52 silver badges61 bronze badges

3 Comments

Basil Bourque Over a year ago

Both of the terribly flawed Date classes were years ago supplanted by the modern java.time classes defined in JSR 310.

daggett Over a year ago

it's not yet deprecated - right? however Date easily could be replaced with LocalTime if there is a need.

Roger Glover Over a year ago

@daggett Considering the use case Date could probably only be easily replaced with LocalDate, since LocalTime does not have a calendar date component.

Collectives™ on Stack Overflow

Manipulate CSV file using groovy and java

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related