Q:

Compare Two CSV Files and Fetch Data

I have two csv files. One Master CSV File around 500000 records. Another DailyCSV file has 50000 Records. 
The DailyCSV files misses few columns which has to be fetched from Master CSV File.
For example
DailyCSV File
id,name,city,zip,occupation
1,Jhon,Florida,50069,Accountant

MasterCSV File
id,name,city,zip,occupation,company,exp,salary
1, Jhon, Florida, 50069, Accountant, AuditFirm, 3, $5000

What I have to do is, read both files, match the records with ID, if ID is present in the master file, then i have to fetch company, exp, salary and write it to a new csv file.
How to achieve this.??
What I have done Currently
 while (true) {
            line = bstream.readLine();
            lineMaster = bstreamMaster.readLine();

            if (line == null || lineMaster == null)
            {
                break;
            }
            else
            {
                while(lineMaster != null)
                readlineSplit = line.split(",(?=([^\"]*\"[^\"]*\")*[^\"]*$)", -1);
                String splitId = readlineSplit[4];
                 String[] readLineSplitMaster =lineMaster.split(",(?=([^\"]*\"[^\"]*\")*[^\"]*$)", -1);
                  String SplitIDMaster = readLineSplitMaster[13];
                  System.out.println(splitId + "|" + SplitIDMaster);
                  //System.out.println(splitId.equalsIgnoreCase(SplitIDMaster));
                  if (splitId.equalsIgnoreCase(SplitIDMaster)) {

                      String writeLine = readlineSplit[0] + "," + readlineSplit[1] + "," + readlineSplit[2] + "," + readlineSplit[3] + "," + readlineSplit[4] + "," + readlineSplit[5] + "," + readLineSplitMaster[15]+ "," + readLineSplitMaster[16] + "," + readLineSplitMaster[17];
                      System.out.println(writeLine);
                      pstream.print(writeLine + "\r\n");
                  }
            }

        }pstream.close();
        fout.flush();
        bstream.close();
        bstreamMaster.close();

A:

First of all, your current parsing approach will be painfully slow. Use a CSV parsing library dedicated for that to speed things up. With uniVocity-parsers you can process your 500K records in less than a second. This is how you can use it to solve your problem:
First let's define a few utility methods to read/write your files:
//opens the file for reading (using UTF-8 encoding)
private static Reader newReader(String pathToFile) {
    try {
        return new InputStreamReader(new FileInputStream(new File(pathToFile)), "UTF-8");
    } catch (Exception e) {
        throw new IllegalArgumentException("Unable to open file for reading at " + pathToFile, e);
    }
}

//creates a file for writing (using UTF-8 encoding)
private static Writer newWriter(String pathToFile) {
    try {
        return new OutputStreamWriter(new FileOutputStream(new File(pathToFile)), "UTF-8");
    } catch (Exception e) {
        throw new IllegalArgumentException("Unable to open file for writing at " + pathToFile, e);
    }
}

Then, we can start reading your daily CSV file, and generate a Map:
public static void main(String... args){
    //First we parse the daily update file.
    CsvParserSettings settings = new CsvParserSettings();
    //here we tell the parser to read the CSV headers
    settings.setHeaderExtractionEnabled(true);
    //and to select ONLY the following columns.
    //This ensures rows with a fixed size will be returned in case some records come with less or more columns than anticipated.
    settings.selectFields("id", "name", "city", "zip", "occupation");

    CsvParser parser = new CsvParser(settings);

    //Here we parse all data into a list.
    List<String[]> dailyRecords = parser.parseAll(newReader("/path/to/daily.csv"));
    //And convert them to a map. ID's are the keys.
    Map<String, String[]> mapOfDailyRecords = toMap(dailyRecords);
    ... //we'll get back here in a second.

This is the code to generate a Map from the list of daily records:
/* Converts a list of records to a map. Uses element at index 0 as the key */
private static Map<String, String[]> toMap(List<String[]> records) {
    HashMap<String, String[]> map = new HashMap<String, String[]>();
    for (String[] row : records) {
        //column 0 will always have an ID.
        map.put(row[0], row);
    }
    return map;
}

With the map of records, we can process your master file and generate the list of updates:
private static List<Object[]> processMasterFile(final Map<String, String[]> mapOfDailyRecords) {
    //we'll put the updated data here
    final List<Object[]> output = new ArrayList<Object[]>();

    //configures the parser to process only the columns you are interested in.
    CsvParserSettings settings = new CsvParserSettings();
    settings.setHeaderExtractionEnabled(true);
    settings.selectFields("id", "company", "exp", "salary");

    //All parsed rows will be submitted to the following RowProcessor. This way the bigger Master file won't
    //have all its rows stored in memory.
    settings.setRowProcessor(new AbstractRowProcessor() {
        @Override
        public void rowProcessed(String[] row, ParsingContext context) {
            // Incoming rows from MASTER will have the ID as index 0.
            // If the daily update map contains the ID, we'll get the daily row
            String[] dailyData = mapOfDailyRecords.get(row[0]);
            if (dailyData != null) {
                //We got a match. Let's join the data from the daily row with the master row.
                Object[] mergedRow = new Object[8];

                for (int i = 0; i < dailyData.length; i++) {
                    mergedRow[i] = dailyData[i];
                }
                for (int i = 1; i < row.length; i++) { //starts from 1 to skip the ID at index 0
                    mergedRow[i + dailyData.length - 1] = row[i];
                }
                output.add(mergedRow);
            }
        }
    });

    CsvParser parser = new CsvParser(settings);
    //the parse() method will submit all rows to the RowProcessor defined above.
    parser.parse(newReader("/path/to/master.csv"));

    return output;
}

Finally, we can get the merged data and write everything to another file:
    ... // getting back to the main method here
    //Now we process the master data and get a list of updates
    List<Object[]> updatedData = processMasterFile(mapOfDailyRecords);

    //And write the updated data to another file
    CsvWriterSettings writerSettings = new CsvWriterSettings();
    writerSettings.setHeaders("id", "name", "city", "zip", "occupation", "company", "exp", "salary");
    writerSettings.setHeaderWritingEnabled(true);

    CsvWriter writer = new CsvWriter(newWriter("/path/to/updates.csv"), writerSettings);
    //Here we write everything, and get the job done.
    writer.writeRowsAndClose(updatedData);
}

This should work like a charm. Hope it helps.
Disclosure: I am the author of this library. It's open-source and free (Apache V2.0 license).

