Write CSV file column-by-column

Question

I was searching for an answer for this but I didn't find it. Does anyone have a solution for this kind of problem. I have a set of text variables that I have to write into the .CSV file using Java. I am currently doing a project with JavaScript that calls for Java. This is a function that I have right now that does the job well and writes the text into .CSV line by line.

function writeFile(filename, data)
{
   try
   { 

      //write the data

      out = new java.io.BufferedWriter(new java.io.FileWriter(filename, true));
      out.newLine();
      out.write(data);
      out.close();
      out=null;
   }
   catch(e)   //catch and report any errors
   {
      alert(""+e);
   }
}

But now I have to write parts of text one by one like the example bellow.

first0,second0,third0
first1,second1,third1
first2,second2,third2
.
.
.
first9,second9,third9

So the algorithm goes like this. The function writes first0 with comma then goes to the next line writes first1, goes to next line writes first2 and so one until first9. After that part is done the script goes to the beginning of the file and writes second0 behind the comma, goes to the next line and writes second1 behind the comma and so on. You get the idea.

So now I need java

Answer 1

You might want to consider using Super CSV to write the CSV file. As well as taking care of escaping embedded double-quotes and commas, it offers a range of writing implementations that write from arrays/Lists, Maps or even POJOs, which means you can easily try out your ideas.

If you wanted to keep it really simple, you can assemble your CSV file in a two-dimensional array. This allows to to assemble it column-first, and then write the whole thing to CSV when it's ready.

package example;

import java.io.FileWriter;
import java.io.IOException;

import org.supercsv.io.CsvListWriter;
import org.supercsv.io.ICsvListWriter;
import org.supercsv.prefs.CsvPreference;

public class ColumnFirst {

    public static void main(String[] args) {

        // you can assemble this 2D array however you want
        final String[][] csvMatrix = new String[3][3];
        csvMatrix[0][0] = "first0";
        csvMatrix[0][1] = "second0";
        csvMatrix[0][2] = "third0";
        csvMatrix[1][0] = "first1";
        csvMatrix[1][1] = "second1";
        csvMatrix[1][2] = "third1";
        csvMatrix[2][0] = "first2";
        csvMatrix[2][1] = "second2";
        csvMatrix[2][2] = "third2";

        writeCsv(csvMatrix);

    }

    private static void writeCsv(String[][] csvMatrix) {

        ICsvListWriter csvWriter = null;
        try {
            csvWriter = new CsvListWriter(new FileWriter("out.csv"), 
                CsvPreference.STANDARD_PREFERENCE);

            for (int i = 0; i < csvMatrix.length; i++) {
                csvWriter.write(csvMatrix[i]);
            }

        } catch (IOException e) {
            e.printStackTrace(); // TODO handle exception properly
        } finally {
            try {
                csvWriter.close();
            } catch (IOException e) {
            }
        }

    }

}

Output:

first0,second0,third0
first1,second1,third1
first2,second2,third2

Answer 2

Here is my solution to the problem. You don't need to keep the whole data in the buffer thanks to the low-level random access file mechanisms. You would still need to load your records one by one:

package file.csv;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.RandomAccessFile;
import java.nio.channels.FileChannel;
import java.util.Arrays;
import java.util.List;

public class CsvColumnWriter {

    public static void main(String args[]) throws Exception{

        CsvColumnWriter csvWriter = new CsvColumnWriter(new File("d:\\csv.txt"), new File("d:\\csv.work.txt"), 3);

        csvWriter.writeNextCol(Arrays.asList(new String[]{"first0", "first1", "first2"}));
        csvWriter.writeNextCol(Arrays.asList(new String[]{"second0", "second1", "second2"}));
        csvWriter.writeNextCol(Arrays.asList(new String[]{"third0", "third1", "third2"}));

    }

    public void writeNextCol(List<String> colOfValues) throws IOException{
        // we are going to create a new target file so we have to first 
        // create a duplicated version
        copyFile(targetFile, workFile);

        this.targetStream = new BufferedOutputStream(new FileOutputStream(targetFile));

        int lineNo = 0;

        for(String nextColValue: colOfValues){

            String nextChunk = nextColValue + ",";

            // before we add the next chunk to the current line, 
            // we must retrieve the line from the duplicated file based on its the ofset and length 
            int lineOfset = findLineOfset(lineNo);  

            workRndAccFile.seek(lineOfset);

            int bytesToRead = lineInBytes[lineNo];
            byte[] curLineBytes = new byte[bytesToRead];
            workRndAccFile.read(curLineBytes);

            // now, we write the previous version of the line fetched from the
            // duplicated file plus the new chunk plus a 'new line' character
            targetStream.write(curLineBytes);
            targetStream.write(nextChunk.getBytes());
            targetStream.write("\n".getBytes());

            // update the length of the line
            lineInBytes[lineNo] += nextChunk.getBytes().length; 

            lineNo++;
        }

        // Though I have not done it myself but obviously some code should be added here to care for the cases where 
        // less column values have been provided in this method than the total number of lines

        targetStream.flush();
        workFile.delete();

        firstColWritten = true;
    }

    // finds the byte ofset of the given line in the duplicated file
    private int findLineOfset(int lineNo) {  
        int ofset = 0;
        for(int i = 0; i < lineNo; i++)
            ofset += lineInBytes[lineNo] + 
                (firstColWritten? 1:0); // 1 byte is added for '\n' if at least one column has been written
        return ofset;
    }

    // helper method for file copy operation
    public static void copyFile( File from, File to ) throws IOException {
            FileChannel in = new FileInputStream( from ).getChannel();
            FileChannel out = new FileOutputStream( to ).getChannel();
            out.transferFrom( in, 0, in.size() );
    }

    public CsvColumnWriter(File targetFile, File workFile, int lines) throws Exception{
        this.targetFile = targetFile;
        this.workFile = workFile;

        workFile.createNewFile();

        this.workRndAccFile = new RandomAccessFile(workFile, "rw");

        lineInBytes = new int[lines];
        for(int i = 0; i < lines; i++)
            lineInBytes[i] = 0;

        firstColWritten = false;
    }

    private File targetFile;
    private File workFile;

    private int[] lineInBytes;
    private OutputStream targetStream;
    private RandomAccessFile workRndAccFile;
    private boolean firstColWritten;

}

Answer 3

I'm just going ahead and assume that you have some freedom how to fulfill this task. To my knowledge, you can't 'insert' text into a file. You can only do it by reading the file completely, change it in-memory, and then write back the result into the file.

So it would be better if you invert your data structure in-memory and then write it. If your data object is a matrix, just transpose it, so that it is in the format you want to write.

Answer 4

How about this

        Scanner input = new Scanner(System.in);
        String[] lines = new String[9];

        for (int j = 0; j < 2; j++) {
            for (int i = 0; i < 9; i++) {
                lines[i] += (String) input.nextLine() + ",";
            }
        }
        for (int i = 0; i < 9; i++) {
            lines[i] += (String) input.nextLine();
        }

Answer 5

Based on your requirements of not losing any data if an error occurs, perhaps you should rethink the design and use an embedded database (there is a discussion of the merits of various embedded databases at Embedded java databases). You would just need a single table in the database.

I suggest this because in your original question it sounds like you are trying to use a CSV file like a database where you can update the columns of any row in any order. In that case, why not just bite the bullet and use a real database.

Anyhow, once you have all the columns and rows of your table filled in, export the database to a CSV file in "text file order" row1-col1, row1-col2 ... row2-col1 etc.

If an error occurs during the building of the database, or the exporting of the CSV file at least you will still have all the data from the previous run and can try again.