How to export results of flexible search query to a csv file using groovy script

[tescopolitan]

I have a flexible search query which involves joins and want to export the results of this query into a csv file. The impex export process doesn't seem to work because of the joins. Anyone know of a groovy script I can use to export the results of the query to a csv file?

[tescopolitan]

For example, if we have a query like select{cp.name},{cp.uid},{pg.uid},{csp.position},{csp.uid}from{ContentPage ascpjoin ContentSlotForPage as csp on{csp.page}={cp.pk}join PageTemplate as pg on{cp.masterTemplate}={pg.pk}}. The regular export doesn't work..how would be be able to export the results of this query into a CSV?

[romitchhabra]

I wonder what you want to extract as csv for which you need join. Can you please share some more examples -

Export script for the example which you shared will look like below and will give you all the data without using join-

UPDATE ContentSlotForPage; page(uid); page(name) ; position ; uid; page(masterTemplate(uid)) #%impex.exportItemsFlexibleSearch("Select {csp.pk} from {ContentSlotForPage} where {csp.uid}='abc'");

[former_member1011565]

It's possible to write a CSV file via Groovy but in my opinion ImpEx would be the right choice here.

Anyways here's some ugly code I quickly put together to get you started. I'm using apache-commons-csv in this example and I'd always recommend using a proven csv library like this one instead of writing the CSV file manually since you'd probably run into escaping issues, not handle multiline values properly and so on.

Happy refactoring.

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import org.apache.commons.io.FileUtils;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.io.File;

final String targetFile = "/Users/what-ever/Desktop/TEST.CSV";

final File file = new File(targetFile);

byte[] bom = [0xEF, 0xBB, 0xBF]; // small "hack" to make this work with Microsoft Excel
FileUtils.writeByteArrayToFile(file, bom, false);

final Writer writer = new OutputStreamWriter(FileUtils.openOutputStream(file, true), "UTF-8");
final CSVPrinter printer = CSVFormat.DEFAULT.withHeader("Code", "Field 1", "Field 2").withDelimiter((char)';').print(writer);

def flexibleSearchService = spring.getBean("flexibleSearchService");
def result = flexibleSearchService.search("SELECT {PK} FROM {Product}").getResult()

for(def product : result) {
  printer.printRecord(product.getCode(), product.getField1(), product.getField2());
}


printer.close();

[tescopolitan]

CSVFormat and CSVPrinter are invalid in our environment.

[former_member1011565]

Then you might need to add the dependency to your project or use a different CSV writer such as the one that comes with hybris (de.hybris.platform.util.CSVWriter).