Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Write CSV file column-by-column

Tags:

java

csv

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

like image 918
macroscripts Avatar asked Sep 23 '12 23:09

macroscripts


2 Answers

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
like image 181
James Bassett Avatar answered Sep 20 '22 10:09

James Bassett


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;

}
like image 40
RGO Avatar answered Sep 21 '22 10:09

RGO