View Javadoc
1   /*
2   Copyright (c) 2007 Health Market Science, Inc.
3   
4   Licensed under the Apache License, Version 2.0 (the "License");
5   you may not use this file except in compliance with the License.
6   You may obtain a copy of the License at
7   
8       http://www.apache.org/licenses/LICENSE-2.0
9   
10  Unless required by applicable law or agreed to in writing, software
11  distributed under the License is distributed on an "AS IS" BASIS,
12  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  See the License for the specific language governing permissions and
14  limitations under the License.
15  */
16  
17  package com.healthmarketscience.jackcess.util;
18  
19  import java.io.BufferedWriter;
20  import java.io.File;
21  import java.io.FileWriter;
22  import java.io.IOException;
23  import java.util.ArrayList;
24  import java.util.Collection;
25  import java.util.HashSet;
26  import java.util.Iterator;
27  import java.util.List;
28  import java.util.regex.Pattern;
29  
30  import com.healthmarketscience.jackcess.Column;
31  import com.healthmarketscience.jackcess.Cursor;
32  import com.healthmarketscience.jackcess.CursorBuilder;
33  import com.healthmarketscience.jackcess.Database;
34  import com.healthmarketscience.jackcess.Row;
35  import com.healthmarketscience.jackcess.impl.ByteUtil;
36  
37  /**
38   * Utility class for exporting tables from an Access database to other
39   * formats.  See the {@link Builder} for convenient configuration of the
40   * export functionality.  Note that most scenarios for customizing output data
41   * can be handled by implementing a custom {@link ExportFilter}.
42   * 
43   * @author Frank Gerbig
44   * @usage _general_class_
45   */
46  public class ExportUtil {
47  
48    public static final String DEFAULT_DELIMITER = ",";
49    public static final char DEFAULT_QUOTE_CHAR = '"';
50    public static final String DEFAULT_FILE_EXT = "csv";
51  
52  
53    private ExportUtil() {
54    }
55  
56    /**
57     * Copy all tables into new delimited text files <br>
58     * Equivalent to: {@code exportAll(db, dir, "csv");}
59     * 
60     * @param db
61     *          Database the table to export belongs to
62     * @param dir
63     *          The directory where the new files will be created
64     * 
65     * @see #exportAll(Database,File,String)
66     * @see Builder
67     */
68    public static void exportAll(Database db, File dir)
69        throws IOException {
70      exportAll(db, dir, DEFAULT_FILE_EXT);
71    }
72  
73    /**
74     * Copy all tables into new delimited text files <br>
75     * Equivalent to: {@code exportFile(db, name, f, false, null, '"',
76     * SimpleExportFilter.INSTANCE);}
77     * 
78     * @param db
79     *          Database the table to export belongs to
80     * @param dir
81     *          The directory where the new files will be created
82     * @param ext
83     *          The file extension of the new files
84     * 
85     * @see #exportFile(Database,String,File,boolean,String,char,ExportFilter)
86     * @see Builder
87     */
88    public static void exportAll(Database db, File dir,
89        String ext) throws IOException {
90      for (String tableName : db.getTableNames()) {
91        exportFile(db, tableName, new File(dir, tableName + "." + ext), false,
92            DEFAULT_DELIMITER, DEFAULT_QUOTE_CHAR, SimpleExportFilter.INSTANCE);
93      }
94    }
95  
96    /**
97     * Copy all tables into new delimited text files <br>
98     * Equivalent to: {@code exportFile(db, name, f, false, null, '"',
99     * SimpleExportFilter.INSTANCE);}
100    * 
101    * @param db
102    *          Database the table to export belongs to
103    * @param dir
104    *          The directory where the new files will be created
105    * @param ext
106    *          The file extension of the new files
107    * @param header
108    *          If <code>true</code> the first line contains the column names
109    * 
110    * @see #exportFile(Database,String,File,boolean,String,char,ExportFilter)
111    * @see Builder
112    */
113   public static void exportAll(Database db, File dir,
114       String ext, boolean header)
115       throws IOException {
116     for (String tableName : db.getTableNames()) {
117       exportFile(db, tableName, new File(dir, tableName + "." + ext), header,
118           DEFAULT_DELIMITER, DEFAULT_QUOTE_CHAR, SimpleExportFilter.INSTANCE);
119     }
120   }
121 
122   /**
123    * Copy all tables into new delimited text files <br>
124    * Equivalent to: {@code exportFile(db, name, f, false, null, '"',
125    * SimpleExportFilter.INSTANCE);}
126    * 
127    * @param db
128    *          Database the table to export belongs to
129    * @param dir
130    *          The directory where the new files will be created
131    * @param ext
132    *          The file extension of the new files
133    * @param header
134    *          If <code>true</code> the first line contains the column names
135    * @param delim
136    *          The column delimiter, <code>null</code> for default (comma)
137    * @param quote
138    *          The quote character
139    * @param filter
140    *          valid export filter
141    * 
142    * @see #exportFile(Database,String,File,boolean,String,char,ExportFilter)
143    * @see Builder
144    */
145   public static void exportAll(Database db, File dir,
146       String ext, boolean header, String delim,
147       char quote, ExportFilter filter)
148       throws IOException {
149     for (String tableName : db.getTableNames()) {
150       exportFile(db, tableName, new File(dir, tableName + "." + ext), header,
151           delim, quote, filter);
152     }
153   }
154 
155   /**
156    * Copy a table into a new delimited text file <br>
157    * Equivalent to: {@code exportFile(db, name, f, false, null, '"',
158    * SimpleExportFilter.INSTANCE);}
159    * 
160    * @param db
161    *          Database the table to export belongs to
162    * @param tableName
163    *          Name of the table to export
164    * @param f
165    *          New file to create
166    * 
167    * @see #exportFile(Database,String,File,boolean,String,char,ExportFilter)
168    * @see Builder
169    */
170   public static void exportFile(Database db, String tableName,
171       File f) throws IOException {
172     exportFile(db, tableName, f, false, DEFAULT_DELIMITER, DEFAULT_QUOTE_CHAR, 
173         SimpleExportFilter.INSTANCE);
174   }
175 
176   /**
177    * Copy a table into a new delimited text file <br>
178    * Nearly equivalent to: {@code exportWriter(db, name, new BufferedWriter(f),
179    * header, delim, quote, filter);}
180    * 
181    * @param db
182    *          Database the table to export belongs to
183    * @param tableName
184    *          Name of the table to export
185    * @param f
186    *          New file to create
187    * @param header
188    *          If <code>true</code> the first line contains the column names
189    * @param delim
190    *          The column delimiter, <code>null</code> for default (comma)
191    * @param quote
192    *          The quote character
193    * @param filter
194    *          valid export filter
195    * 
196    * @see #exportWriter(Database,String,BufferedWriter,boolean,String,char,ExportFilter)
197    * @see Builder
198    */
199   public static void exportFile(Database db, String tableName,
200       File f, boolean header, String delim, char quote,
201       ExportFilter filter) throws IOException {
202     BufferedWriter out = null;
203     try {
204       out = new BufferedWriter(new FileWriter(f));
205       exportWriter(db, tableName, out, header, delim, quote, filter);
206       out.close();
207     } finally {
208       ByteUtil.closeQuietly(out);
209     }
210   }
211 
212   /**
213    * Copy a table in this database into a new delimited text file <br>
214    * Equivalent to: {@code exportWriter(db, name, out, false, null, '"',
215    * SimpleExportFilter.INSTANCE);}
216    * 
217    * @param db
218    *          Database the table to export belongs to
219    * @param tableName
220    *          Name of the table to export
221    * @param out
222    *          Writer to export to
223    * 
224    * @see #exportWriter(Database,String,BufferedWriter,boolean,String,char,ExportFilter)
225    * @see Builder
226    */
227   public static void exportWriter(Database db, String tableName,
228       BufferedWriter out) throws IOException {
229     exportWriter(db, tableName, out, false, DEFAULT_DELIMITER, 
230                  DEFAULT_QUOTE_CHAR, SimpleExportFilter.INSTANCE);
231   }
232 
233   /**
234    * Copy a table in this database into a new delimited text file. <br>
235    * Equivalent to: {@code exportWriter(Cursor.createCursor(db.getTable(tableName)), out, header, delim, quote, filter);}
236    * 
237    * @param db
238    *          Database the table to export belongs to
239    * @param tableName
240    *          Name of the table to export
241    * @param out
242    *          Writer to export to
243    * @param header
244    *          If <code>true</code> the first line contains the column names
245    * @param delim
246    *          The column delimiter, <code>null</code> for default (comma)
247    * @param quote
248    *          The quote character
249    * @param filter
250    *          valid export filter
251    * 
252    * @see #exportWriter(Cursor,BufferedWriter,boolean,String,char,ExportFilter)
253    * @see Builder
254    */
255   public static void exportWriter(Database db, String tableName,
256       BufferedWriter out, boolean header, String delim,
257       char quote, ExportFilter filter)
258       throws IOException 
259   {
260     exportWriter(CursorBuilder.createCursor(db.getTable(tableName)), out, header,
261                  delim, quote, filter);
262   }
263 
264   /**
265    * Copy a table in this database into a new delimited text file.
266    * 
267    * @param cursor
268    *          Cursor to export
269    * @param out
270    *          Writer to export to
271    * @param header
272    *          If <code>true</code> the first line contains the column names
273    * @param delim
274    *          The column delimiter, <code>null</code> for default (comma)
275    * @param quote
276    *          The quote character
277    * @param filter
278    *          valid export filter
279    *
280    * @see Builder
281    */
282   public static void exportWriter(Cursor cursor,
283       BufferedWriter out, boolean header, String delim,
284       char quote, ExportFilter filter)
285       throws IOException 
286   {
287     String delimiter = (delim == null) ? DEFAULT_DELIMITER : delim;
288 
289     // create pattern which will indicate whether or not a value needs to be
290     // quoted or not (contains delimiter, separator, or newline)
291     Pattern needsQuotePattern = Pattern.compile(
292         "(?:" + Pattern.quote(delimiter) + ")|(?:" + 
293         Pattern.quote("" + quote) + ")|(?:[\n\r])");
294 
295     List<? extends Column> origCols = cursor.getTable().getColumns();
296     List<Column> columns = new ArrayList<Column>(origCols);
297     columns = filter.filterColumns(columns);
298 
299     Collection<String> columnNames = null;
300     if(!origCols.equals(columns)) {
301 
302       // columns have been filtered
303       columnNames = new HashSet<String>();
304       for (Column c : columns) {
305         columnNames.add(c.getName());
306       }
307     }
308 
309     // print the header row (if desired)
310     if (header) {
311       for (Iterator<Column> iter = columns.iterator(); iter.hasNext();) {
312 
313         writeValue(out, iter.next().getName(), quote, needsQuotePattern);
314 
315         if (iter.hasNext()) {
316           out.write(delimiter);
317         }
318       }
319       out.newLine();
320     }
321 
322     // print the data rows
323     Object[] unfilteredRowData = new Object[columns.size()];
324     Row row;
325     while ((row = cursor.getNextRow(columnNames)) != null) {
326 
327       // fill raw row data in array
328       for (int i = 0; i < columns.size(); i++) {
329         unfilteredRowData[i] = columns.get(i).getRowValue(row);
330       }
331 
332       // apply filter
333       Object[] rowData = filter.filterRow(unfilteredRowData);
334       if(rowData == null) {
335         continue;
336       }
337 
338       // print row
339       for (int i = 0; i < columns.size(); i++) {
340 
341         Object obj = rowData[i];
342         if(obj != null) {
343 
344           String value = null;
345           if(obj instanceof byte[]) {
346 
347             value = ByteUtil.toHexString((byte[])obj);
348 
349           } else {
350 
351             value = String.valueOf(rowData[i]);
352           }
353 
354           writeValue(out, value, quote, needsQuotePattern);
355         }
356 
357         if (i < columns.size() - 1) {
358           out.write(delimiter);
359         }
360       }
361 
362       out.newLine();
363     }
364 
365     out.flush();
366   }
367 
368   private static void writeValue(BufferedWriter out, String value, char quote,
369                                  Pattern needsQuotePattern) 
370     throws IOException
371   {
372     if(!needsQuotePattern.matcher(value).find()) {
373 
374       // no quotes necessary
375       out.write(value);
376       return;
377     }
378 
379     // wrap the value in quotes and handle internal quotes
380     out.write(quote);
381     for (int i = 0; i < value.length(); ++i) {
382       char c = value.charAt(i);
383 
384       if (c == quote) {
385         out.write(quote);
386       }
387       out.write(c);
388     }
389     out.write(quote);
390   }
391 
392 
393   /**
394    * Builder which simplifies configuration of an export operation.
395    */
396   public static class Builder
397   {
398     private Database _db;
399     private String _tableName;
400     private String _ext = DEFAULT_FILE_EXT;
401     private Cursor _cursor;
402     private String _delim = DEFAULT_DELIMITER;
403     private char _quote = DEFAULT_QUOTE_CHAR;
404     private ExportFilter _filter = SimpleExportFilter.INSTANCE;
405     private boolean _header;
406 
407     public Builder(Database db) {
408       this(db, null);
409     }
410 
411     public Builder(Database db, String tableName) {
412       _db = db;
413       _tableName = tableName;
414     }
415 
416     public Builder(Cursor cursor) {
417       _cursor = cursor;
418     }
419 
420     public Builder setDatabase(Database db) {
421       _db = db;
422       return this;
423     }
424 
425     public Builder setTableName(String tableName) {
426       _tableName = tableName;
427       return this;
428     }
429 
430     public Builder setCursor(Cursor cursor) {
431       _cursor = cursor;
432       return this;
433     }
434 
435     public Builder setDelimiter(String delim) {
436       _delim = delim;
437       return this;
438     }
439 
440     public Builder setQuote(char quote) {
441       _quote = quote;
442       return this;
443     }
444 
445     public Builder setFilter(ExportFilter filter) {
446       _filter = filter;
447       return this;
448     }
449 
450     public Builder setHeader(boolean header) {
451       _header = header;
452       return this;
453     }
454 
455     public Builder setFileNameExtension(String ext) {
456       _ext = ext;
457       return this;
458     }
459 
460     /**
461      * @see ExportUtil#exportAll(Database,File,String,boolean,String,char,ExportFilter)
462      */
463     public void exportAll(File dir) throws IOException {
464       ExportUtil.exportAll(_db, dir, _ext, _header, _delim, _quote, _filter);
465     }
466 
467     /**
468      * @see ExportUtil#exportFile(Database,String,File,boolean,String,char,ExportFilter)
469      */
470     public void exportFile(File f) throws IOException {
471       ExportUtil.exportFile(_db, _tableName, f, _header, _delim, _quote,
472                             _filter);
473     }
474 
475     /**
476      * @see ExportUtil#exportWriter(Database,String,BufferedWriter,boolean,String,char,ExportFilter)
477      * @see ExportUtil#exportWriter(Cursor,BufferedWriter,boolean,String,char,ExportFilter)
478      */
479     public void exportWriter(BufferedWriter writer) throws IOException {
480       if(_cursor != null) {
481         ExportUtil.exportWriter(_cursor, writer, _header, _delim, 
482                                 _quote, _filter);
483       } else {
484         ExportUtil.exportWriter(_db, _tableName, writer, _header, _delim, 
485                                 _quote, _filter);
486       }
487     }
488   }
489 
490 }