I need to export solr data to EXCEL or CSV. I have SOLR code with millions of records and I need a way to export them by a certain query to CSV/EXCEL. In addition I need to control the headers inside the EXCEL.
What do you suggest? Is there a 3rd party API that does it? Should I use CSV format and just extract them and then manipulate the csv?
The best tool to export your Solr data is the /export request handler [1].
Unfortunately, from the official Solr documentation, csv is not supported as an output.
Json is the default. If you can manage Json transformations to obtain the data format you want in CSV, the /export is definitely the best way to proceed. If performance is not an issue, the standard select request handler using the CSV response writer should do the trick [2].
http:/localhost:8983/solr/collection1/select?q=*:*&wt=csv&indent=true&rows=N
[1] https://lucene.apache.org/solr/guide/6_6/exporting-result-sets.html
[2] http:/localhost:8886/solr/tech/select?q=:&wt=csv&indent=true
You may use Solr select endpoint to extract all documents with q=*:* and csv responseWriter.
Example:
http:/localhost:8886/solr/tech/select?q=*:*&wt=csv&indent=true
More details about csv ResponseWriter can be found here.
Since Solr 6.3, you can use the XLSX response writer.
With this you can control the result headers, customizing your own columns names and even column widths. It looks exactly what you want :)
Here's the documentation you need to follow : https://lucene.apache.org/solr/guide/6_6/response-writers.html#ResponseWriters-XLSXResponseWriter