search a column word in csv file and replace it by another value java

Question

I have two csv file, main and look. main file contains a list of word like this:

a,c,e,f
b,d,o,f

and look.csv contain two rows like this:

a,f,j
1,0,1

I want to search for each word of main.csv and find a match in look.csv. if there was a match, i replace word in main.csv by a value that is in the row 2 from look.csv

i write this code

public class lookup {
   public static void main(String args[]) throws FileNotFoundException
   {
       String word;
       Scanner main = new Scanner(new File("C:\\Users\\sa\\Desktop\\hotel2\\all.csv"));
       main.useDelimiter(",");           
       Scanner look=new Scanner (new File("C:\\Users\\sa\\Desktop\\comtedad.csv"));

       while (main.hasNext())
       {
           word=main.next()  ;

           while (look.hasNext()){
               if (main.next().equals(look.next())) {//code for replacing}
                   }
    }
               main.close();




  }}

how can i access to second row of look.scv when two entry were matched?

Answer 1

A few things to note before posting my solution:

• there is no easy way to replace the contents of a file while reading it, you have to create a new one and output what you need there
• if you want to replace values, then this is good place use a Map
• your code implies that the look.csv file contains only 2 lines

The following would achieve what you are looking for, but again, the output is in a different file (you can rename afterwards if you wish to do so) :

public class Main {

public static void main(String[] args) throws Exception {
    //1. create Scanners that will look through the CSV files
    Scanner main = new Scanner(new File("main.csv"));
    Scanner look = new Scanner(new File("look.csv"));

    //2. create the final CSV file that will contain both the replaced words and non-replaced words from main.csv
    FileWriter replaced = new FileWriter(createReplacedCsv());

    //3. store contents of look.csv in a Map
    Map<String, String> lookCsvWords = storeLookCsvInSet(look);

    //4. Store the contents of the file in a StringBuilder, so you can use the StringBuilder.deleteCharAt(int)
    // and .lastIndexOf(String) to delete the last comma
    StringBuilder builder = new StringBuilder();

    //5. keep track of the line we are on
    int line = 0;

    //6. iterate through main.csv and search for replaceable words, and write them to replaced.csv if they are found
    while (main.hasNext()) {

        //search the first line for replaceable chars
        String[] wordsInLine = main.nextLine().split(",");
        for (String word : wordsInLine) {
            if (lookCsvWords.containsKey(word) && line == 0) {
                word = lookCsvWords.get(word);
            }
            builder.append(word).append(",");
        }
        line ++;
        builder.deleteCharAt(builder.lastIndexOf(","));
        builder.append("\n");
    }

    //7. write the contents of the StringBuilder to the file
    replaced.write(builder.toString());
    replaced.close();
}

/*
 * Creates the replaced.csv file if it doesn't exist
 */
private static File createReplacedCsv() throws Exception {
    File replacedCsv = new File("replaced.csv");
    replacedCsv.createNewFile();
    return replacedCsv;
}

/*
 * Store the words within look.csv in a Map.
 * This method assumes that look.csv has only two lines
 */
private static Map<String, String> storeLookCsvInSet(Scanner lookScanner) throws Exception {
    Map<String, String> lookCsvWordMappings = new HashMap<String, String>();
    String[] line1Values = lookScanner.nextLine().split(",");
    String[] line2Values = lookScanner.nextLine().split(",");

    for (int i=0; i<line1Values.length; i++) {
        lookCsvWordMappings.put(line1Values[i], line2Values[i]);
    }
    return lookCsvWordMappings;
}

}

Answer 2

Try first to load look.csv in a Mapping data structure like Map<String,String> like this :

Map<String,String> mapping=new HashMap<String,String>()
Scanner look=new Scanner (new File("C:\\Users\\sa\\Desktop\\look.csv"));
String[] keys=look.nextLine().split(",");
String[] values=look.nextLine().split(",");

for(int i=0;i<keys.length;++i)
mapping.put(keys[i],values[i]);

then write a new file while reading the main file like this ;

Scanner main = new Scanner(new File("C:\\Users\\sa\\Desktop\\hotel2\\all.csv"));

PrintWriter  mainOutput = new PrintWriter  (new File("C:\\Users\\sa\\Desktop\\hotel2\\all.out.csv"));
while (main.hasNext()){
String[] nextTokens=main.nextLine().split(",");
for(String token:nextTokens)
if(mapping.get(token)!=null)
{
mainOutput .print(mapping.get(token)+",");
}else {

mainOutput .print(token+",");
}
mainOutput .println();
}

mainOutput .close();