How to write a list to CSV with Super CSV

Question

Exception in thread "main" org.supercsv.exception.SuperCsvException: The number of columns to be processed (229326) must match the number of CellProcessors (8):

I believe i may have to redo what im doing using supercsv as it may be easier in the long run however im open to any other suggestions. I simply want to write back to a csv file, i have a list with all the data in it however the ouput is like this

4350 02/06/2013 3965.21 0.0 0.0 0.0 0.0 0.0,
4698 02/06/2013 498.16 0.0 0.0 0.0 0.0 0.0, 
4992 02/06/2013 97.87 87.82 6.05 0.0 0.0 0.0,  
4441 02/06/2013 18.8 71.98 11.6 0.0 0.0 -42.5,  54092 02/06/2013 105.11 118.82 6.24 0.0 0.0 0.0,

I've managed to get the out put i want by replacing strings within the list however when it runs it hangs and i believe its due to how i'm writing back to the csv, i'm not sure, what else to do other than to write it back to the csv diffrently not using super csv. The error i get is

"1805","31/07/2013","-233.4","0.0","0.0","0.0","0.0","0.0"
"8054","31/07/2013","280.45","82.38","52.38","0.0","0.0","-200.0"The number of columns to be processed (1) must match the number of CellProcessors (8):

My witer class is as follows

package writer;

import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.supercsv.cellprocessor.FmtDate;
import org.supercsv.cellprocessor.ParseDouble;
import org.supercsv.cellprocessor.ParseInt;
import org.supercsv.cellprocessor.constraint.NotNull;
import org.supercsv.cellprocessor.ift.CellProcessor;
import org.supercsv.io.CsvListWriter;
import org.supercsv.io.ICsvListWriter;
import org.supercsv.prefs.CsvPreference;


public class RentStatementsWriter {
public ArrayList rData;
private List<String> csvData;

char b = ',';

public RentStatementsWriter(ArrayList rentData)  {
    rData = rentData;
    ICsvListWriter listWriter = null;


    try{
        listWriter = new CsvListWriter(new FileWriter("rentl.csv"),CsvPreference.STANDARD_PREFERENCE);
        CellProcessor[] processors =new CellProcessor[]{
                new ParseInt(),
                new FmtDate("dd/MM/yyyy"),//
                new ParseDouble(),
                new ParseDouble(),
                new ParseDouble(),
                new ParseDouble(),
                new ParseDouble(),
                new ParseDouble(),

        };
        final String [] header = new String []{"_num", "End_Date", "bal_val","rval","cval","bf_val","aval","pval"};

        System.out.print("to string "+rData.toString().replaceFirst(" ", "\"").replaceAll("\\,"," \\,").replaceAll("  ", "").replaceAll(" ", "\"\\,\"").replaceAll("\"\\,\"\\,", "\"\n\""));
        csvData = Arrays.asList(rData.toString().replaceFirst(" ", "\"").replaceAll("\\,"," \\,").replaceAll("  ", "").replaceAll(" ", "\"\\,\"").replaceAll("\"\\,\"\\,", "\"\""));
        /*
         * replace
         * .replaceAll(" ", "\"\\,\"")
         */
        listWriter.writeHeader(header);
        listWriter.write(csvData, processors);



    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        System.out.print(e+" file unable to write");
    } finally {
        if(listWriter !=null){
            try {
                listWriter.close();
            } catch (IOException e) {

                e.printStackTrace();
                System.out.println("list writer");
            }
        }

    }


}
String listToCsv(List<String> listOfStrings, char separator) {
    StringBuilder sb = new StringBuilder();

    // all but last
    for(int i = 0; i < listOfStrings.size() - 1 ; i++) {
        sb.append("\""+listOfStrings.get(i)+"\"");
        sb.append(separator);
    }

    // last string, no separator
    sb.append(listOfStrings.get(listOfStrings.size()-1));

    return sb.toString();
}





}

What am i missing in this syntax, or is there a better way of doing this task

Answer 1

There's a couple of problems:

1. ParseInt and ParseDouble are used for reading CSV Strings into Integer and Double respectively. See the handy table here to see what cell processors can be used for reading/writing or both. You can leave them null if you want, and Super CSV will just call toString() on each object.

2. The exception you're getting (1 column / 8 processors) indicates that you're expecting there to be 8 columns (i.e. 8 elements in your List), but there's only 1. You're only passing a single String into Arrays.asList() - looks like you're assuming this method actually splits the String into a List (it doesn't!).

3. Why are you converting your rent data List to a String? That is really bizarre. If your data needs any manipulation (I'm not sure if it does), then you should be updating each element in your List, not converting the whole List to a String then trying to split it up again.

4. What is the output when you don't do any replacement, i.e. what happens when you pass your rentData list directly to listWriter.write()?

Can I suggest you fix the processor set up (replace the ParseInt and ParseDouble with null), pass the rentData List directly to Super CSV...

listWriter.write(rentData, processors);

...then post the result (output/stacktrace) to your question.

Answer 2

The Solution to this question lies in the question.

The number of columns to be processed (1) must match the number of CellProcessors (8):

You are actually having more number of comma separated data in a row than you initially told SuperCSV.

final String [] header = new String []{"_num", "End_Date", "bal_val","rval","cval","bf_val","aval","pval"};

This puts SuperCSV to assume that only 8 number of values expected for each row and each column corresponds to one header. If you pass more values in row, it doesn't know what's that value corresponds to, so it's throwing Exception.

This link gives you how to define optional/mandatory columns.

Answer 3

@Hound Dog Although you was probably right i couldn't get it working the way i wanted it myself. I changed the list to of type string and was stil getting that[lorg supercsv] crap, i just decided to quit with the super csv thing, just in case anybody runs into this issue doing it like this i found to be be easier. The stuff in the constructor is not needed apart from the generate csv method

package writer;




public class RentStatementsWriter {
/*
 * 
 * problem being that for super csv to work each entry will need a seperate list
 */
public ArrayList<RentStatements> rData;
private List<String> csvData;

char b = ',';

public RentStatementsWriter(ArrayList rentData)  {
    rData = rentData;

    csvData = new ArrayList<String>();



    try{


        generateCsvFile("renttest.csv", rentData,b);

    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        System.out.print(e+" file unable to write");
    } finally {
        if(listWriter !=null){
            try {
                listWriter.close();
            } catch (IOException e) {

                e.printStackTrace();
                System.out.println("list writer");
            }
        }

    }


}

private void generateCsvFile(String fileName, ArrayList rentData, char b2) {

    try{
        final String [] header = new String []{"tncy_num", "End_Date", "bal_val","rent_val","chg_val","benf_val","adj_val","pay_val"};
        FileWriter writer = new FileWriter(fileName);


        writer.append("tncy_num");
        writer.append(',');
        writer.append("End_Date");
        writer.append(',');
        writer.append("bal_val");
        writer.append(',');
        writer.append("rent_val");
        writer.append(',');
        writer.append("chg_val");
        writer.append(',');
        writer.append("benf_val");
        writer.append(',');
        writer.append("adj_val");
        writer.append(',');
        writer.append("pay_val");
        writer.append('\n');
        for(int i = 0;i <rentData.size();i++){
            String line = rentData.get(i).toString();

            String  bits []=line.split(" ");//splits each space into diffrent bits

            //string something = bits.get waleva the it is surround it by ""
             writer.append(bits[1]);

                writer.append(b2);

                writer.append(bits[2]);

                writer.append(b2);

                writer.append(bits[3]);

                writer.append(b2);

                writer.append(bits[4]);

                writer.append(b2);

                writer.append(bits[5]);

                writer.append(b2);

                writer.append(bits[6]);

                writer.append(b2);

                writer.append(bits[7]);

                writer.append(b2);

                writer.append(bits[8]);

                writer.append('\n');


        }


        writer.flush();
        writer.close();

    }catch(IOException e)
    {

        e.printStackTrace();
    }
    // TODO Auto-generated method stub

}

}