1

I am trying to dump out the contents of my mysql query into a csv and read it using some java based open source csv reader. Here are the problems that I face with that,

  1. My data set is having around 50 fields. The data set contains few fields with text having line breaks. Hence to prevent breaking my CSV reader, I gave Fields optionally enclosed by "\"" so that line breaks will be wrapped inside double quotes. In this case, for other fields even if there are no line breaks, it wraps them inside double quotes.
  2. Looks like by default the escape character while doing mysql dump is \ ( backslash) This causes line breaks to appear with \ at the end which breaks the csv parser. To remove this \ at the end, if I give Fields escaped by '' ( empty string), it causes my double quotes in the text not to be escaped, still breaking the csv read.

It would be great if I can skip the line break escaping, but still retain escaping double quotes to cause csv reader not to break.

Any suggestions what can I follow here?

Thanks, Sriram

sriram
  • 712
  • 8
  • 26
  • `OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'` should be what you're looking for, to create standard CSV, which escapes `"` by doubling them up. – Michael - sqlbot Jun 01 '16 at 04:53
  • @Michael-sqlbot Thanks for your comment. I already tried that. It escapes line breaks inside the text field with double quotes as well which breaks the number of fields in csv output. – sriram Jun 01 '16 at 06:17
  • Apologies, I misread your question. I suppose you could `SELECT CONCAT('"',REPLACE(column1,'"','""'),'"'), ... INTO OUTFILE ...` – Michael - sqlbot Jun 02 '16 at 00:56
  • What @Michael-sqlbot is suggesting is the way to go. Marrying `select into outfile` with `load data infile` if that is an option. If anyone thinks a programming language (you name it) or a tool will yield faster results, I beg to differ. – Drew Jun 02 '16 at 01:21
  • Can't really argue with that, although when I do it, I just use Perl, DBI of course, and `Text::CSV_XS`, which is a Perl module whose working guts are written in C, so it's pretty speedy and has a lot of options for wacky escaping and quoting, as needed. – Michael - sqlbot Jun 02 '16 at 01:31

1 Answers1

1

Try dumping your data into CSV using uniVocity-parsers. You can then read the result using the same library:

Try this for dumping the data out:

ResultSet resultSet = executeYourQuery();

// To dump the data of our ResultSet, we configure the output format:
CsvWriterSettings writerSettings = new CsvWriterSettings();
writerSettings.getFormat().setLineSeparator("\n");
writerSettings.setHeaderWritingEnabled(true); // if you want want the column names to be printed out.

// Then create a routines object:
CsvRoutines routines = new CsvRoutines(writerSettings);

// The write() method takes care of everything. Both resultSet and output are closed by the routine.
routines.write(resultSet, new File("/path/to/your.csv"), "UTF-8");

And this to read your file:

// creates a CSV parser
CsvParserSettings parserSettings = new CsvParserSettings();
parserSettings.getFormat().setLineSeparator("\n");
parserSettings.setHeaderExtractionEnabled(true); //extract headers from file
CsvParser parser = new CsvParser(parserSettings);

// call beginParsing to read records one by one, iterator-style. Note that there are many ways to read your file, check the documentation.
parser.beginParsing(new File("/path/to/your.csv"), "UTF-8);

String[] row;
while ((row = parser.parseNext()) != null) {
    System.out.println(Arrays.toString(row));
}

Hope this helps.

Disclaimer: I'm the author of this library, it's open source and free (Apache V2.0 license)

Jeronimo Backes
  • 6,141
  • 2
  • 25
  • 29
  • Thanks for your answer. As you mentioned here, definitely I can use the Java Api to read it and process it, and write it into CSV as per my requirements, but I want to give mysql's in built csv write functionality a try. I think I am probably missing something as both line break escape in the text field and double quotes escape are taking the same escape character. I just don't want to escape the line breaks, but instead enclose them within double quotes, but escape double quotes appearing in the text field. – sriram Jun 01 '16 at 06:26
  • Try this solution: http://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format If it doesn't work then don't waste too much time trying to make it work, mysql is pretty bad complying with standards of any kind and you'll waste a lot of time to get a possibly slower solution. – Jeronimo Backes Jun 01 '16 at 07:03