CODEWITHSUNDEEP
csvmysql
sriram
sriram

Reputation: 732

Mysql dump character escaping and CSV read

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

Upvotes: 1

Views: 875

Answers (1)

Jeronimo Backes
Jeronimo Backes

Reputation: 6289

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)

Upvotes: 1

Related Questions