CsvWriter


1   /**
2       Copyright (C) 2002-2003  Together
3   
4       This library is free software; you can redistribute it and/or
5       modify it under the terms of the GNU Lesser General Public
6       License as published by the Free Software Foundation; either
7       version 2.1 of the License, or (at your option) any later version.
8   
9       This library is distributed in the hope that it will be useful,
10      but WITHOUT ANY WARRANTY; without even the implied warranty of
11      MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
12      Lesser General Public License for more details.
13  
14      You should have received a copy of the GNU Lesser General Public
15      License along with this library; if not, write to the Free Software
16      Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
17  
18  */
19  
20  package org.relique.jdbc.csv;
21  
22  import java.io.File  ;
23  import java.io.IOException  ;
24  import java.sql.SQLException  ;
25  import java.util.ArrayList  ;
26  import java.util.Vector  ;
27  
28  
29  
30  
31  /**
32   * This class is a helper class that handles the reading and parsing of data
33   * from a .csv file.
34   *
35   * @author     Sinisa Milosevic
36   * @author     Zoran Milakovic
37   */
38  
39  public class CsvWriter
40  {
41    private CsvRandomAccessFile randomCsvFile;
42    private File   file;
43    private String  [] columnNames;
44    private String  [] columnTypes;
45    private String  [] columns;
46    private java.lang.String   buf = null;
47    private char separator = CsvDriver.DEFAULT_SEPARATOR;
48    private long maxFileSize = CsvDriver.DEFAULT_FILE_MAXSIZE;
49    private String   extension = CsvDriver.DEFAULT_EXTENSION;
50    private String   tableName;
51    private String   fileName;
52    private int counter;
53    private long current;
54    private long endLine;
55    private String   charset;
56    private String   QUOTE = "\"";
57    private boolean useQuotesEscape = true;
58  
59  
60    /**
61     * Used with statement.
62     *
63     * @param fileName
64     * @param separator
65     * @param extension
66     * @param maxFileSize
67     * @throws java.lang.Exception
68     */
69     public CsvWriter(
70        String   fileName,
71        char separator,
72        String   extension,
73        long maxFileSize,
74        String   charset,
75        boolean useQuotes,
76        boolean useQuotesEscape
77        )
78        throws java.lang.Exception  
79    {
80      this.separator = separator;
81      this.extension = extension;
82      this.maxFileSize = maxFileSize;
83      if(!useQuotes) this.QUOTE = "";
84      if( fileName != null ) {
85        this.fileName = fileName;
86        this.randomCsvFile = new CsvRandomAccessFile(fileName, charset);
87        this.file = new File  (fileName);
88        String   headerLine = this.randomCsvFile.readCsvLine();      
89        columnNames = parseCsvLineAsHeader(headerLine);
90      }
91      this.charset = charset;
92      this.useQuotesEscape = useQuotesEscape;
93      counter = 1;
94      current = 0;
95      endLine=0;
96      
97    }
98  
99  
100   /**
101    * When use split files, this is used when file name is changed.
102    * @param name
103    * @throws Exception
104    */
105   public void setFileName(String   name) throws Exception   {
106     this.fileName=name;
107     if( this.randomCsvFile != null )
108       this.randomCsvFile.close();
109     this.file = new File  ( this.fileName );
110     this.randomCsvFile = new CsvRandomAccessFile(this.fileName,charset);
111   }
112 
113 
114   public void fillTableColumnNames() throws java.lang.Exception   {
115     String   headerLine = this.randomCsvFile.readCsvLine();
116     this.columnNames = parseCsvLineAsHeader(headerLine);
117   }
118 
119   /**
120    * Gets the columnNames attribute of the CsvReader object
121    *
122    * @return The columnNames value
123    * @since
124    */
125   public String  [] getColumnNames()
126   {
127     return columnNames;
128   }
129 
130   private String   getNextFileName(String   currentFileName) {
131     String   newName = "";
132     String   number = "";
133 //name without extension
134     String   currentFileExtension = currentFileName.substring(currentFileName.lastIndexOf("."), currentFileName.length());
135     currentFileName = currentFileName.substring(0, currentFileName.lastIndexOf("."));
136     if( currentFileExtension.endsWith(CsvDriver.FILE_NAME_EXT) ) {
137        number += currentFileName.substring(currentFileName.length()-3, currentFileName.length());
138        long num = Long.valueOf(number).longValue()+1;
139        if( num >= 100 && num < 1000 )
140          number = String.valueOf( num );
141        else if ( num >= 10 && num < 100 )
142          number = "0"+String.valueOf( num );
143        else if ( num > 1 && num < 10 )
144          number = "00"+String.valueOf( num );
145        currentFileName = currentFileName.substring(0, currentFileName.length()-3);
146        newName = currentFileName + number + currentFileExtension;
147     } else {
148       newName = currentFileName.toUpperCase() + "001" + this.extension + CsvDriver.FILE_NAME_EXT;
149     }
150     return newName;
151   }
152 
153 
154   public String   getTableName() {
155     if(tableName != null)
156       return tableName;
157 
158     int lastSlash = 0;
159     for(int i = fileName.length()-1; i >= 0; i--)
160       if(fileName.charAt(i) == '/' || fileName.charAt(i) == '\\') {
161     lastSlash = i;
162     break;
163       }
164       tableName = fileName.substring(lastSlash+1, fileName.length() - 4);
165       return tableName;
166   }
167 
168   /**
169    * Get the value of the column at the specified index.
170    *
171    * @param  columnIndex  Description of Parameter
172    * @return              The column value
173    * @since
174    */
175 
176   public String   getColumn(int columnIndex)
177   {
178     return columns[columnIndex];
179   }
180 
181   /**
182    * Get value from column at specified name.
183    * If the column name is not found, throw an error.
184    *
185    * @param  columnName     Description of Parameter
186    * @return                The column value
187    * @exception  SQLException  Description of Exception
188    * @since
189    */
190 
191   public String   getColumn(String   columnName) throws SQLException  
192   {
193     for (int loop = 0; loop < columnNames.length; loop++)
194     {
195       if (columnName.equalsIgnoreCase(columnNames[loop]) || columnName.equalsIgnoreCase(getTableName() + "." + columnNames[loop]))
196       {
197       return getColumn(loop);
198     }
199     }
200     throw new SQLException  ("Column '" + columnName + "' not found.");
201   }
202 
203 
204   /**
205    *Description of the Method
206    *
207    * @return                Description of the Returned Value
208    * @exception  SQLException  Description of Exception
209    * @since
210    */
211   private long lastCurrent = -1;
212   public boolean next() throws SQLException  , IOException   {
213     columns = new String  [columnNames.length];
214     String   dataLine = null;
215     if( lastCurrent == -1 ) {
216       current = randomCsvFile.getFilePointer();
217     } else {
218       current = lastCurrent;
219     }
220 
221     try {
222       if (buf != null) {
223         // The buffer is not empty yet, so use this first.
224         dataLine = buf;
225         buf = null;
226       } else {
227         // read new line of data from input.
228         if( lastCurrent != -1 )
229           randomCsvFile.seek(lastCurrent);
230         dataLine = this.randomCsvFile.readCsvLine();
231       }
232       if (dataLine == null) {
233         String   nextFileName = getNextFileName(this.fileName);
234           if( new File  (nextFileName).exists() ) {
235             this.fileName = nextFileName;
236             if( randomCsvFile != null )
237                 randomCsvFile.close();
238             randomCsvFile = new CsvRandomAccessFile(this.fileName, charset);
239             counter = 1;
240             current = 0;
241             endLine=0;
242             //skip header            
243             dataLine = randomCsvFile.readCsvLine();
244           } else {
245             randomCsvFile.close();
246             return false;
247           }
248       }
249 
250     } catch (IOException   e) {
251       throw new SQLException  (e.toString());
252     }
253     columns = parseCsvLine(dataLine);
254     endLine = randomCsvFile.getFilePointer();
255     return true;
256   }
257 
258 
259   /**
260    *Description of the Method
261    *
262    * @since
263    */
264   public void close()
265   {
266     try
267     {
268       this.file = null;
269       this.randomCsvFile.close();
270       buf = null;
271     }
272     catch (Exception   e)
273     {
274     }
275   }
276 
277 
278   /**
279   *
280   * @param line
281   * @return array with values or column names.
282   * @throws SQLException
283   */
284  protected String  [] parseCsvLine(String   line) throws SQLException  
285  {
286    ArrayList   values = new ArrayList  ();
287    boolean inQuotedString = false;
288    String   value = "";
289    String   orgLine = line;
290    int currentPos = 0;
291    int fullLine = 0;
292    int currentColumn = 0;
293    char currentChar;
294 
295      line += separator;
296      long lineLength = line.length();
297      while (fullLine == 0) {
298        currentPos = 0;
299        while (currentPos < lineLength) {
300 
301          //handle BINARY columns
302         if( !(this.columnTypes.length <= currentColumn ) ) {
303          if (this.columnTypes[currentColumn].equals(CsvDriver.BINARY_TYPE)) {
304            String   binaryValue = "";
305            currentChar = line.charAt(currentPos);
306            if (currentChar == ',') {
307              values.add(binaryValue); //binary value is null;
308              currentPos ++;
309            }
310            else if (currentChar == '"') {
311              if (line.charAt(currentPos + 1) == '"') {
312                values.add(binaryValue); //binary value is null
313                currentPos = currentPos + 3;
314              }
315              else {
316                // take all until next separator, and that is value
317                // do not insert BinaryObject+index into line, just set right currentPos
318                // and insert value into vector
319                // binary value is always beteween quotes (")
320                binaryValue = line.substring(currentPos);
321                binaryValue = binaryValue.substring(1,
322                                                    binaryValue.indexOf(separator) -
323                                                    1);
324                values.add(binaryValue);
325                currentPos += binaryValue.length() + 3;
326              }
327            }
328            //set currentColumn++
329            currentColumn++;
330            continue;
331          }
332         } else {
333           throw new SQLException  ("Invalid csv format : file = "+new File  (fileName).getAbsolutePath()+", line = "+line);
334        }
335 
336 
337 
338 //parse one by one character
339          currentChar = line.charAt(currentPos);
340          if (value.length() == 0 && currentChar == '"' && !inQuotedString) {
341 //enter here if we are at start of column value
342            currentPos++;
343            inQuotedString = true;
344            continue;
345          }
346 
347          if (currentChar == '"') {
348 //get next character
349            char nextChar = line.charAt(currentPos + 1);
350 //if we have "", consider it as ", and add it to value
351            if (nextChar == '"') {
352              value += currentChar;
353              currentPos++;
354            }
355            else {
356 //enter here if we are at end of column value
357              if (!inQuotedString) {
358                throw new SQLException  ("Unexpected '\"' in position " +
359                                       currentPos + ". Line=" + orgLine);
360              }
361              if (inQuotedString && nextChar != separator) {
362                throw new SQLException  ("Expecting " + separator +
363                                       " in position " + (currentPos + 1) +
364                                       ". Line=" + orgLine);
365              }
366 
367 //set currentPos to comma after value
368              currentPos++;
369 //if value is empty string between double quotes consider it as empty string
370 //else if value is empty string between commas consider it as null value
371              values.add(value);
372              currentColumn++;
373              value = "";
374              inQuotedString = false;
375            }
376          }
377 
378          else {
379 //when we are at end of column value, and value is not inside of double quotes
380            if (currentChar == separator) {
381 //when have separator in data
382              if (inQuotedString) {
383                value += currentChar;
384              }
385              else {
386 //if value is empty string between double quotes consider it as empty string
387 //else if value is empty string between commas consider it as null value
388                 if( value.equals("") )
389                   value = null;
390                values.add(value);
391                currentColumn++;
392                value = "";
393              }
394            }
395            else {
396              value += currentChar;
397            }
398          }
399 
400          currentPos++;
401        } //end while
402 
403        if (inQuotedString) {
404          // Remove extra , added at start
405          value = value.substring(0, value.length() - 1);
406          try {
407            line = randomCsvFile.readCsvLine();
408          }
409          catch (IOException   e) {
410            throw new SQLException  (e.toString());
411          }
412        }
413        else {
414          fullLine = 1;
415        }
416 
417      }// end while( fullLine == 0 )
418    String  [] retVal = new String  [values.size()];
419    values.toArray(retVal);
420 
421    return retVal;
422  }
423 
424 
425  /**
426   *
427   * @param line
428   * @return array with values or column names.
429   * @throws SQLException
430   */
431  protected String  [] parseCsvLineAsHeader(String   line) throws SQLException  
432  {
433 //JOptionPane.showMessageDialog(null,"line = "+line);     
434    Vector   values = new Vector  ();
435    ArrayList   columnTypesList = new ArrayList  ();
436    boolean inQuotedString = false;
437    String   value = "";
438    String   orgLine = line;
439    int currentPos = 0;
440    int fullLine = 0;
441 
442    while (fullLine == 0) {
443      currentPos = 0;
444      line += separator;
445      while (currentPos < line.length()) {
446        char currentChar = line.charAt(currentPos);          
447        if (value.length() == 0 && currentChar == '"' && !inQuotedString) {
448          currentPos++;
449          inQuotedString = true;
450          continue;
451        }
452        if (currentChar == '"') {
453          char nextChar = line.charAt(currentPos + 1);
454          if (nextChar == '"') {
455            value += currentChar;
456            currentPos++;
457          }
458          else {
459            if (!inQuotedString) {
460              throw new SQLException  ("Unexpected '\"' in position " +
461                                     currentPos + ". Line=" + orgLine);
462            }           
463            if (inQuotedString && nextChar != separator) {  
464              throw new SQLException  ("Expecting " + separator + " in position " +
465                                     (currentPos + 1) + ". Line=" + orgLine);
466            }
467            if (value.endsWith("-"+CsvDriver.BINARY_TYPE)) {
468              columnTypesList.add(CsvDriver.BINARY_TYPE);
469              value = value.substring(0,value.indexOf("-"+CsvDriver.BINARY_TYPE));
470            }
471            else
472              columnTypesList.add(CsvDriver.VARCHAR_TYPE);          
473            values.add(value);
474            value = "";
475            inQuotedString = false;
476            currentPos++;
477          }
478        }
479        else {
480          if (currentChar == separator) {
481            if (inQuotedString) {
482              value += currentChar;
483            }
484            else {
485              if (value.endsWith("-"+CsvDriver.BINARY_TYPE)) {
486                columnTypesList.add(CsvDriver.BINARY_TYPE);
487                value = value.substring(0,
488                                        value.indexOf("-"+CsvDriver.BINARY_TYPE));
489              }
490              else
491                columnTypesList.add(CsvDriver.VARCHAR_TYPE);
492              values.add(value);
493              value = "";
494            }
495          }
496          else {
497            value += currentChar;
498          }
499        }
500        currentPos++;
501      }
502      if (inQuotedString) {
503        // Remove extra , added at start
504        value = value.substring(0, value.length() - 1);
505        try {
506          line = randomCsvFile.readCsvLine();
507        }
508        catch (IOException   e) {
509          throw new SQLException  (e.toString());
510        }
511      }
512      else {
513        fullLine = 1;
514      }
515    }
516    String  [] retVal = new String  [values.size()];
517    values.copyInto(retVal);
518 
519    this.columnTypes = new String  [columnTypesList.size()];
520    columnTypesList.toArray(columnTypes);
521 
522    return retVal;
523 
524  }
525 
526 
527 
528 
529   protected boolean newLine(String  [] colNames, String  [] colValues) throws IOException   {
530     String   newLine="";
531     boolean more = true;
532     boolean createNewOutput = false;
533 //find out if file size is out of range, and if so create new file
534     while(more) {
535       if( (this.maxFileSize != -1) && (this.file.length() > this.maxFileSize) ) {
536         String   newTableName = this.getNextFileName(this.fileName);
537         this.fileName = newTableName;
538         this.createExtTable(this.columnNames, this.columnTypes, newTableName);
539       } else {
540         createNewOutput = true;
541         more = false;
542       }
543     }
544     try {
545       if (createNewOutput) {
546         this.randomCsvFile.close();
547         this.randomCsvFile = new CsvRandomAccessFile(this.fileName, charset);
548       }
549     }catch(Exception   e) {
550       e.printStackTrace();
551     }
552 
553     for(int i=0;i<columnNames.length; i++) {
554       boolean find = false;
555       for(int j=0;j<colNames.length; j++) {
556         if(colNames[j].equalsIgnoreCase(columnNames[i])) {
557           if(colValues[j]==null)
558             newLine=newLine+separator;
559           else  {
560             if(!this.useQuotesEscape)
561                 colValues[j] = Utils.replaceAll(colValues[j], CsvSqlParser.DOUBLE_QUOTE_ESCAPE, "\"");
562             newLine=newLine+this.QUOTE+colValues[j]+this.QUOTE+separator;
563           }
564           find = true;
565         }
566       }
567       if(!find)
568         newLine=newLine+separator;
569     }
570     if(!newLine.equals(""))
571       newLine=newLine.substring(0,newLine.length()-1);
572 
573     long l = this.randomCsvFile.length();
574     this.randomCsvFile.seek(l);
575     this.write(this.randomCsvFile,"\n"+newLine);
576     this.randomCsvFile.close();
577 
578     return true;
579   }
580 
581   protected boolean createTable(String  [] colNames, String   table) throws IOException   {
582     String   newLine="";
583     for(int i=0;i<colNames.length; i++) {
584       newLine=newLine+this.QUOTE+colNames[i]+this.QUOTE+separator;
585     }
586     if(!newLine.equals(""))
587       newLine=newLine.substring(0,newLine.length()-1);
588 
589     this.fileName = table;
590 
591     this.file = new File  ( this.fileName );
592 
593       if( this.randomCsvFile != null )
594         this.randomCsvFile.close();
595       this.randomCsvFile = new CsvRandomAccessFile(fileName,charset);
596       this.write(this.randomCsvFile,newLine);
597       this.randomCsvFile.close();
598 
599     return true;
600   }
601 
602   protected boolean createExtTable(String  [] colNames, String  [] colTypes, String   table) throws IOException   {
603     String   newLine="";
604     for(int i=0;i<colNames.length; i++) {
605       if(colTypes[i].equals(CsvDriver.BINARY_TYPE))
606         newLine=newLine+this.QUOTE+colNames[i]+"-"+colTypes[i]+this.QUOTE+separator;
607       else
608         newLine=newLine+this.QUOTE+colNames[i]+this.QUOTE+separator;
609     }
610     if(!newLine.equals(""))
611       newLine=newLine.substring(0,newLine.length()-1);
612 
613     this.fileName = table;
614 
615     this.file = new File  ( this.fileName );
616 
617     if( !this.file.exists() ) {
618       CsvRandomAccessFile temp = new CsvRandomAccessFile(this.fileName,charset);
619       this.write(temp,newLine);
620       temp.close();
621     }
622 
623     return true;
624   }
625 
626 
627   protected boolean updateFields(
628       String  [] colNames,
629       String  [] colValues,
630       String  [] colWhereNames,
631       String  [] colWhereValues
632       ) throws IOException  , SQLException   {
633 
634     boolean isUpdated=false;
635 
636     while( next() ) {
637       isUpdated=false;
638       counter++;
639       boolean find = false;
640       out:
641       for(int i=0; i<colWhereNames.length; i++) {
642 //compare values
643         if( ! Utils.compareValues( getColumn(colWhereNames[i]), colWhereValues[i] ) )
644           break out;
645         if(i==(colWhereNames.length-1)) {
646           find = true;
647         }
648       }
649 //if there is no where clause
650       if( colWhereNames.length == 0 )
651         find = true;
652 
653 //go to next line
654       lastCurrent = randomCsvFile.getFilePointer();
655       if(find) {
656         for(int i=0; i < columnNames.length; i++) {
657           for(int j=0; j<colNames.length; j++) {
658             if(colNames[j].equalsIgnoreCase(columnNames[i])) {
659               if(!this.useQuotesEscape)
660                 colValues[j] = Utils.replaceAll(colValues[j], CsvSqlParser.DOUBLE_QUOTE_ESCAPE, "\"");
661               columns[i]=colValues[j];
662             }
663           }
664         }
665         String   updatedLine = "";
666         for(int i=0; i<columns.length; i++) {
667           if(columns[i]==null)
668             updatedLine=updatedLine+separator;
669           else {
670             updatedLine=updatedLine+this.QUOTE+columns[i]+this.QUOTE+separator;
671           }
672 
673         }
674         if(!updatedLine.equals("")) {
675           randomCsvFile.seek(endLine);
676           String   line="";
677           String   newLine="";
678 
679           while(( newLine=randomCsvFile.readCsvLine())!=null ){
680             line+=newLine+"\n";
681           }
682           if(line!=null) {
683             if(!line.equals(""))
684               line=line.substring(0,line.length()-1);
685           }
686           updatedLine=updatedLine.substring(0,updatedLine.length()-1);
687           randomCsvFile.seek(current);
688           if(Utils.isUTF16(this.charset)) 
689              this.write(randomCsvFile,"\n"+updatedLine);
690           else 
691              this.write(randomCsvFile,updatedLine);
692           //go to next line
693           lastCurrent = randomCsvFile.getFilePointer();
694           if( randomCsvFile.getFilePointer() != randomCsvFile.length() ) {
695               this.write(randomCsvFile,"\n");
696             //go to next line
697             lastCurrent = randomCsvFile.getFilePointer();
698             if(line!=null){
699               if(!line.equals(""))
700                   this.write(randomCsvFile,line);
701               randomCsvFile.setLength(randomCsvFile.getFilePointer());
702             }
703           }
704           isUpdated = false;
705         }
706       }
707     }
708     return isUpdated;
709   }
710   
711   /**
712    * Write to file using specified encoding of string msg.
713    * @param file
714    * @param msg
715    */
716   private void write(CsvRandomAccessFile file,String   line) throws IOException   {
717       try {
718         if(Utils.isUTF16(this.charset)) {            
719           if(file.length() == 0)
720               file.write(line.getBytes(this.charset),0,line.getBytes(this.charset).length);
721           else
722               file.write(line.getBytes(this.charset),2,line.getBytes(this.charset).length-2);
723         } else {
724             if(this.charset != null)
725                 file.write(line.getBytes(charset));
726             else 
727                 file.write(line.getBytes());
728         }
729     } catch (IOException   e) {
730         throw e;
731     }
732   }
733   
734   
735   
736   
737 
738 }
739
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags