merging two csv file with different header

Question

I need to merge the data in two CSV file with similar header but different data according to the their respective section. The CSV file will contain two sections,the first part contain the data according sector and the second part will contain the data from residential area. Here is my first CSV file

Sector,Total Number of Occurrence 
sector1,12
sector2,30
sector3,100

House,Total Number of Occurrence  
B12,80
A2,87

My second CSV file

Sector,Total Number of Occurrence 
sector 99,89
sector 11,9

House,Total Number of Occurrence 
Q11,22
Q22,67

I hope to generated a CSV file contain both data inside but the data must be allocated to the correct section which look like the following

Sector,Total Number of Occurrence 
sector1,12
sector2,30
sector3,100
sector 99,89
sector 11,9    

House,Total Number of Occurrence  
B12,80
A2,87
Q11,22
Q22,67

But I guess with my current developed source code, it's cant able to do that because it's contain the second header listed inside the CSV which are House,Total Number of Occurrence. May I know how to achieve my desired output? This is what my current csv output look like

Sector,Total Number of Occurrence 
sector1,12
sector2,30
sector3,100

House,Total Number of Occurrence  
B12,80
A2,87
sector 99,89
sector 11,9

House,Total Number of Occurrence  
B12,80
A2,87

This is my current developed source code

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.nio.charset.Charset;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class SummarizeReport1
{
   static ArrayList<String> list1 = new ArrayList<>();
   static String line1;
   public static void main(String[] args) throws IOException 
   {
        List<Path> paths = Arrays.asList(Paths.get("C:\\Users\\user\\Desktop\\file\\log\\backup\\report12017-10-31.csv"), Paths.get("C:\\Users\\user\\Desktop\\file\\log\\backup\\report12017-10-31 - Copy.csv"));
        List<String> mergedLines = getMergedLines(paths);
        Path target = Paths.get("C:\\Users\\user\\Desktop\\file\\log\\backup\\SummarizedReport1.csv");
        Files.write(target, mergedLines, Charset.forName("UTF-8"));
    }

    private static List<String> getMergedLines(List<Path> paths) throws IOException 
    {
        List<String> mergedLines = new ArrayList<> ();
        for (Path p : paths)
        {
            List<String> lines = Files.readAllLines(p, Charset.forName("UTF-8"));
            if (!lines.isEmpty()) {
                if (mergedLines.isEmpty()) {
                    mergedLines.add(lines.get(0)); //add header only once
                }
                mergedLines.addAll(lines.subList(1, lines.size()));
            }
        }
        return mergedLines;
    }   
}

AxelH · Accepted Answer · 2017-10-23 11:50:02Z

1

First, create a class to store the header and each row.

public class SubFile{
    private String headers;
    private List<String> lines
}

Then, read the files line per line. For the first line, simply create a new SubFile instance with the header. Each following line, add them in this instance (addLine).

You need to store more that one "sub" csv, so use a Collection, here, I will use a List, because why not...

List<SubFile> files;

Each time you read a header (first line or after an empty line), you need to check if that header would match an instance in the Collection to keep adding to it, or create an instance.

public SubFile getInstance(String headerLine){
     /*
     instance = search instance in collection 
     if (instance not found)
         create instance
         add it to the list
     return instance
     */
}

I believe this would be quite simple to implement so I will let you try first, you have the algorithm to use.

edited Oct 23, 2017 at 11:50

answered Oct 23, 2017 at 11:40

AxelH

14.6k2 gold badges27 silver badges56 bronze badges

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

2 Comments

yumi Over a year ago

thanks for your suggestion, do you mean I need to create another class call subFile in another program and store the CSV header inside the list which had declared in subFile? I am not expert in java

AxelH Over a year ago

@yumi I would use another class to keep the code clean and readable, but if you can't use a Class, it is always possible to do it without, you can simply use two List, a List<String> headers and (less fun to read) List<List<String>> rows. both list will have the same length, the index of the headers will match the index of his rows. Like I said, not clean anymore ;) Of course, it doesn't take much to learn OOP and how to use a simple class in Java (no need of inheritance here, so you just need the basics.

Collectives™ on Stack Overflow

merging two csv file with different header

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related