Java CSV file Manipulation

I have following sample CSV file

rc,u,s,ui,gh
m,1,8,0,12
n,3,0,0,7
d,1,1,8,0

I want to read this CSV file and get column by its name (e.g., s). subtract fetched column by some values and update that column in the CSV file开发者_JAVA百科.

Is there an easy way to do it in Java?

---

It is quite easy in PHP, you can use fgetcsv function http://php.net/manual/en/function.fgetcsv.php

Added Sample

    <?php

    $inHandle= fopen("input.csv", "r");

    $text = "";
    $someVar = array(1, 2, 3, 4);           // for subtracting 1 from col1 2 from col2 ...

    while ( ( $data = fgetcsv($inHandle) ) !== FALSE) 
    {
    $text  = $text  . $data[0];

    for ($i= 1; $i < count($data);  $i++) 
    {

        if($i==0)
            $text =  $text .  $data[$i] . ", ";
        else
            $text =  $text .  ( $data[$i] - $someVar[$i -1] ) . " , ";
    }
       $text =  $text . " \n";
    }

    fclose($inHandle);

    $outHandle = fopen('output.csv', 'w');

    fwrite($outHandle , $text );

    fclose($outHandle );
    ?>

---

String line;
BufferedReader br = new BufferedReader(new FileReader("path_to_your_file"));

while ((line = br.readLine()) != null) { 
       StringTokenizer tokenizer = new StringTokenizer(line, ",");

       while(tokenizer.hasMoreTokens()) {
              String token = tokenizer.nextToken(); 
       } 
}

This way you can get your comma separated tokens. Do what you want with them and then, have a look at BufferedWriter class to store back the values.

---

As far as I know, there is no 'easy' way of doing this, at least with the standard Java API. Looping is probably going to be the only option here.

There are, however, other languages which make this kind of thing relatively simple. R (freely available and open source) is a great language for data manipulation.

---

In PHP you could try with something like this:

<?php
function csv2PhpArray($sFile,$sSeparator = ';'){
$arrCsv;
$arrCsvKeys;
$i = 0;
    if (($handle = fopen($sFile, "r")) !== false) {
        while (($arrRow = fgetcsv($handle, 1000, $sSeparator)) !== false) {
            //Each row
            $nLenRow = count($arrRow);
            for ($j=0; $j < $nLenRow; $j++) {
                if($i==0){
                    $arrCsvKeys[] = $arrRow[$j];
                }else{
                    $arrCsv[$arrCsvKeys[$j]][] = $arrRow[$j];
                }
            }
            if($i==0) $i++;
        }
        fclose($handle);
    }
    return $arrCsv;
}
function updateCsv($arrCsv,$sFile,$sSeparator = ';'){
    $arrCsvKeys = array_keys($arrCsv);
    $i = 0;
    $nMaxKeys = count($arrCsvKeys);
    $nMax  = count($arrCsv[$arrCsvKeys['0']]);
    $arrInput;
    for($k=0;$k<$nMaxKeys;$k++){
        $arrInput[$i][$k] = $arrCsvKeys[$k];
    }
    for($j=0;$j<$nMax;$j++){
        $i++;
        for($k=0;$k<$nMaxKeys;$k++){
            $arrInput[$i][$k] = $arrCsv[$arrCsvKeys[$k]][$j];
        }
    }

    $nMax  = count($arrInput);
    $fp = fopen($sFile, 'w');
    for($i=0;$i<$nMax;$i++){
        fputcsv($fp, $arrInput[$i]);
    }

    fclose($fp);
}

$arrCsv = csv2PhpArray('file.csv',';');
echo '<pre>';
var_dump($arrCsv);
echo '</pre>';
$nMax = count($arrCsv['s']);
for($i=0;$i<$nMax;$i++){
    $arrCsv['s'][$i]= intval($arrCsv['s'][$i])+10;
}
echo '<pre>';
var_dump($arrCsv);
echo '</pre>';
updateCsv($arrCsv,'file2.csv',';');
echo '<pre>';
var_dump(csv2PhpArray('file2.csv',';'));
echo '</pre>';
?>

I put two files (file.csv and file2.csv) to see the update results.

I see you change the answer to only java, but I hope this can be help you.

---

You can use JEzCSV for read or write CSV file, can you can read the code, is open source

---

You may be interested in the code I have posted to GitHub which reads CSV, puts the CSV into a POJO, and then writes from the POJO to a CSV file. This modifies the original CSV using the POJO and writes the new columns you need to a new CSV file.

https://github.com/Blue4570/CsvAppender

public class CsvWriter {

private File csvFile;
private String delimiter;
static final String NEW_FILE_LOCATION_AND_NAME = "<LOCATION_AND_NAME_OF_YOUR_FILE_HERE";


public CsvWriter(String delimiter) {
    this.csvFile = new File(getClass().getClassLoader().getResource("csv/TestCsv.txt").getFile());
    this.delimiter = delimiter;
}

public void writeFile() {
    List<NewTable> tableList = readFile(csvFile);
    try(FileWriter writer = new FileWriter(NEW_FILE_LOCATION_AND_NAME)) {
        writer.append("ID$FirstName$LastName").append("\n");
        tableList.forEach(table -> {
            try {
                writer.append(table.getId()).append(delimiter);
                writer.append(table.getFirstName()).append(delimiter);
                writer.append(table.getLastName());
                writer.append("\n");
            } catch (IOException e) {
                System.out.println("Unable to write new File");
                e.printStackTrace();
            }
        });
    } catch(IOException e) {
        System.out.println("Unable to write new file");
    }
}

private List<NewTable> readFile(File csvFile) {
    List<NewTable> tableList = new ArrayList<>();
    try (InputStream inputFS = new FileInputStream(csvFile);
         BufferedReader br = new BufferedReader(new InputStreamReader(inputFS))
    ) {
        // skip the header of the csv
        tableList = br.lines().skip(1).map(mapToItem).collect(Collectors.toList());

    } catch (IOException e) {
        System.out.println("Error reading file");
    }
    return tableList;
}

private Function<String, NewTable> mapToItem = (line) -> {
    Optional<NewTable> newTable = Optional.empty();
    if (line != null) {
        String[] p = line.split("\\$");
         newTable = Optional.of(new NewTable(p[0], p[1]));
    }
    return newTable.orElseGet(NewTable::new);
};

}