---

CsvTool.java

Go to the documentation of this file.

package net.threebit.utils.sosc;

/*
        Copyright 2003 Shawn Deleurme
        Copyright 2003 Kevin O'Donnell

        This program is free software; you can redistribute it and/or modify
        it under the terms of the GNU General Public License as published by
        the Free Software Foundation; either version 2 of the License, or
        (at your option) any later version.

        This program is distributed in the hope that it will be useful,
        but WITHOUT ANY WARRANTY; without even the implied warranty of
        MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
        GNU General Public License for more details.

        You should have received a copy of the GNU General Public License
        along with this program; if not, write to the Free Software
        Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
*/

import com.oreilly.servlet.*;
import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.logging.*;

public class CsvTool {

        private static Logger logger = Logger.getLogger("net.threebit.utils.sosc");

        static public List csvToList (File csvFile, String[] columnOrder) throws Exception {
                return csvToList( new FileReader(csvFile), columnOrder);
        }

        static public List csvToList (StringBuffer sb, String[] columnOrder) throws Exception {
                return csvToList( new StringReader(sb.toString()), columnOrder);
        }

        static public List csvToList (Reader reader, String[] columnOrder) throws Exception {
                if (columnOrder == null) { throw new Exception("Must specify a column order."); }
                BufferedReader br = new BufferedReader( reader );
                String line;

                // This array provides a mapping from the column offset in the incoming CSV file to the
                // requested offset in "columnOrder"
                int[] columnMapping = new int[columnOrder.length];

                // This array keeps track of what columns are encapsulated in quotation marks.
                boolean[] columnQuotes = new boolean[columnOrder.length];

                // Read in the first line; it contains the names of the incoming CSV file.
                line = br.readLine();
                if (Sosc.debug) { System.out.println("header: '"+line+"'"); }
                if (line == null) { throw new Exception("No data in CSV File"); }
                String[] columnNames = line.split(",");

                // Make sure that all of the columns specified in columnOrder are avaible.
                // TODO: this is O(n^2)
                for (int x = 0; x < columnOrder.length; x++) {
                        boolean ok = false;
                        for (int y = 0; y < columnNames.length; y++) {
                                boolean quoted = false;
                                String n = columnNames[y];
                                if (n.matches("^\\\".*\\\"$")) {
                                        n = n.substring(1,n.length()-1);
                                        quoted = true;
                                }
                                if (columnOrder[x].equalsIgnoreCase(n)) {
                                        ok = true;
                                        columnMapping[x] = y;
                                        columnQuotes[x] = quoted; // remember if this column is quoted in the original CSV file.
                                        break;
                                }
                        }
                        if (Sosc.debug) {
                                System.out.println(
                                        "column["+x+"] " +
                                        " name: " + columnOrder[x] + 
                                        " quoted: " + columnQuotes[x] +
                                        " column: " + columnMapping[x]
                                );
                        }
                        if (! ok) {
                                throw new Exception("CSVFORMATERROR: Could not find column '" + columnOrder[x] + "' in the CSV file");
                        }
                }

                // Read in the whole file and construct a List of String[]s
                int row = 0;
                List lines = new ArrayList(10000);
                String data[] = null;
                String dout[] = null;
                while ((line = br.readLine()) != null) {
                        row ++;
                        if (Sosc.debug) { System.out.println("line["+row+"]: '"+line+"'"); }
                        if (line.equals("")) { continue; }

                        data = line.split(",");
                        if (Sosc.debug) { for (int x = 0; x < data.length; x++) { System.out.println("["+x+"]: '"+data[x]+"'"); } }

                        if (data.length < columnOrder.length) {
                                throw new Exception(
                                        "Row "+row+" has fewer columns ("+data.length+") than the header row ("+columnOrder.length+"); " +
                                        "'"+line+"'"
                                );
                        }

                        //
                        // Handle the case where there are embedded commas within quoted cells.
                        // ie: "124 Madison Avenue, Apartment 4".
                        // See CsvToolTest.test5()
                        //
                        int maxLoops = data.length+2; // avoid infinite loops (+2 is just an adhoc buffer)
                        int loop = 1;
                        if (Sosc.debug && data.length > columnNames.length) {
                                System.out.println("Line has one or more commas within quoted fields");
                        }
                        while (data.length > columnNames.length && loop++ < maxLoops) {
                                for (int x = 0; x < data.length; x++) {
                                        if (data[x].startsWith("\"")) {
                                                if (data[x+1].endsWith("\"")) {

                                                        String newCell = data[x] + "," + data[x+1];
                                                        newCell = newCell.substring(1,newCell.length()-1);
                                                        // System.out.println("newCell: '" + newCell + "'");
        
                                                        String[] newData = new String[data.length-1];
                                                        for (int a = 0; a < newData.length; a++) {
                                                                if (a < x) { newData[a] = data[a]; }
                                                                else if (a == x) { newData[a] = newCell; }
                                                                else { newData[a] = data[a+1]; }
                                                        }
                                                        // for (int a = 0; a < newData.length; a++) { System.out.println("newData: " + a + " " + newData[a]); }
                                                        data = newData;
                                                        x = data.length;
                                                }
                                        }
                                }
                        }

                        dout = new String[ columnOrder.length ];
                        for (int x = 0; x < columnOrder.length; x++) {
                                dout[x] = data[ columnMapping[x] ];
                                if (Sosc.debug) { System.out.println("  out["+x+"]: dout: '"+dout[x]+"'"); }
                                if (columnQuotes[x]) {
                                        if (dout[x].matches("^\".*\"$")) {
                                                dout[x] = dout[x].substring(1,dout[x].length()-1);
                                        }
                                        else {
                                                throw new Exception("Unhandled embedded comma condition; dout["+x+"]: '"+dout[x]+"' line: '"+line+"'");
                                        }
                                }
                        }
                        lines.add(dout);
                }

                return lines;
        }

        static public StringBuffer listOfListsToCsv (List list) throws Exception {
                return listOfListsToCsv(list,new StringBuffer());
        }

        static public StringBuffer listOfListsToCsv (List list, StringBuffer sb) throws Exception {
                if (sb == null) { throw new Exception("No stringbuffer provided; use listOfMapsToCsv(List list) instead."); }

                // Silently allow a null list; just means the CVS has no rows
                if (list == null) { return sb; }

                for (Iterator i = list.iterator(); i.hasNext(); ) {
                        List row = (List) i.next();
                        for (Iterator j = row.iterator(); j.hasNext(); ) {
                                sb.append(j.next());
                                sb.append(",");
                        }
                        sb.deleteCharAt( sb.length()-1 ); // remove trailing ","
                        sb.append("\n");
                }

                return sb;
        }

        static public StringBuffer resultSetToCsv (ResultSet resultSet) throws Exception {
                return resultSetToCsv(resultSet,new StringBuffer(),null);
        }

        static public StringBuffer resultSetToCsv (ResultSet resultSet, StringBuffer b, String[] columns) throws Exception {
                if (resultSet == null) { throw new Exception("ResultSet was null"); }
                if (b == null) { throw new Exception("The specified string buffer was null."); }

                // If no columns where given, then just assume the full set
                System.out.println("Debug A");
                if (columns == null) {
                        Set names = DbTool.toMap(resultSet,false).keySet();
                        columns = new String[names.size()];
                        int x = 0;
                        for (Iterator i = names.iterator(); i.hasNext(); ) {
                                columns[x++] = (String) i.next();
                        }
                }
                // Make sure that all of the specified columsn are available.
                else {
                        Set names = DbTool.toMap(resultSet,false).keySet();
                        for (int x = 0; x < columns.length; x++) {
                                if (! names.contains( columns[x] )) {
                                        throw new Exception("Specified columns is not available in the result set: '" + columns[x] + "'");
                                }
                        }
                }

                // Add the headers
                System.out.println("Debug B");
                for (int x = 0; x < columns.length; x++) {
                        b.append(columns[x]);
                        b.append(",");
                }
                b.deleteCharAt(b.length()-1);
                b.append("\n");

                Map rm = null;
                System.out.println("Debug C");
                do {
                        System.out.println("Debug D");
                        rm = DbTool.toMap(resultSet,false);
                        String col = null;
                        for (int x = 0; x < columns.length; x++) {
                                b.append(rm.get(columns[x]));
                                b.append(",");
                        }
                        b.deleteCharAt(b.length()-1);
                        b.append("\n");
                } while (resultSet.next());

                return b;
        }

        static public String[] csvLine (String line) throws Exception {
                try {
                        // Outlier
                        if (line == null || line.equals("")) { return new String[0]; }
        
                        ArrayList items = new ArrayList();
                        char[] c = line.toCharArray();
        
                        boolean quoted = false;
                        boolean tokenDone = false;
                        int TOKEN_START = 1;
                        int TOKEN_MIDDLE = 2;
                        int mode = TOKEN_START;
                        StringBuffer token = null;
                        for (int x = 0; x < c.length; x++) {
                                if (mode == TOKEN_START) {

                                        // If the previous token was quoted, then this char must
                                        // be a comma and we should skip it.
                                        if (quoted && c[x] == ',') { continue; }

                                        // If true, then this token is empty.  Insert an empty string
                                        // into 'items' and continue processing.
                                        if (c[x] == ',') {
                                                items.add("");
                                                continue;
                                        }
        
                                        // Otherwise, allocate space for a new token.
                                        token = new StringBuffer();
                                        quoted = false;
                                        tokenDone = false;
        
                                        // If true, then this token starts with a full quote, so ignore
                                        // the quote and mark state so we stop processing this token on
                                        // sight of a '"' instead of a ','
                                        if (c[x] == '"') {
                                                quoted = true;
                                        }
                                        // Otherwise, this is the first character for a "normal" token.
                                        else {
                                                token.append(c[x]);
                                        }
                                        mode = TOKEN_MIDDLE;
                                        continue;
                                }
                                if (mode == TOKEN_MIDDLE) {
        
                                        // There are only two conditions that can end a token (except for
                                        // end of line, which is handed outside this loop.
                                        if (c[x] == ',' && (!quoted)) { tokenDone = true; }
                                        if (c[x] == '"' && (quoted)) { tokenDone = true; }
        
                                        // If the token is finished, add the current token contents to "items"
                                        // and reset for another token.
                                        if (tokenDone) {
                                                items.add(token.toString());
                                                mode = TOKEN_START;
                                                token = null;
                                        }
                                        // Otherwise, this is just another character in the token; add it and
                                        // continue.
                                        else {
                                                token.append(c[x]);
                                        }
                                }
                        }
                        // There should always be one more token after running out of charaters (because
                        // the terminating "," is not contained on the line.  For example, a line containing
                        // '1,2,3' is {1,2,3}, whereas '1,2,3,' is '{1,2,3,}' (empty string as last element).
                        if (token != null) {
                                if (quoted) {
                                        // This is an error, since the line was not terminated by the end-quote.
                                        throw new RuntimeException("Line does not end in '\"'");
                                }
                                else {
                                        // OK, just add the data as the last item.
                                        items.add( token.toString() );
                                }
                        }
                        else {
                                if (! quoted) {
                                        items.add("");
                                }
                                else {
                                        // Do nothing because the last token was quoted, meaning we are done because the
                                        // last character was '"'
                                }
                        }

                        // Convert to String[] and return.
                        String[] result = new String[items.size()];
                        for (int x = 0; x < items.size(); x++) { result[x] = (String) items.get(x); }
                        return result;

                }
                catch (Exception e) {
                        throw new Exception("Error processing line: '"+line+"'",e);
                }
        }

}

---