1 | /* |
2 | * Copyright 2004-2014 H2 Group. Multiple-Licensed under the MPL 2.0, |
3 | * and the EPL 1.0 (http://h2database.com/html/license.html). |
4 | * Initial Developer: H2 Group |
5 | */ |
6 | package org.h2.tools; |
7 | |
8 | import java.io.BufferedInputStream; |
9 | import java.io.BufferedOutputStream; |
10 | import java.io.BufferedReader; |
11 | import java.io.BufferedWriter; |
12 | import java.io.IOException; |
13 | import java.io.InputStream; |
14 | import java.io.InputStreamReader; |
15 | import java.io.OutputStream; |
16 | import java.io.OutputStreamWriter; |
17 | import java.io.Reader; |
18 | import java.io.Writer; |
19 | import java.sql.Connection; |
20 | import java.sql.ResultSet; |
21 | import java.sql.ResultSetMetaData; |
22 | import java.sql.SQLException; |
23 | import java.sql.Statement; |
24 | import java.sql.Types; |
25 | import java.util.ArrayList; |
26 | |
27 | import org.h2.api.ErrorCode; |
28 | import org.h2.engine.Constants; |
29 | import org.h2.engine.SysProperties; |
30 | import org.h2.message.DbException; |
31 | import org.h2.store.fs.FileUtils; |
32 | import org.h2.util.IOUtils; |
33 | import org.h2.util.JdbcUtils; |
34 | import org.h2.util.New; |
35 | import org.h2.util.StringUtils; |
36 | |
37 | /** |
38 | * A facility to read from and write to CSV (comma separated values) files. When |
39 | * reading, the BOM (the byte-order-mark) character 0xfeff at the beginning of |
40 | * the file is ignored. |
41 | * |
42 | * @author Thomas Mueller, Sylvain Cuaz |
43 | */ |
44 | public class Csv implements SimpleRowSource { |
45 | |
46 | private String[] columnNames; |
47 | |
48 | private String characterSet = SysProperties.FILE_ENCODING; |
49 | private char escapeCharacter = '\"'; |
50 | private char fieldDelimiter = '\"'; |
51 | private char fieldSeparatorRead = ','; |
52 | private String fieldSeparatorWrite = ","; |
53 | private boolean caseSensitiveColumnNames; |
54 | private boolean preserveWhitespace; |
55 | private boolean writeColumnHeader = true; |
56 | private char lineComment; |
57 | private String lineSeparator = SysProperties.LINE_SEPARATOR; |
58 | private String nullString = ""; |
59 | |
60 | private String fileName; |
61 | private Reader input; |
62 | private char[] inputBuffer; |
63 | private int inputBufferPos; |
64 | private int inputBufferStart = -1; |
65 | private int inputBufferEnd; |
66 | private Writer output; |
67 | private boolean endOfLine, endOfFile; |
68 | |
69 | private int writeResultSet(ResultSet rs) throws SQLException { |
70 | try { |
71 | int rows = 0; |
72 | ResultSetMetaData meta = rs.getMetaData(); |
73 | int columnCount = meta.getColumnCount(); |
74 | String[] row = new String[columnCount]; |
75 | int[] sqlTypes = new int[columnCount]; |
76 | for (int i = 0; i < columnCount; i++) { |
77 | row[i] = meta.getColumnLabel(i + 1); |
78 | sqlTypes[i] = meta.getColumnType(i + 1); |
79 | } |
80 | if (writeColumnHeader) { |
81 | writeRow(row); |
82 | } |
83 | while (rs.next()) { |
84 | for (int i = 0; i < columnCount; i++) { |
85 | Object o; |
86 | switch (sqlTypes[i]) { |
87 | case Types.DATE: |
88 | o = rs.getDate(i + 1); |
89 | break; |
90 | case Types.TIME: |
91 | o = rs.getTime(i + 1); |
92 | break; |
93 | case Types.TIMESTAMP: |
94 | o = rs.getTimestamp(i + 1); |
95 | break; |
96 | default: |
97 | o = rs.getString(i + 1); |
98 | } |
99 | row[i] = o == null ? null : o.toString(); |
100 | } |
101 | writeRow(row); |
102 | rows++; |
103 | } |
104 | output.close(); |
105 | return rows; |
106 | } catch (IOException e) { |
107 | throw DbException.convertIOException(e, null); |
108 | } finally { |
109 | close(); |
110 | JdbcUtils.closeSilently(rs); |
111 | } |
112 | } |
113 | |
114 | /** |
115 | * Writes the result set to a file in the CSV format. |
116 | * |
117 | * @param writer the writer |
118 | * @param rs the result set |
119 | * @return the number of rows written |
120 | */ |
121 | public int write(Writer writer, ResultSet rs) throws SQLException { |
122 | this.output = writer; |
123 | return writeResultSet(rs); |
124 | } |
125 | |
126 | /** |
127 | * Writes the result set to a file in the CSV format. The result set is read |
128 | * using the following loop: |
129 | * |
130 | * <pre> |
131 | * while (rs.next()) { |
132 | * writeRow(row); |
133 | * } |
134 | * </pre> |
135 | * |
136 | * @param outputFileName the name of the csv file |
137 | * @param rs the result set - the result set must be positioned before the |
138 | * first row. |
139 | * @param charset the charset or null to use the system default charset |
140 | * (see system property file.encoding) |
141 | * @return the number of rows written |
142 | */ |
143 | public int write(String outputFileName, ResultSet rs, String charset) |
144 | throws SQLException { |
145 | init(outputFileName, charset); |
146 | try { |
147 | initWrite(); |
148 | return writeResultSet(rs); |
149 | } catch (IOException e) { |
150 | throw convertException("IOException writing " + outputFileName, e); |
151 | } |
152 | } |
153 | |
154 | /** |
155 | * Writes the result set of a query to a file in the CSV format. |
156 | * |
157 | * @param conn the connection |
158 | * @param outputFileName the file name |
159 | * @param sql the query |
160 | * @param charset the charset or null to use the system default charset |
161 | * (see system property file.encoding) |
162 | * @return the number of rows written |
163 | */ |
164 | public int write(Connection conn, String outputFileName, String sql, |
165 | String charset) throws SQLException { |
166 | Statement stat = conn.createStatement(); |
167 | ResultSet rs = stat.executeQuery(sql); |
168 | int rows = write(outputFileName, rs, charset); |
169 | stat.close(); |
170 | return rows; |
171 | } |
172 | |
173 | /** |
174 | * Reads from the CSV file and returns a result set. The rows in the result |
175 | * set are created on demand, that means the file is kept open until all |
176 | * rows are read or the result set is closed. |
177 | * <br /> |
178 | * If the columns are read from the CSV file, then the following rules are |
179 | * used: columns names that start with a letter or '_', and only |
180 | * contain letters, '_', and digits, are considered case insensitive |
181 | * and are converted to uppercase. Other column names are considered |
182 | * case sensitive (that means they need to be quoted when accessed). |
183 | * |
184 | * @param inputFileName the file name |
185 | * @param colNames or null if the column names should be read from the CSV |
186 | * file |
187 | * @param charset the charset or null to use the system default charset |
188 | * (see system property file.encoding) |
189 | * @return the result set |
190 | */ |
191 | public ResultSet read(String inputFileName, String[] colNames, |
192 | String charset) throws SQLException { |
193 | init(inputFileName, charset); |
194 | try { |
195 | return readResultSet(colNames); |
196 | } catch (IOException e) { |
197 | throw convertException("IOException reading " + inputFileName, e); |
198 | } |
199 | } |
200 | |
201 | /** |
202 | * Reads CSV data from a reader and returns a result set. The rows in the |
203 | * result set are created on demand, that means the reader is kept open |
204 | * until all rows are read or the result set is closed. |
205 | * |
206 | * @param reader the reader |
207 | * @param colNames or null if the column names should be read from the CSV |
208 | * file |
209 | * @return the result set |
210 | */ |
211 | public ResultSet read(Reader reader, String[] colNames) throws IOException { |
212 | init(null, null); |
213 | this.input = reader; |
214 | return readResultSet(colNames); |
215 | } |
216 | |
217 | private ResultSet readResultSet(String[] colNames) throws IOException { |
218 | this.columnNames = colNames; |
219 | initRead(); |
220 | SimpleResultSet result = new SimpleResultSet(this); |
221 | makeColumnNamesUnique(); |
222 | for (String columnName : columnNames) { |
223 | result.addColumn(columnName, Types.VARCHAR, Integer.MAX_VALUE, 0); |
224 | } |
225 | return result; |
226 | } |
227 | |
228 | private void makeColumnNamesUnique() { |
229 | for (int i = 0; i < columnNames.length; i++) { |
230 | StringBuilder buff = new StringBuilder(); |
231 | String n = columnNames[i]; |
232 | if (n == null || n.length() == 0) { |
233 | buff.append('C').append(i + 1); |
234 | } else { |
235 | buff.append(n); |
236 | } |
237 | for (int j = 0; j < i; j++) { |
238 | String y = columnNames[j]; |
239 | if (buff.toString().equals(y)) { |
240 | buff.append('1'); |
241 | j = -1; |
242 | } |
243 | } |
244 | columnNames[i] = buff.toString(); |
245 | } |
246 | } |
247 | |
248 | private void init(String newFileName, String charset) { |
249 | this.fileName = newFileName; |
250 | if (charset != null) { |
251 | this.characterSet = charset; |
252 | } |
253 | } |
254 | |
255 | private void initWrite() throws IOException { |
256 | if (output == null) { |
257 | try { |
258 | OutputStream out = FileUtils.newOutputStream(fileName, false); |
259 | out = new BufferedOutputStream(out, Constants.IO_BUFFER_SIZE); |
260 | output = new BufferedWriter(new OutputStreamWriter(out, characterSet)); |
261 | } catch (Exception e) { |
262 | close(); |
263 | throw DbException.convertToIOException(e); |
264 | } |
265 | } |
266 | } |
267 | |
268 | private void writeRow(String[] values) throws IOException { |
269 | for (int i = 0; i < values.length; i++) { |
270 | if (i > 0) { |
271 | if (fieldSeparatorWrite != null) { |
272 | output.write(fieldSeparatorWrite); |
273 | } |
274 | } |
275 | String s = values[i]; |
276 | if (s != null) { |
277 | if (escapeCharacter != 0) { |
278 | if (fieldDelimiter != 0) { |
279 | output.write(fieldDelimiter); |
280 | } |
281 | output.write(escape(s)); |
282 | if (fieldDelimiter != 0) { |
283 | output.write(fieldDelimiter); |
284 | } |
285 | } else { |
286 | output.write(s); |
287 | } |
288 | } else if (nullString != null && nullString.length() > 0) { |
289 | output.write(nullString); |
290 | } |
291 | } |
292 | output.write(lineSeparator); |
293 | } |
294 | |
295 | private String escape(String data) { |
296 | if (data.indexOf(fieldDelimiter) < 0) { |
297 | if (escapeCharacter == fieldDelimiter || data.indexOf(escapeCharacter) < 0) { |
298 | return data; |
299 | } |
300 | } |
301 | int length = data.length(); |
302 | StringBuilder buff = new StringBuilder(length); |
303 | for (int i = 0; i < length; i++) { |
304 | char ch = data.charAt(i); |
305 | if (ch == fieldDelimiter || ch == escapeCharacter) { |
306 | buff.append(escapeCharacter); |
307 | } |
308 | buff.append(ch); |
309 | } |
310 | return buff.toString(); |
311 | } |
312 | |
313 | private void initRead() throws IOException { |
314 | if (input == null) { |
315 | try { |
316 | InputStream in = FileUtils.newInputStream(fileName); |
317 | in = new BufferedInputStream(in, Constants.IO_BUFFER_SIZE); |
318 | input = new InputStreamReader(in, characterSet); |
319 | } catch (IOException e) { |
320 | close(); |
321 | throw e; |
322 | } |
323 | } |
324 | if (!input.markSupported()) { |
325 | input = new BufferedReader(input); |
326 | } |
327 | input.mark(1); |
328 | int bom = input.read(); |
329 | if (bom != 0xfeff) { |
330 | // Microsoft Excel compatibility |
331 | // ignore pseudo-BOM |
332 | input.reset(); |
333 | } |
334 | inputBuffer = new char[Constants.IO_BUFFER_SIZE * 2]; |
335 | if (columnNames == null) { |
336 | readHeader(); |
337 | } |
338 | } |
339 | |
340 | private void readHeader() throws IOException { |
341 | ArrayList<String> list = New.arrayList(); |
342 | while (true) { |
343 | String v = readValue(); |
344 | if (v == null) { |
345 | if (endOfLine) { |
346 | if (endOfFile || list.size() > 0) { |
347 | break; |
348 | } |
349 | } else { |
350 | v = "COLUMN" + list.size(); |
351 | list.add(v); |
352 | } |
353 | } else { |
354 | if (v.length() == 0) { |
355 | v = "COLUMN" + list.size(); |
356 | } else if (!caseSensitiveColumnNames && isSimpleColumnName(v)) { |
357 | v = v.toUpperCase(); |
358 | } |
359 | list.add(v); |
360 | if (endOfLine) { |
361 | break; |
362 | } |
363 | } |
364 | } |
365 | columnNames = new String[list.size()]; |
366 | list.toArray(columnNames); |
367 | } |
368 | |
369 | private static boolean isSimpleColumnName(String columnName) { |
370 | for (int i = 0, length = columnName.length(); i < length; i++) { |
371 | char ch = columnName.charAt(i); |
372 | if (i == 0) { |
373 | if (ch != '_' && !Character.isLetter(ch)) { |
374 | return false; |
375 | } |
376 | } else { |
377 | if (ch != '_' && !Character.isLetterOrDigit(ch)) { |
378 | return false; |
379 | } |
380 | } |
381 | } |
382 | if (columnName.length() == 0) { |
383 | return false; |
384 | } |
385 | return true; |
386 | } |
387 | |
388 | private void pushBack() { |
389 | inputBufferPos--; |
390 | } |
391 | |
392 | private int readChar() throws IOException { |
393 | if (inputBufferPos >= inputBufferEnd) { |
394 | return readBuffer(); |
395 | } |
396 | return inputBuffer[inputBufferPos++]; |
397 | } |
398 | |
399 | private int readBuffer() throws IOException { |
400 | if (endOfFile) { |
401 | return -1; |
402 | } |
403 | int keep; |
404 | if (inputBufferStart >= 0) { |
405 | keep = inputBufferPos - inputBufferStart; |
406 | if (keep > 0) { |
407 | char[] src = inputBuffer; |
408 | if (keep + Constants.IO_BUFFER_SIZE > src.length) { |
409 | inputBuffer = new char[src.length * 2]; |
410 | } |
411 | System.arraycopy(src, inputBufferStart, inputBuffer, 0, keep); |
412 | } |
413 | inputBufferStart = 0; |
414 | } else { |
415 | keep = 0; |
416 | } |
417 | inputBufferPos = keep; |
418 | int len = input.read(inputBuffer, keep, Constants.IO_BUFFER_SIZE); |
419 | if (len == -1) { |
420 | // ensure bufferPos > bufferEnd |
421 | // even after pushBack |
422 | inputBufferEnd = -1024; |
423 | endOfFile = true; |
424 | // ensure the right number of characters are read |
425 | // in case the input buffer is still used |
426 | inputBufferPos++; |
427 | return -1; |
428 | } |
429 | inputBufferEnd = keep + len; |
430 | return inputBuffer[inputBufferPos++]; |
431 | } |
432 | |
433 | private String readValue() throws IOException { |
434 | endOfLine = false; |
435 | inputBufferStart = inputBufferPos; |
436 | while (true) { |
437 | int ch = readChar(); |
438 | if (ch == fieldDelimiter) { |
439 | // delimited value |
440 | boolean containsEscape = false; |
441 | inputBufferStart = inputBufferPos; |
442 | int sep; |
443 | while (true) { |
444 | ch = readChar(); |
445 | if (ch == fieldDelimiter) { |
446 | ch = readChar(); |
447 | if (ch != fieldDelimiter) { |
448 | sep = 2; |
449 | break; |
450 | } |
451 | containsEscape = true; |
452 | } else if (ch == escapeCharacter) { |
453 | ch = readChar(); |
454 | if (ch < 0) { |
455 | sep = 1; |
456 | break; |
457 | } |
458 | containsEscape = true; |
459 | } else if (ch < 0) { |
460 | sep = 1; |
461 | break; |
462 | } |
463 | } |
464 | String s = new String(inputBuffer, |
465 | inputBufferStart, inputBufferPos - inputBufferStart - sep); |
466 | if (containsEscape) { |
467 | s = unEscape(s); |
468 | } |
469 | inputBufferStart = -1; |
470 | while (true) { |
471 | if (ch == fieldSeparatorRead) { |
472 | break; |
473 | } else if (ch == '\n' || ch < 0 || ch == '\r') { |
474 | endOfLine = true; |
475 | break; |
476 | } else if (ch == ' ' || ch == '\t') { |
477 | // ignore |
478 | } else { |
479 | pushBack(); |
480 | break; |
481 | } |
482 | ch = readChar(); |
483 | } |
484 | return s; |
485 | } else if (ch == '\n' || ch < 0 || ch == '\r') { |
486 | endOfLine = true; |
487 | return null; |
488 | } else if (ch == fieldSeparatorRead) { |
489 | // null |
490 | return null; |
491 | } else if (ch <= ' ') { |
492 | // ignore spaces |
493 | continue; |
494 | } else if (lineComment != 0 && ch == lineComment) { |
495 | // comment until end of line |
496 | inputBufferStart = -1; |
497 | while (true) { |
498 | ch = readChar(); |
499 | if (ch == '\n' || ch < 0 || ch == '\r') { |
500 | break; |
501 | } |
502 | } |
503 | endOfLine = true; |
504 | return null; |
505 | } else { |
506 | // un-delimited value |
507 | while (true) { |
508 | ch = readChar(); |
509 | if (ch == fieldSeparatorRead) { |
510 | break; |
511 | } else if (ch == '\n' || ch < 0 || ch == '\r') { |
512 | endOfLine = true; |
513 | break; |
514 | } |
515 | } |
516 | String s = new String(inputBuffer, |
517 | inputBufferStart, inputBufferPos - inputBufferStart - 1); |
518 | if (!preserveWhitespace) { |
519 | s = s.trim(); |
520 | } |
521 | inputBufferStart = -1; |
522 | // check un-delimited value for nullString |
523 | return readNull(s); |
524 | } |
525 | } |
526 | } |
527 | |
528 | private String readNull(String s) { |
529 | return s.equals(nullString) ? null : s; |
530 | } |
531 | |
532 | private String unEscape(String s) { |
533 | StringBuilder buff = new StringBuilder(s.length()); |
534 | int start = 0; |
535 | char[] chars = null; |
536 | while (true) { |
537 | int idx = s.indexOf(escapeCharacter, start); |
538 | if (idx < 0) { |
539 | idx = s.indexOf(fieldDelimiter, start); |
540 | if (idx < 0) { |
541 | break; |
542 | } |
543 | } |
544 | if (chars == null) { |
545 | chars = s.toCharArray(); |
546 | } |
547 | buff.append(chars, start, idx - start); |
548 | if (idx == s.length() - 1) { |
549 | start = s.length(); |
550 | break; |
551 | } |
552 | buff.append(chars[idx + 1]); |
553 | start = idx + 2; |
554 | } |
555 | buff.append(s.substring(start)); |
556 | return buff.toString(); |
557 | } |
558 | |
559 | /** |
560 | * INTERNAL |
561 | */ |
562 | @Override |
563 | public Object[] readRow() throws SQLException { |
564 | if (input == null) { |
565 | return null; |
566 | } |
567 | String[] row = new String[columnNames.length]; |
568 | try { |
569 | int i = 0; |
570 | while (true) { |
571 | String v = readValue(); |
572 | if (v == null) { |
573 | if (endOfLine) { |
574 | if (i == 0) { |
575 | if (endOfFile) { |
576 | return null; |
577 | } |
578 | // empty line |
579 | continue; |
580 | } |
581 | break; |
582 | } |
583 | } |
584 | if (i < row.length) { |
585 | row[i++] = v; |
586 | } |
587 | if (endOfLine) { |
588 | break; |
589 | } |
590 | } |
591 | } catch (IOException e) { |
592 | throw convertException("IOException reading from " + fileName, e); |
593 | } |
594 | return row; |
595 | } |
596 | |
597 | private static SQLException convertException(String message, Exception e) { |
598 | return DbException.get(ErrorCode.IO_EXCEPTION_1, e, message).getSQLException(); |
599 | } |
600 | |
601 | /** |
602 | * INTERNAL |
603 | */ |
604 | @Override |
605 | public void close() { |
606 | IOUtils.closeSilently(input); |
607 | input = null; |
608 | IOUtils.closeSilently(output); |
609 | output = null; |
610 | } |
611 | |
612 | /** |
613 | * INTERNAL |
614 | */ |
615 | @Override |
616 | public void reset() throws SQLException { |
617 | throw new SQLException("Method is not supported", "CSV"); |
618 | } |
619 | |
620 | /** |
621 | * Override the field separator for writing. The default is ",". |
622 | * |
623 | * @param fieldSeparatorWrite the field separator |
624 | */ |
625 | public void setFieldSeparatorWrite(String fieldSeparatorWrite) { |
626 | this.fieldSeparatorWrite = fieldSeparatorWrite; |
627 | } |
628 | |
629 | /** |
630 | * Get the current field separator for writing. |
631 | * |
632 | * @return the field separator |
633 | */ |
634 | public String getFieldSeparatorWrite() { |
635 | return fieldSeparatorWrite; |
636 | } |
637 | |
638 | /** |
639 | * Override the case sensitive column names setting. The default is false. |
640 | * If enabled, the case of all column names is always preserved. |
641 | * |
642 | * @param caseSensitiveColumnNames whether column names are case sensitive |
643 | */ |
644 | public void setCaseSensitiveColumnNames(boolean caseSensitiveColumnNames) { |
645 | this.caseSensitiveColumnNames = caseSensitiveColumnNames; |
646 | } |
647 | |
648 | /** |
649 | * Get the current case sensitive column names setting. |
650 | * |
651 | * @return whether column names are case sensitive |
652 | */ |
653 | public boolean getCaseSensitiveColumnNames() { |
654 | return caseSensitiveColumnNames; |
655 | } |
656 | |
657 | /** |
658 | * Override the field separator for reading. The default is ','. |
659 | * |
660 | * @param fieldSeparatorRead the field separator |
661 | */ |
662 | public void setFieldSeparatorRead(char fieldSeparatorRead) { |
663 | this.fieldSeparatorRead = fieldSeparatorRead; |
664 | } |
665 | |
666 | /** |
667 | * Get the current field separator for reading. |
668 | * |
669 | * @return the field separator |
670 | */ |
671 | public char getFieldSeparatorRead() { |
672 | return fieldSeparatorRead; |
673 | } |
674 | |
675 | /** |
676 | * Set the line comment character. The default is character code 0 (line |
677 | * comments are disabled). |
678 | * |
679 | * @param lineCommentCharacter the line comment character |
680 | */ |
681 | public void setLineCommentCharacter(char lineCommentCharacter) { |
682 | this.lineComment = lineCommentCharacter; |
683 | } |
684 | |
685 | /** |
686 | * Get the line comment character. |
687 | * |
688 | * @return the line comment character, or 0 if disabled |
689 | */ |
690 | public char getLineCommentCharacter() { |
691 | return lineComment; |
692 | } |
693 | |
694 | /** |
695 | * Set the field delimiter. The default is " (a double quote). |
696 | * The value 0 means no field delimiter is used. |
697 | * |
698 | * @param fieldDelimiter the field delimiter |
699 | */ |
700 | public void setFieldDelimiter(char fieldDelimiter) { |
701 | this.fieldDelimiter = fieldDelimiter; |
702 | } |
703 | |
704 | /** |
705 | * Get the current field delimiter. |
706 | * |
707 | * @return the field delimiter |
708 | */ |
709 | public char getFieldDelimiter() { |
710 | return fieldDelimiter; |
711 | } |
712 | |
713 | /** |
714 | * Set the escape character. The escape character is used to escape the |
715 | * field delimiter. This is needed if the data contains the field delimiter. |
716 | * The default escape character is " (a double quote), which is the same as |
717 | * the field delimiter. If the field delimiter and the escape character are |
718 | * both " (double quote), and the data contains a double quote, then an |
719 | * additional double quote is added. Example: |
720 | * <pre> |
721 | * Data: He said "Hello". |
722 | * Escape character: " |
723 | * Field delimiter: " |
724 | * CSV file: "He said ""Hello""." |
725 | * </pre> |
726 | * If the field delimiter is a double quote and the escape character is a |
727 | * backslash, then escaping is done similar to Java (however, only the field |
728 | * delimiter is escaped). Example: |
729 | * <pre> |
730 | * Data: He said "Hello". |
731 | * Escape character: \ |
732 | * Field delimiter: " |
733 | * CSV file: "He said \"Hello\"." |
734 | * </pre> |
735 | * The value 0 means no escape character is used. |
736 | * |
737 | * @param escapeCharacter the escape character |
738 | */ |
739 | public void setEscapeCharacter(char escapeCharacter) { |
740 | this.escapeCharacter = escapeCharacter; |
741 | } |
742 | |
743 | /** |
744 | * Get the current escape character. |
745 | * |
746 | * @return the escape character |
747 | */ |
748 | public char getEscapeCharacter() { |
749 | return escapeCharacter; |
750 | } |
751 | |
752 | /** |
753 | * Set the line separator used for writing. This is usually a line feed (\n |
754 | * or \r\n depending on the system settings). The line separator is written |
755 | * after each row (including the last row), so this option can include an |
756 | * end-of-row marker if needed. |
757 | * |
758 | * @param lineSeparator the line separator |
759 | */ |
760 | public void setLineSeparator(String lineSeparator) { |
761 | this.lineSeparator = lineSeparator; |
762 | } |
763 | |
764 | /** |
765 | * Get the line separator used for writing. |
766 | * |
767 | * @return the line separator |
768 | */ |
769 | public String getLineSeparator() { |
770 | return lineSeparator; |
771 | } |
772 | |
773 | /** |
774 | * Set the value that represents NULL. It is only used for non-delimited |
775 | * values. |
776 | * |
777 | * @param nullString the null |
778 | */ |
779 | public void setNullString(String nullString) { |
780 | this.nullString = nullString; |
781 | } |
782 | |
783 | /** |
784 | * Get the current null string. |
785 | * |
786 | * @return the null string. |
787 | */ |
788 | public String getNullString() { |
789 | return nullString; |
790 | } |
791 | |
792 | /** |
793 | * Enable or disable preserving whitespace in unquoted text. |
794 | * |
795 | * @param value the new value for the setting |
796 | */ |
797 | public void setPreserveWhitespace(boolean value) { |
798 | this.preserveWhitespace = value; |
799 | } |
800 | |
801 | /** |
802 | * Whether whitespace in unquoted text is preserved. |
803 | * |
804 | * @return the current value for the setting |
805 | */ |
806 | public boolean getPreserveWhitespace() { |
807 | return preserveWhitespace; |
808 | } |
809 | |
810 | /** |
811 | * Enable or disable writing the column header. |
812 | * |
813 | * @param value the new value for the setting |
814 | */ |
815 | public void setWriteColumnHeader(boolean value) { |
816 | this.writeColumnHeader = value; |
817 | } |
818 | |
819 | /** |
820 | * Whether the column header is written. |
821 | * |
822 | * @return the current value for the setting |
823 | */ |
824 | public boolean getWriteColumnHeader() { |
825 | return writeColumnHeader; |
826 | } |
827 | |
828 | /** |
829 | * INTERNAL. |
830 | * Parse and set the CSV options. |
831 | * |
832 | * @param options the the options |
833 | * @return the character set |
834 | */ |
835 | public String setOptions(String options) { |
836 | String charset = null; |
837 | String[] keyValuePairs = StringUtils.arraySplit(options, ' ', false); |
838 | for (String pair : keyValuePairs) { |
839 | if (pair.length() == 0) { |
840 | continue; |
841 | } |
842 | int index = pair.indexOf('='); |
843 | String key = StringUtils.trim(pair.substring(0, index), true, true, " "); |
844 | String value = pair.substring(index + 1); |
845 | char ch = value.length() == 0 ? 0 : value.charAt(0); |
846 | if (isParam(key, "escape", "esc", "escapeCharacter")) { |
847 | setEscapeCharacter(ch); |
848 | } else if (isParam(key, "fieldDelimiter", "fieldDelim")) { |
849 | setFieldDelimiter(ch); |
850 | } else if (isParam(key, "fieldSeparator", "fieldSep")) { |
851 | setFieldSeparatorRead(ch); |
852 | setFieldSeparatorWrite(value); |
853 | } else if (isParam(key, "lineComment", "lineCommentCharacter")) { |
854 | setLineCommentCharacter(ch); |
855 | } else if (isParam(key, "lineSeparator", "lineSep")) { |
856 | setLineSeparator(value); |
857 | } else if (isParam(key, "null", "nullString")) { |
858 | setNullString(value); |
859 | } else if (isParam(key, "charset", "characterSet")) { |
860 | charset = value; |
861 | } else if (isParam(key, "preserveWhitespace")) { |
862 | setPreserveWhitespace(Boolean.parseBoolean(value)); |
863 | } else if (isParam(key, "writeColumnHeader")) { |
864 | setWriteColumnHeader(Boolean.parseBoolean(value)); |
865 | } else if (isParam(key, "caseSensitiveColumnNames")) { |
866 | setCaseSensitiveColumnNames(Boolean.parseBoolean(value)); |
867 | } else { |
868 | throw DbException.getUnsupportedException(key); |
869 | } |
870 | } |
871 | return charset; |
872 | } |
873 | |
874 | private static boolean isParam(String key, String... values) { |
875 | for (String v : values) { |
876 | if (key.equalsIgnoreCase(v)) { |
877 | return true; |
878 | } |
879 | } |
880 | return false; |
881 | } |
882 | |
883 | } |