Modifying complex csv files in java

Question

I wanted to write a program which can print, and modify the irregular csv files. The format is as follows:

    1.date
    2.organization name
    3. student name, id number, residence
       student name, id number, residence
       student name, id number, residence
       student name, id number, residence
       student name, id number, residence
    1.another date
    2.another organization name
    3. student name, id number, residence
       student name, id number, residence
       student name, id number, residence
      ..........

For instance, the data may be given as follows:

1. 10/09/2016
2. cycling club
3. sam, 1000, oklahoma
   henry, 1001, california
   bill, 1002, NY
1. 11/15/2016
2. swimming club
3. jane, 9001, georgia
   elizabeth, 9002, lousiana

I am a beginner and I have not found any viable resource online which deals with this type of problem. My main concern is, how do we iterate through the loop and identify the date and name of the club, and feed them into a array? Please advise.

Is that second code area an example schema or an example file? It doesn't look like a CSV file. — Paul Hicks, Mar 22 '16 at 00:13
Actually that isn't too difficult: you use a scanner to read line by line; the ones that should carry a data can be parsed using SimpleDateFormatter; next line ... you just store the string, and the next line, you feed into a CSV parser (just google for that term; you result in plenty of libraries that help with that). — GhostCat, Mar 22 '16 at 00:14

score 1 · Accepted Answer · answered Mar 22 '16 at 00:51

I think this should be helpful for you. Basically there should be some pattern in your messed up csv. Below is my code to arrange your csv

   public static void main(String[] args) throws FileNotFoundException, UnsupportedEncodingException {

        PrintWriter writer = new PrintWriter("file.txt", "UTF-8");
            try{

              //Create object of FileReader
              FileReader inputFile = new FileReader("csv.txt");

              //Instantiate the BufferedReader Class
              BufferedReader bufferReader = new BufferedReader(inputFile);

              //Variable to hold the one line data
              String line;
              String date="";String org ="";String student ="";
              // Read file line by line and print on the console
              while ((line = bufferReader.readLine()) != null)   {
                  if(line.contains("1.")){
                      if(date!="" || org!=""){
                          writer.println(date+","+org+","+student);
                          student ="";
                       }
                         date = line.substring(2);
                        }else if(line.contains("2.")){
                            org = line.substring(2);

                      }else{
                            line = "("+line+")";
                          student += line+",";
                  }

                    System.out.println(line);
              }
              writer.println(date+","+org+","+student);
              //Close the buffer reader
              bufferReader.close();
      }catch(Exception e){
        System.out.println("Error while reading file line by line:" + e.getMessage());                      
   }
       writer.close();
  }

This is the output you will get for this

   10/09/2016, cycling club,(3. sam, 1000, oklahoma),(   henry, 1001, california),(   bill, 1002, NY),
   11/15/2016, swimming club,(3. jane, 9001, georgia),(   elizabeth, 9002, lousiana),

I am reading the file from csv.txt. while loop goes through each line of text file.all the fields are stored in a variable. When next date comes I write all of them into output file. Last line of the csv is written to file after the while loop terminates.

Thank you so much for your input. Your code is very clear and easy to understand. This helped me a lot. — Saman Ray, Mar 22 '16 at 02:22

score 1 · Answer 2 · answered Mar 22 '16 at 20:24

Try uniVocity-parsers to handle this. For parsing this sort of format, you'll find a few examples here. For writing, look here and here.

Adapting from the examples I've given, you could write:

final ObjectRowListProcessor dateProcessor = new ObjectRowListProcessor();
final ObjectRowListProcessor clubProcessor = new ObjectRowListProcessor();
final ObjectRowListProcessor memberProcessor = new ObjectRowListProcessor();

InputValueSwitch switch = new InputValueSwitch(0){
    public void rowProcessorSwitched(RowProcessor from, RowProcessor to) {
    //your custom logic here
    if (to == dateProcessor) {
        //processing dates.
    }
    if (to == clubProcessor) {
        //processing clubs.
    }
    if (to == memberProcessor){
        //processing members
    }
};

switch.addSwitchForValue("1.", dateProcessor, 1);  //getting values of column 1 and sending them to `dateProcessor`
switch.addSwitchForValue("2.", clubProcessor, 1); //getting values of column 1 and sending them to `clubProcessor`
switch.addSwitchForValue("3.", memberProcessor, 1, 2, 3); //getting values of columns 1, 2, and 3 and sending them to `memberProcessor` 
setDefaultSwitch(memberProcessor, 1, 2, 3); //Rows with blank value at column 0 are members. Also get columns 1, 2, and 3 and send them to `memberProcessor`

CsvParserSettings settings = new CsvParserSettings(); //many options here, check the tutorial and examples

// configure the parser to use the switch
settings.setRowProcessor(switch);

//creates a parser
CsvParser parser = new CsvParser(settings);

//parse everying. Rows will be sent to the RowProcessor of each switch, depending on the value at column 0.
parser.parse(new File("/path/to/file.csv"));

Disclaimer: I'm the author of this library, it's open-source and free (Apache 2.0 license)

Modifying complex csv files in java

2 Answers2

Linked