How do I create a csv file with dynamically rows

Question

I just can't figure out how to create a csv file with dynamically rows. I used commons-csv to write 3 rows to a file but I need some more.

I'm reading values out of a database line by line which look like that:

[ID:1,TSP:'2018-01-01 00:10:00', VALUE: 856830, VAL1:'36,704'],
[ID:4,TSP:'2018-01-01 00:12:00', VALUE: 736830, VAL1:'1,14'],
[ID:5,TSP:'2018-01-01 00:10:00', VALUE: 656830, VAL1:'12,504'],
[ID:5,TSP:'2018-01-01 00:50:00', VALUE: 936830, VAL1:'5,18'],
[ID:3,TSP:'2018-01-01 00:10:00', VALUE: 736860, VAL1:'3,4'],
[ID:4,TSP:'2018-01-01 00:50:00', VALUE: 726830, VAL1:'9,14']

I wanna create a .csv file with a formatting like that:

TSP(2018-01-01 00:10:00) | VALUE_ID1 | VAL_ID1 | VALUE_ID3 | VAL_ID3 | VALUE_ID5 | VAL_ID5

TSP(2018-01-01 00:12:00) | VALUE_ID4 | VAL_ID4

TSP(2018-01-01 00:50:00) | VALUE_ID4 | VAL_ID4 | VALUE_ID5 | VAL_ID5

I hope anyone can help me out with this one, cuz it kills me...

Thanks in advance!

EDIT:

I used groupBy on the TSP so I have the following pattern now:

TSP(2018-01-01 00:10:00):[[ID:1,TSP(2018-01-01 00:10:00),VALUE_ID1,VAL_ID1], [ID:3,TSP(2018-01-01 00:10:00),VALUE_ID3,VAL_ID3], [ID:5,TSP(2018-01-01 00:10:00),VALUE_ID5,VAL_ID5]]

TSP(2018-01-01 00:12:00):[[ID:4,TSP(2018-01-01 00:10:00),VALUE_ID4,VAL_ID4]]

etc. that's where I got stuck now –

i guess you have to put all values with the same time in one line. and id=5 has the different date then id=1 by mistake... if yes, just sort the resultset by TSP field. and while it's the same put all you need in one line, if TSP has different value print out new line and new TSP value... — daggett
– daggett, Commented Jul 17, 2018 at 7:49
yea, it was a mistake. I used groupBy already (edited the topic) — CeDeZ
– CeDeZ, Commented Jul 17, 2018 at 8:20

Yogi · Accepted Answer · 2018-07-17 07:50:27Z

1

You can use below Java API for CVS generation:

Both API has read/write API for working with CSV Examples of APIs:

I would prefer to use OpenCSV

answered Jul 17, 2018 at 7:50

Yogi

1,92515 silver badges24 bronze badges

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

2 Comments

CeDeZ Over a year ago

I tried with Apache Commons CSV but can't get the pattern implemented

TraxX Over a year ago

Another API you can use is supercsv

Jeronimo Backes · Accepted Answer · 2018-07-20 00:54:58Z

I believe univocity-parsers can help you greatly here. Check this section of its tutorial. You can write your rows like this:

File outputFile = new File("/path/to/your.csv");
CsvWriter writer = new CsvWriter(outputFile, new CsvWriterSettings());

String currentTime = null;
for(String[] row : getDataFromYourDatabase()){
    if(row[1].equals(currentTime)){ //check the time signature
        writer.addValue(row[0]); // write the ID on a column
        writer.addValue(row[2]); // write the value associated with the ID on the next column
    } else {
        if(currentTime != null){
             writer.writeValuesToRow(); // generates a row.
        }
        currentTime = row[1];
        writer.addValue(row[1]); //writes the time to the first column of the next row
    }
}

Assuming method getDataFromYourDatabase() returns all rows ordered by the time (i.e. the values that look like this: TSP(2018-01-01 00:10:00)) this should work great.

Hope it helps.

Disclosure: I'm the author of this library. It's open-source and free (Apache 2.0 license)

user3863921 · Accepted Answer · 2019-11-08 23:18:47Z

This is the best I can come up with using Fuzzy-Csv (https://github.com/kayr/fuzzy-csv/)

package fuzzycsv

import static fuzzycsv.FuzzyStaticApi.reduce

def data = [[ID: 1, TSP: '2018-01-01 00:10:00', VALUE: 856830, VAL1: '36,704'],
            [ID: 4, TSP: '2018-01-01 00:12:00', VALUE: 736830, VAL1: '1,14'],
            [ID: 5, TSP: '2018-01-01 00:10:00', VALUE: 656830, VAL1: '12,504'],
            [ID: 5, TSP: '2018-01-01 00:50:00', VALUE: 936830, VAL1: '5,18'],
            [ID: 3, TSP: '2018-01-01 00:10:00', VALUE: 736860, VAL1: '3,4'],
            [ID: 4, TSP: '2018-01-01 00:50:00', VALUE: 726830, VAL1: '9,14']]

def list =
        FuzzyCSVTable
                .fromMapList(data)
                .transform('ID') { "VAL_ID${it.ID}" }
                .transform('TSP') { "TSP_(${it.TSP})" }
                .summarize('TSP', reduce { it['ID'] }.az('IDS'))
                .with { tbl(csv.collect { it.flatten() }[1..-1]) }


println(list.toCsvString())

output

"TSP_(2018-01-01 00:10:00)","VAL_ID1","VAL_ID5","VAL_ID3"
"TSP_(2018-01-01 00:12:00)","VAL_ID4"
"TSP_(2018-01-01 00:50:00)","VAL_ID5","VAL_ID4"

Collectives™ on Stack Overflow

How do I create a csv file with dynamically rows

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related